By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Multiple Reports Emailed to Multiple Recipients

P: 10
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 email-mrxxx@gmail.com, Report2 sent to Mryyy with email-mryyy@yahoo.com. i am using ms access2013.
May 15 '15 #1
Share this Question
Share on Google+
14 Replies


Seth Schrock
Expert 2.5K+
P: 2,939
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.
May 15 '15 #2

P: 10
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.
May 15 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,939
Is each report going to multiple people or just one person?
May 15 '15 #4

P: 10
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.
May 15 '15 #5

P: 10
here is the code that i have which is perfectly sending the report to multiple recipients
Expand|Select|Wrap|Line Numbers
  1.  Private Sub Command24_Click()
  2.  
  3.  
  4.     Dim strEMail As String
  5.     Dim oOutlook As Object
  6.     Dim oMail As Object
  7.     Dim strAddr As String
  8.     Dim MyDB As DAO.Database
  9.     Dim rstEMail As DAO.Recordset
  10.     Dim FileName As String
  11.     Dim FilePath As String
  12.  
  13.     'Generate a Report
  14.         FileName = Me.Name & "_ID "
  15.         FilePath = "C:\Reports\" & FileName & ".pdf"
  16.         DoCmd.OutputTo acOutputReport, "rptBranches", acFormatPDF, FilePath
  17.         MsgBox "Report has been saved succesfully", vbOKOnly, "Save confirmed"
  18.  
  19.     Set oOutlook = CreateObject("Outlook.Application")
  20.     Set oMail = oOutlook.CreateItem(0)
  21.  
  22.     'Retrieve all E-Mail Addressess in Regional_Managers
  23.     Set MyDB = CurrentDb
  24.     Set rstEMail = MyDB.OpenRecordset("Select * From Regional_Managers", dbOpenSnapshot, dbOpenForwardOnly)
  25.  
  26.     With rstEMail
  27.       Do While Not .EOF
  28.         'Build the Recipients String
  29.         strEMail = strEMail & ![EmailAddress] & ";"
  30.           .MoveNext
  31.       Loop
  32.     End With
  33.     '--------------------------------------------------
  34.  
  35.     With oMail
  36.       .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  37.       .Body = "Test E-Mail to Multiple Recipients"
  38.       .Subject = "Yada, Yada, Yada"
  39.       .Attachments.Add FilePath
  40.         .Display
  41.     End With
  42.  
  43.     Set oMail = Nothing
  44.     Set oOutlook = Nothing
  45.  
  46.     rstEMail.Close
  47.     Set rstEMail = Nothing
  48.     Kill FilePath
  49.  
  50. End Sub
  51.  
May 15 '15 #6

Seth Schrock
Expert 2.5K+
P: 2,939
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.
May 15 '15 #7

P: 10
is there no other way to alter the current code i have
May 15 '15 #8

Seth Schrock
Expert 2.5K+
P: 2,939
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.
May 15 '15 #9

jforbes
Expert 100+
P: 1,107
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.
May 15 '15 #10

P: 10
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.
May 18 '15 #11

Seth Schrock
Expert 2.5K+
P: 2,939
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.
May 18 '15 #12

