How do I loop to get all the e-mail addresses in the TO: line instead of one at a time?

  • Thread starter Thread starter Rick's News
  • Start date Start date
R

Rick's News

How do I loop to get all the e-mail addresses in the TO: line instead of one
at a time? I want to send this report to everyone in the report. Not one
record at a time...

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE camsid Is Not
Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF, rs!Email,
"Recurring Training Forecast For the Next 14 Days. ", "If you are in the To
line of the e-mail, you have training due.", EditMessage:=True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
 
You need to move your SendObject outside the loop and then
put a variable inside the loop to append all of the address
to. You also have the problem of the undeclared variable
that I addressed in the Forms group. Here is a revision...

Dim rs As DAO.Recordset
Dim strSQL As String
Dim gstrReportFilter As String
Dim strEmail As String

strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE
camsid Is Not
Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
strEmail = strEmail & rs!Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF,
strEmail,
"Recurring Training Forecast For the Next 14 Days. ", "If
you are in the To
line of the e-mail, you have training due.",
EditMessage:=True
rs.Close
Set rs = Nothing


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Gary Miller said:
You need to move your SendObject outside the loop and then
put a variable inside the loop to append all of the address
to. You also have the problem of the undeclared variable
that I addressed in the Forms group. Here is a revision...

Dim rs As DAO.Recordset
Dim strSQL As String
Dim gstrReportFilter As String
Dim strEmail As String

strSQL = "SELECT CAMSid, Email FROM [WorkcenterRoster] WHERE
camsid Is Not
Null;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "CAMSid = " & rs!CamsID
strEmail = strEmail & rs!Email & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendReport, "CAMS DUE", acFormatRTF,
strEmail,
"Recurring Training Forecast For the Next 14 Days. ", "If
you are in the To
line of the e-mail, you have training due.",
EditMessage:=True
rs.Close
Set rs = Nothing
Click to expand...

I think you'll find that code will only create the report for the last
value of CAMSid in the recordset. The filter, too, must be brought out
of the loop.
 
Back
Top