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

[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

 

[Gary Miller]

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
________________________

 

[Dirk Goldgar]

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

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.