P: 10
Seth please help i am stuck here. I am confused and lost completely. this is the actual code i am working on.
Expand|Select|Wrap|Line Numbers
  1. Private Sub SADM_Weekly_Performance_Click()
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim MyFileName As String
  5.     Dim temp As String
  6.     Dim mypath As String
  7.  
  8.     Dim strEMail As String
  9.     Dim oOutlook As Object
  10.     Dim oMail As Object
  11.     Dim rstEMail As DAO.Recordset
  12.     Dim attach As String
  13.     Dim address As String
  14.  
  15.     mypath = "C:\Users\EWM\Desktop\Auto\SADM Wkly Performance - "
  16.  
  17.     Set db = CurrentDb()
  18.     Set rs = db.OpenRecordset("SELECT distinct [Region] FROM [SADM Performance Per Week Query]", dbOpenSnapshot)
  19.  
  20. '--------------------------------Generate Split and save the report pdf file---------------------------------------------------------------
  21.     Do While Not rs.EOF  '
  22.         temp = rs("Region")
  23.         MyFileName = rs("Region")
  24.  
  25.         Debug.Print "Generated Path for " & temp & " - " & mypath & MyFileName
  26.  
  27.         DoCmd.OpenReport "sadm performance per week", acViewReport, , "[Region]='" & temp & "'"
  28.         DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName & Format(Date, "dd/mmmm/yy") & ".pdf"
  29.         DoCmd.Close acReport, "sadm performance per week"
  30.         DoEvents
  31.  
  32.         rs.MoveNext
  33.  
  34.         Loop
  35. '--------------------------------------------------------------------------------------------------------------------------------
  36.  
  37.     Set rstEMail = db.OpenRecordset("Select * From RMsReports", dbOpenSnapshot, dbOpenForwardOnly)
  38.  
  39.     With rstEMail
  40.       Do While Not .EOF
  41.  
  42.        Set oOutlook = CreateObject("Outlook.Application")
  43.        Set oMail = oOutlook.CreateItem(0)
  44.  
  45.         'Build the Recipient String
  46.         address = ![EmailAddress] & ";"
  47.  
  48.         'Build attachment String
  49.         attach = ![Path] & ".pdf"
  50.  
  51.           With oMail
  52.              .To = address
  53.              .Body = "Please find attached weekly SADM Performance Report"
  54.              .Subject = MyFileName
  55.              .Attachments.Add = attach
  56.              .Display
  57.           End With
  58.  
  59.         Set oMail = Nothing
  60.         Set oOutlook = Nothing
  61.  
  62.        .MoveNext
  63.      Loop
  64.     End With
  65.  
  66.     rs.Close
  67.     Set rs = Nothing
  68.     Set db = Nothing
  69.  
  70. End Sub
  71.  
May 19 '15 #13

P: 10
Mates i finally got it thanks to Seth's idea. Here is my final Code
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub SADM_Weekly_Performance_Click()
  4.     Dim db As DAO.Database
  5.     Dim rs As DAO.Recordset
  6.     Dim MyFileName As String
  7.     Dim temp As String
  8.     Dim mypath As String
  9.  
  10.     Dim strEMail As String
  11.     Dim oOutlook As Object
  12.     Dim oMail As Object
  13.     Dim rstEMail As DAO.Recordset
  14.     Dim attach As String
  15.  
  16.  
  17.     mypath = "C:\Auto\SADM Wkly Performance - "
  18.  
  19.     Set db = CurrentDb()
  20.     Set rs = db.OpenRecordset("SELECT distinct [Region] FROM [SADM Performance Per Week Query]", dbOpenSnapshot)
  21.  
  22. '--------------------------------Generate Split and save the report pdfs to file---------------------------------------------------------------
  23.     Do While Not rs.EOF  '
  24.         temp = rs("Region")
  25.         MyFileName = rs("Region")
  26.  
  27.         Debug.Print "Generated Path for " & temp & " - " & mypath & MyFileName
  28.  
  29.         DoCmd.OpenReport "sadm performance per week", acViewReport, , "[Region]='" & temp & "'"
  30.         DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName & Format(Date, "dd/mmmm/yy") & ".pdf"
  31.         DoCmd.Close acReport, "sadm performance per week"
  32.         DoEvents
  33.  
  34.         rs.MoveNext
  35.  
  36.         Loop
  37. '--------------------------------------------------------------------------------------------------------------------------------
  38.  
  39.     Set rstEMail = db.OpenRecordset("Select * From RMsReports", dbOpenSnapshot, dbOpenForwardOnly)
  40.  
  41.     With rstEMail
  42.       Do While Not .EOF
  43.  
  44.        Set oOutlook = CreateObject("Outlook.Application")
  45.        Set oMail = oOutlook.CreateItem(0)
  46.  
  47.         'Build the Recipient String
  48.         strEMail = ![EmailAddress] & ";"
  49.  
  50.         'Build attachment String
  51.         attach = ![Path] & ".pdf"
  52.  
  53.           With oMail
  54.              .To = Left$(strEMail, Len(strEMail) - 1)
  55.              .Body = "Please find attached weekly SADM Performance Report"
  56.              .Subject = MyFileName & " SADM Weekly Performance Report"
  57.              .Attachments.Add attach
  58.              .Display
  59.           End With
  60.  
  61.         Set oMail = Nothing
  62.         Set oOutlook = Nothing
  63.  
  64.        .MoveNext
  65.      Loop
  66.     End With
  67.  
  68.     rs.Close
  69.     Set rs = Nothing
  70.     Set db = Nothing
  71.  
  72. End Sub
  73.  
May 19 '15 #14

Seth Schrock
Expert 2.5K+
P: 2,939
Glad you got it to work. Good luck on the rest of your project!
May 19 '15 #15

Post your reply

Sign in to post your reply or Sign up for a free account.