Email report automatically to every ID but with own ID

**Ruegen** · 11-21-2013, 10:42 PM

I am using

Code:

Private Sub Command4_Click()


Dim db As dao.Database
Dim rs As dao.Recordset
Dim strsql As String




strsql = "SELECT tblPersons.ID, tblPersons.Title, tblPersons.Absent, tblPersons.PersonChecked, tblPersons.Email " & vbCrLf & _
"FROM tblPersons;"


Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)


rs.MoveFirst
Do Until rs.EOF
rs.Edit
If rs!Absent = True Then
rs!PersonChecked = True




DoCmd.OpenReport "rptPersons", acViewPreview, , "ID=" & rs!ID
DoCmd.SendObject acSendReport, "rptPersons", "PDFFormat(*.pdf)", rs!Email, , , "test email please delete", rs!Title, no
DoCmd.Close acReport, "rptPersons", acSaveNo
End If






rs.Update
rs.MoveNext
Loop
MsgBox "all done"
rs.Close
Set rs = Nothing
End Sub

however with this method it has to open up each report first then email it - I would prefer it without having to open the report in order to filter it by id

I was hoping to change the reports record source instead and never docmd.openreport

is there a way around this?