Using GetAddress

[Anne P.]

Hi,

I have a memo template in which I am using GetAddress to allow the user to
choose names from the address book. I have two textboxes on the user form:
txtTo and txtCC. I have a command button on the userform to call the
address book. Here is the code I am using:

Private Sub cmdTo_Click()
Dim strAddress As String

If txtTo = "" Then
strAddress = Application.GetAddress(, , True, 1, 2, , True, True)
txtTo.Text = strAddress
Else
strAddress = txtTo.Text & Application.GetAddress(, , True, 1, 2, , True,
True)
txtTo.Text = strAddress
End If
End Sub

This shows the Select Names dialog with the To and CC boxes. The problem is
this:

I select several names and add them to the To box, then select several names
and add to the CC box. When I click OK and come back to my userform, all of
the names from both the To box and the CC Box come into the txtTo textbox on
my user form. They appear in the txtTo box as follows:
Amy Fox, John Doe, Snow White
Jack Black, Jack Frost, John Fox

I figure that I have to use the InStr function to replace the commas with
paragraph marks to make a list of names. But how do I get the first list
into the txtTo box and the second list into the txtCC box.

Thanks for any help,
Anne P.

 

[Chuck]

how do I get the first list

into the txtTo box and the second list into the txtCC box.

Not sure what you mean by "the second list". If you mean, how do I return
the To addresses from GetAddress into txtTo and the CC addresses from
GetAddress into txtCC try this code. It looks a little more intimidating
than it is. Essentially it checks where the Chr(13) character is in the
string returned by GetAddress and if it's anywhere before the very end, it
assumes everything before the Chr(13) is a To address and everything after is
a CC. Note that this code also strips out the Chr(13) characters (using
Len(x) -1)). There's also some error checking code - if the user doesn't
choose any addresses and then clicks OK or Cancel in the Select Names dialog,
they get a message and nothing further happens.

Private Sub cmdTo_Click()

Dim strAddress As String
Dim strTo As String
Dim strCC As String
Dim lngPos As String

strAddress = Application.GetAddress( _
, , True, 1, 2, , True, True)

If strAddress = "" Then
MsgBox "No addressee(s) selected."
Exit Sub
End If

lngPos = InStr(1, strAddress, Chr(13))

If lngPos < Len(strAddress) Then
'everything after Chr(13) is a CC
'returned from GetAddress
strTo = Left(Left(strAddress, lngPos), _
Len(Left(strAddress, lngPos)) - 1)
strCC = Left(Right(strAddress, Len(strAddress) - lngPos), _
Len(Right(strAddress, Len(strAddress) - lngPos)) - 1)
Else
strTo = Left(strAddress, Len(strAddress) - 1)
End If

If txtTo = "" Then
txtTo.Text = strTo
Else
If strTo > "" Then
txtTo.Text = txtTo.Text & ", " & strTo
End If
End If
If txtCC = "" Then
txtCC.Text = strCC
Else
If strCC > "" Then
txtCC.Text = txtCC.Text & ", " & strCC
End If
End If

End Sub

 

[Anne P.]

Chuck,

You were correct. I wanted names from To in txtTo and from CC in txtCC.
The code you provided works like a charm.

There is one other thing that I want to do and I believe I know how to do
it. The code you supplied splits the to and cc names, but they are put in
the txtTo and txtCC boxes as such:

txtTo = Amy Fox, John Doe, Jane Doe

I need them to be:
Amy Fox
John Doe
Jane Doe

I just found a function called ReplaceString in which I pass the string to
be searched, the string to look for (, ) and the string to replace with
(vbCr). I am going to give that a try right now.

Thanks so much for the help. I knew somebody here on this board would be
able to help me out. I should have posted sooner.

Anne P.