Multiple Reports Emailed to Multiple Recipients

[mazintech]

i have multiple reports (Report1, 2 , 3 ... 8) auto generated and saved to a file C:\Reports. can someone assist with code on one Click to a button to send these to multiple recipients in tblEmployees with the EmployeeEmail Field containing email addresses of the recipients Report1 sent to MrXXX with [e-mail], Report2 sent to Mryyy with [e-mail]. i am using ms access2013.

[Seth Schrock]

Here is a link to similar question: How to send Email with Attachment(s) within Access. Give it a shot and if you have any questions just let us know.

[mazintech]

thanx Seth but the code seems similar to what i have which is sending a single email to multiple recipients. what i am looking for is a way to send multiple emails to multiple recipients. each email sent to a specific recipient in the database attaching a report.

[Seth Schrock]

Is each report going to multiple people or just one person?

[mazintech]

each report is going to a single person (regional manager). point to note these report contain regional information used by different regional managers in the table tblEmployees.

[mazintech]

here is the code that i have which is perfectly sending the report to multiple recipients

 Private Sub Command24_Click()
 
 
    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim strAddr As String
    Dim MyDB As DAO.Database
    Dim rstEMail As DAO.Recordset
    Dim FileName As String
    Dim FilePath As String
 
    'Generate a Report
        FileName = Me.Name & "_ID "
        FilePath = "C:\Reports\" & FileName & ".pdf"
        DoCmd.OutputTo acOutputReport, "rptBranches", acFormatPDF, FilePath
        MsgBox "Report has been saved succesfully", vbOKOnly, "Save confirmed"
 
    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)
 
    'Retrieve all E-Mail Addressess in Regional_Managers
    Set MyDB = CurrentDb
    Set rstEMail = MyDB.OpenRecordset("Select * From Regional_Managers", dbOpenSnapshot, dbOpenForwardOnly)
 
    With rstEMail
      Do While Not .EOF
        'Build the Recipients String
        strEMail = strEMail & ![EmailAddress] & ";"
          .MoveNext
      Loop
    End With
    '--------------------------------------------------
 
    With oMail
      .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
      .Body = "Test E-Mail to Multiple Recipients"
      .Subject = "Yada, Yada, Yada"
      .Attachments.Add FilePath
        .Display
    End With
 
    Set oMail = Nothing
    Set oOutlook = Nothing
 
    rstEMail.Close
    Set rstEMail = Nothing
    Kill FilePath
 
End Sub
 

[Seth Schrock]

So then you will need to have a table that has the report name and the email address that it will go to and (optionally) the File name that you want used. You would then place your existing code inside a loop that goes through each record getting the report name and the email address from the table.

[mazintech]

is there no other way to alter the current code i have

[Seth Schrock]

You can copy your existing code and paste it at the end for every report. You would have to hard code the email addresses then.

[jforbes]

Seth is right, one way or another you'll need a way to map which Report goes to which Manager. By itself, Access can't pull that off. If you already have that relationship built somehow in your database, like a table, query or function, then it might be possible to reuse it. If that information isn't defined in any way, then it would need to be defined to do what you are requesting.

[mazintech]

Hi Buddies. i hope you had a great weekend. I have created the query RMsReports with ReportNames and EmailAddresses from the tblEmployees table as you advised. Now its coding time i cant seem to figure out where exactly am i going to put the loop. may modify my code please.

[Seth Schrock]

Start the loop on line 12 and end the loop on line 49. You would then replace the static report name in line 16 with the value from the query and then obviously the To email address.

[mazintech]

Seth please help i am stuck here. I am confused and lost completely. this is the actual code i am working on.

Private Sub SADM_Weekly_Performance_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim mypath As String
 
    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim rstEMail As DAO.Recordset
    Dim attach As String
    Dim address As String
 
    mypath = "C:\Users\EWM\Desktop\Auto\SADM Wkly Performance - "
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT distinct [Region] FROM [SADM Performance Per Week Query]", dbOpenSnapshot)
 
'--------------------------------Generate Split and save the report pdf file---------------------------------------------------------------
    Do While Not rs.EOF  '
        temp = rs("Region")
        MyFileName = rs("Region")
 
        Debug.Print "Generated Path for " & temp & " - " & mypath & MyFileName
 
        DoCmd.OpenReport "sadm performance per week", acViewReport, , "[Region]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName & Format(Date, "dd/mmmm/yy") & ".pdf"
        DoCmd.Close acReport, "sadm performance per week"
        DoEvents
 
        rs.MoveNext
 
        Loop
'--------------------------------------------------------------------------------------------------------------------------------
 
    Set rstEMail = db.OpenRecordset("Select * From RMsReports", dbOpenSnapshot, dbOpenForwardOnly)
 
    With rstEMail
      Do While Not .EOF
 
       Set oOutlook = CreateObject("Outlook.Application")
       Set oMail = oOutlook.CreateItem(0)
 
        'Build the Recipient String
        address = ![EmailAddress] & ";"
 
        'Build attachment String
        attach = ![Path] & ".pdf"
 
          With oMail
             .To = address
             .Body = "Please find attached weekly SADM Performance Report"
             .Subject = MyFileName
             .Attachments.Add = attach
             .Display
          End With
 
        Set oMail = Nothing
        Set oOutlook = Nothing
 
       .MoveNext
     Loop
    End With
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Sub
 

[mazintech]

Mates i finally got it thanks to Seth's idea. Here is my final Code

Option Compare Database
 
Private Sub SADM_Weekly_Performance_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim mypath As String
 
    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim rstEMail As DAO.Recordset
    Dim attach As String
 
 
    mypath = "C:\Auto\SADM Wkly Performance - "
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT distinct [Region] FROM [SADM Performance Per Week Query]", dbOpenSnapshot)
 
'--------------------------------Generate Split and save the report pdfs to file---------------------------------------------------------------
    Do While Not rs.EOF  '
        temp = rs("Region")
        MyFileName = rs("Region")
 
        Debug.Print "Generated Path for " & temp & " - " & mypath & MyFileName
 
        DoCmd.OpenReport "sadm performance per week", acViewReport, , "[Region]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName & Format(Date, "dd/mmmm/yy") & ".pdf"
        DoCmd.Close acReport, "sadm performance per week"
        DoEvents
 
        rs.MoveNext
 
        Loop
'--------------------------------------------------------------------------------------------------------------------------------
 
    Set rstEMail = db.OpenRecordset("Select * From RMsReports", dbOpenSnapshot, dbOpenForwardOnly)
 
    With rstEMail
      Do While Not .EOF
 
       Set oOutlook = CreateObject("Outlook.Application")
       Set oMail = oOutlook.CreateItem(0)
 
        'Build the Recipient String
        strEMail = ![EmailAddress] & ";"
 
        'Build attachment String
        attach = ![Path] & ".pdf"
 
          With oMail
             .To = Left$(strEMail, Len(strEMail) - 1)
             .Body = "Please find attached weekly SADM Performance Report"
             .Subject = MyFileName & " SADM Weekly Performance Report"
             .Attachments.Add attach
             .Display
          End With
 
        Set oMail = Nothing
        Set oOutlook = Nothing
 
       .MoveNext
     Loop
    End With
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
End Sub
 

[Seth Schrock]

Glad you got it to work. Good luck on the rest of your project!