VBA - Loop through query and email reports

[Basenji3]

I'm a novice with VBA in Access and I have ran into a problem. If someone has some ideas I would appreciate it.

I have a list of managers in the "tblReportsTo" table (reportsTo is the field). I need to loop through this table, grabbing data from the "tbl5ManagerApproval" table, using the query "qry5MgrAppv" and generate a report(rpt5MgrApporval)which is then emailed to that manger.

What I have for code is below:

Private Sub Command58_Click()
On Error GoTo Exit_Command58_Click
 
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblReportsTo") 'table
Dim stDocName As String
Dim strSQL As String
 
stDocName = "rpt5MgrApproval"
 
    DoCmd.SetWarnings False
 
    rs.MoveFirst
    Do While Not rs.EOF
 
    strSQL = "SELECT * FROM tblMgrApproval WHERE reportsto= """ & rs!reportsto & """"
 
    DoCmd.SendObject acReport, stDocName, "PdfFormat(*.pdf)", rs!emailAddress, "", "", "Manager Approval", "", True, """"""
    rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
    MsgBox "Reports Completed", vbOKOnly, "Month End PTO"
 
Exit_Command58_Click:
    If MsgBox("Do you want to cancel the run?", vbQuestion Or vbYesNo) = vbYes Then
        Exit Sub
    Else
        Resume Next
    End If
End Sub

[jimatqsi]

I work in Access 2003 and 2007. It could be there are other ways to do this in the newer versions. But here are the issues I have to deal with to do what you are doing.

Create a pdf to attach. I do that either by designing the report to go to a specific printer, which is a .pdf printer. Depending on the .pdf driver you use, the pdf file created may be a common name (same every time) or it may vary. You'll want to use one that can be configured to not ask any questions during the pdf creation. I use the Bullzip driver.

If you know the name of the file that will created, simple. If not, you will have to loop through the file names in the folder that will be used (using Dir() command) to find the file name to be attached to your mail.

If you cannot design your report to use a specific pdf printer, perhaps because people use the report under other circumstances, then you have add some logic to save the current system default printer, change the default printer to your pdf printer, print the report, and then change the system default printer back.

So there's a lot of unknowns for me here, and I don't want to go into too much detail without knowing what your situation is. If you could fill in some greater detail, I or someone else here can give more help.

Jim