More help please on email mass recipients from Access

[dawnd3]

Here is my previous post thread181-27567

So with the help of a tek-tips and my wonderful husband I got as far as creating code that emails each person in a recordset separetly and attaches a report. The only problem we are facing now is that the SendObject doesn't have a Where condition to filter the report. (I only want each recipient to get their specific data.) Here is the code and I would love if you have any ideas. Is there something other than SendObject that I can use that will allow for filtering the report?

Thanks

Dawn

Private Sub Command8_Click()
On Local Error GoTo Some_Err

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
Dim strTo As String

Set MyDB = DBEngine.Workspaces(0).Databases(0)

Set RS = MyDB.OpenRecordset _
("qryRosterUpdate")
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No member email addresses found.", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
lngCount = lngCount + 1
strTo = RS!Email
DoCmd.SendObject acSendReport, rptRosterUpdate, acFormatRTF, Me!, , , "Please Verify your MDMC Roster Information", "Dear " & Me![FirstName] & ", The MDMC Rosters will available at the next MDMC meeting. Please review the attached document and verify that I have your information correct. If you have any changes or corrections please email them to me by Thursday, June 22nd midnight. Thank you!", 0

RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close

MsgBox "Done sending " & CStr(lngRSCount) & " member emails. ", vbInformation, "Done"
Exit Sub

Some_Err:
MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"
End Sub


[URL unfurl="true"]www.calliopeconsulting.net[/URL]

 

[JerryKlmns]

Open the report using the WhereCondition and on SendObject method do not specify the report name. That way access shall use the active report. Close the report before going to the next record. I guess you have a MemberID field in the report to filter it!

DoCmd.OpenReport rptRosterUpdate, acViewPreview, , "MemberID=" & RS.Fields("MemberID")
DoCmd.SendObject acSendReport, , acFormatRTF, Me![Email], , , "Please Verify your MDMC Roster Information", "Dear " & Me![FirstName] & ", The MDMC Rosters will available at the next MDMC meeting. Please review the attached document and verify that I have your information correct. If you have any changes or corrections please email them to me by Thursday, June 22nd midnight. Thank you!", 0
DoCmd.Close acReport, rptRosterUpdate, acSaveNo

 

[SeeThru]

Similar to Jerry...

I've used a form that opens each report in turn and emails it, closes it and then re-opens with new criteria.