Still not quite sure, but this loops through each record in T_Inspectors and grabs the email address. This will be the TO address. No search criteria.
Then the report opens to the FULL query. No filtered results. No criteria
Code:
Dim strCriteria As String
Dim strDate As String
Dim strEmail As String
'strDate = "#10/13/2013#" 'we need to retreive this from a control on a form
strCriteria = "[Date]=" & strDate 'This is what will generate the PDF. It only matches the date and not the Employee ID1
'DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, ,strDate , acHidden 'THis depends on a date assigned to strDate
DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , , acHidden 'This depends on the query to select the correct date and report
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("T_Inspectors", dbOpenDynaset) 'We need an email address
rs.MoveFirst
Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails
strEmail = rs![Email]
DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
DoCmd.Close acReport, "R_WeeklyDispatch_Today"