Sending Email to Multiple People

[ddevil]

I have written the code below that sends and email to a person when someone changes a task that the person needs to attend to. Now, I'd like to send an email to a list of people. I have a query that complies the list, but I don't know how to loop though that list to send to multiple people. If you could help, that would be great!

______________________________________________________
Private Sub UpdatesStillNeeded_Click()
Dim answer As Integer
Dim EmailText As String
Dim ToReceiver, Subject As String

EmailText = "The Project Aging database is ready to be updated"

ToReceiver = DLookup("EmployeeEmailAddress", "Tlkp_Employee", "ConsultantLookup = [EmployeeLName]")
Subject = "Project Aging Database"

answer = MsgBox("Do you want to send an email ", vbYesNo, "Send Email?")

If answer = vbYes Then

DoCmd.SendObject acSendNoObject, , , ToReceiver, , , Subject, EmailText, False

End If
End Sub

 

[PHV]

Use a recordset to retrieve your distribution list.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ddevil]

Ok, I created the record set as below, but how do I populate the ToReceiver to send and email to everyone at once, instead of sending individually. So in the TO field I want name@email; name@email;, etc.? My code is below, thanks for your help!

Private Sub UpdatesStillNeeded_Click()
Dim answer As Integer
Dim cnn As New ADODB.Connection
Dim rstTo As New ADODB.Recordset
Dim EmailText As String

EmailText = "This is a test, please reply back so I know if you got this message. Thanks!"
Set cnn = CurrentProject.Connection
rstTpen "Select * from qry_EmailProjReview", cnn, adOpenDynamic, adLockOptimistic
Do While Not rstTo.EOF

Dim ToReceiver, Subject As String
ToReceiver = EmployeeEmailAddress
Subject = "Project Aging Database Test"


Do While Not rstTo.EOF
DoCmd.SendObject acSendNoObject, , , rstTo("EmployeeEmailAddress"), , , Subject, EmailText, False
rstTo.MoveNext
Loop


Loop

End Sub

 

[PWise]

dim toStr As string
Do While Not rstTo.EOF
ToStr=ToStr + ";" & rstTo("EmployeeEmailAddress"),
rstTo.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , Tostr, , , Subject, EmailText, False

 

[jadams0173]

DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]

to-- A string expression that lists the recipients whose names you want to put on the To line in the mail message.
Separate the recipient names you specify in this argument and in the cc and bcc arguments with a semicolon ( or with the list separator set on the Number tab of the Regional Settings Properties dialog box in Windows Control Panel. If the recipient names aren't recognized by the mail application, the message isn't sent and an error occurs.
If you leave this argument blank, Microsoft Access prompts you for the recipients.

 

[ddevil]

That was just too easy, I'm always making it harder that I has to be!

Thank you!

 

[ddevil]

I have another problem with email, please look at the code below. How would I write the in statement for the record set. I just want those with the roleid of 18 and 6 to be emailed, I'm obviously not doing it correctly. Thanks!

Private Sub NotifyAM_Click()
Dim answer As Integer
Dim EmailText As String
Dim ToReceiver, Subject As String

EmailText = "The Project Aging Information Has Been Updated and the file is attached for your review. Thank you!"

Set cnn = CurrentProject.Connection
rstTpen "Select * from qry_EmailProjReview", where RoleID in (18,6)",cnn, adOpenDynamic, adLockOptimistic

Do While Not rstTo.EOF

Dim toStr As String
Dim ToReceiver As String

Do While Not rstTo.EOF
toStr = toStr & rstTo("EmployeeEmailAddress") + ";"
rstTo.MoveNext
Loop

DoCmd.SendObject acSendQuery, "qry_AM_Export_of_Date", acFormatRTF, ToReceiver, , , Subject, EmailText, False

Loop

End Sub

 

[PWise]

 rstTo.Open "Select * from qry_EmailProjReview where RoleID in (18,6)",cnn, adOpenDynamic, adLockOptimistic

 

[ddevil]

Darn ,'s. Thanks!