email loop from switchboard 1

[hlkelly]

I posted earlier today but realize I should be taking a different approach to my problem. Goal: send a DIFFERENT e-mail to a set of clients obtained by query. The code below works great if the e-mail to each person is the same. I hope to achieve a customized e-mail to each recipient detailing their order shipment. The code calls the query and inserts the e-mails found but all in one message. Any assistance is appreciated.

Public Function sendEmail()

Dim rs As New ADODB.Recordset
Dim strEmail As String

rs.Open "qrydailyordermailmerge", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

strEmail = ""
Do While Not rs.EOF
strEmail = strEmail & rs!ContactEmail & ";"
rs.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , "Your Order Confirmation from MY COMPANY NAME HERE", "Test", True

rs.Close
Set rs = Nothing

End Function


Thanks.

Heather

 

[Remou]

Just move the loop end.

Public Function sendEmail()

Dim rs As New ADODB.Recordset
Dim strEmail As String

rs.Open "qrydailyordermailmerge", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

strEmail = ""
Do While Not rs.EOF
strEmail = rs!ContactEmail

If Trim(strEmail & "")<>"" Then
  DoCmd.SendObject , , , strEmail, , , "Your Order Confirmation from MY COMPANY NAME HERE", "Test", True
Else
  'No email address
End If 

rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Function

Please use [ignore]

[/ignore] tags. You will find more detail in the Process TGML link.

 

[hlkelly]

Outstanding.

Now...how do I add the different variables to the body of the message? Say I have an order number I want to put in the body? I see where I can change the body of the message ("test"). Something like this?

strbody = strbody & "Order #: " & OrderID & Chr(13)

 

[Remou]

That is the general idea. You probably want the Order # from the recordset, so:

[tt]strbody = strbody & "Order #: " & rs!OrderID & vbCrLf[/tt]

Chr(13) is just 'carriage return', for the most part, you also need 'line feed' Chr(10), so vbCrLf is convenient.