Problem with commas changing to semicolons in string

[jgeniti]

I am trying to create a routine that will populate the "To" field in a
new emial with the names in a table. I have written the following
routine to append all of the names in the table to a string, but when
the string gets passed through the sendObject command it changes all
of my commas to semicolons. The actual string parameter is OK, but for
some reason I can't get it to show up correctly in the "TO" field.

Example:
ToList = "Doe, Jane; Smith, John" will show up as "Doe; Jane; Smith;
John" in the "TO" field of Outlook.
As you could imagine, Outlook get screwed up by all of the semicolons.

Anyhelp would be appreciated.
Thank you,
James


Private Sub CreateEmail_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ToList, ESubj As String
On Error GoTo Err_Handler
Set db = CurrentDb()
Set rst = db.OpenRecordset("PickList")

With rst
If .RecordCount Then
.MoveFirst
Do Until .EOF
ToList = ToList & rst("FROM")
.MoveNext
If .EOF Then
Else
ToList = ToList & "; "
End If
Loop
End If
.Close
End With

Set rst = Nothing
Set db = Nothing
ESubj = "Winner - " & Me.ChooseEvent
DoCmd.SendObject acSendNoObject, , acFormatHTML, ToList, , , ESubj

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "MS Access has generated the following error" & vbCrLf
& vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: FormName /
SendEmail_Click" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error
has Occured! "
End If


End Sub

 

[strive4peace]

Hi James,

the "to" list is supposed to be email addresses


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[jgeniti]

Thanks for the reply Crystal but the way our Outlook is setup I can
just add a list of names and it will recognize them. If I cut and
paste the records from the table into the "TO" field it works fine.

 

[Guest]

In Outlook, you are correct; however, when you are using the SendObject in
Access, Crystal is correct.

 

[strive4peace]

thanks, Dave

gjenti, you might try delimiting the names with single or double quotes
-- see if Outlook does the association. You must have a way to include
a space or comma in the string so that it is not interpreted as a
delimiter...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*