470,632 Members | 1,634 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Access Report to Send to Multiple Recipients

5 Nibble
I have a report in Access that is grouped by customer with a page break after each customer so only their data is shown on the page. I would like to email the report to each customer with their individual data(page). I can get Access to email to each customer, but it emails the entire report and does not email only the specific customer. I attempted to create a Module to handle this and while it does email, it sends an email for each line of data even if it is the same customer and only sends the email text and not the details from the report. Below is the code:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SendSerialEmail()
  2.  
  3.  Dim db As DAO.Database
  4.  Dim rs As DAO.Recordset
  5.  
  6.  Dim emailTo As String
  7.  Dim emailSubject As String
  8.  Dim emailText As String
  9.  
  10.  Dim outApp As Outlook.Application
  11.  Dim outMail As Outlook.MailItem
  12.  Dim outlookStarted As Boolean
  13.  
  14.  On Error Resume Next
  15.  Set outApp = GetObject(, "Outlook.Application")
  16.  On Error GoTo 0
  17.  If outApp Is Nothing Then
  18.      Set outApp = CreateObject("Outlook.Application")
  19.      outlookStarted = True
  20.  End If
  21.  Set db = CurrentDb
  22.  Set rs = db.OpenRecordset("SELECT Pay, Bill, Datepaid, Totalamount, Email FROM Query1")
  23.  Do Until rs.EOF
  24.      emailTo = rs.Fields("Email").Value
  25.  
  26.      emailSubject = "YTD Transactions"
  27.  
  28.      emailText = emailText & _
  29.                  "Below is your year to date transactions."
  30.  
  31.  
  32.      Set outMail = outApp.CreateItem(olMailItem)
  33.      outMail.To = emailTo
  34.      outMail.Subject = emailSubject
  35.      outMail.Body = emailText
  36.      outMail.Send
  37.  
  38.      rs.MoveNext
  39.  Loop
  40.  
  41.  rs.Close
  42.  Set rs = Nothing
  43.  Set db = Nothing
  44.  
  45.    If outlookStarted Then
  46.      outApp.Quit
  47.  End If
  48.  
  49.  Set outMail = Nothing
  50.  Set outApp = Nothing
  51. End Sub
I have a table, query and report with Pay (customer), Bill (invoice number), Datepaid, Totalamount, Email columns.

Any help would be greatly appreciated.

Thanks
1 Week Ago #1
11 7268
zmbd
5,444 Expert Mod 4TB
Hello kjhyde
Welcome to Bytes.
I've moved your thread to the Access forum as your question applies more directly to Access and VBA instead of VB6

You have the basics down and I think you will find the following information to also be helpfull:
Application Automation.

Now for your particular situation:
Line22 has your recordset - impossible to tell what it is actually returning to your code; however, from your post I have a fairly good idea that it's just a raw record dump of all your customers.
You will need an outer loop with your customer base and then a filtered query to run the individualized emails.

There is a thread where I've answered a very similar question with the attached database
Open the form in design view
Show the properties for the [Send the Emails] button
Show the code for the [On Click] event of the button
Scroll down to the Private Sub z_send_single_email_to_each_person_with_all_querie s() script which will walk you thru one method of accomplishing what you are after.
+ What I do in this code is create an Excel workbook from the query and then add records to the workbook for the person of interest in the loop
+ The workbook is then attached to an email and then sent.
++ This is only one method - please don't focus too much on the workbook aspect.
++ Instead, you could pull the information form the query and build a string that is then inserted into the body of of the email
++ I have code somewhere for a dynamic report that's emailed too... I'll have to find that database, it's been a long time since I've used it as most of my staff simply view the reports online
Attached Files
File Type: zip Bytes_944237.zip (105.8 KB, 12 views)
1 Week Ago #2
kjhyder
5 Nibble
Thank you for your response. I did not make my question clear in my original post. I have a report in Access that is grouped by customer listing out each invoice, payment, and payment date over the course of the year. The report is subtotaled by customer. I have also added page breaks after each customer. If I were to print the report, I would get an individual pages showing only one customer's data on each page. I would like the ability to create a "Send Email" button on the report that would generate emails to each customer either attaching their unique page of the report or inserting their unique data into the email body. I do have a table with each customer and customer email. I would also like the process from the "Send Email" button to send to each customer automatically without me having to send to each customer individually.
1 Week Ago #3
zmbd
5,444 Expert Mod 4TB
🌟 Reports should be exactly that - a report.
In an ideal world, reports should never have "command buttons" instituting an action therefrom, actions are the province of the user-form.
Splitting your current report along the page/groupings, AFAIK, isn't possible, even within the on_page event.
Your base query should set the data that you want to have sent to the customer to receive in the report.

🏁 You need to decide if you want to send the report and/or create text within the body of the email

📝📩If you goal is to simply send the information from within the email then my example database loop is all you need to get you started with steping thru the data set.
For each customer in the outer loop, use the current customer primary key to filter/create the record set from the data table(s) based on the related foreign key
Pull from the record set to create your string for the body text. The exact code will vary based upon your data, business model, and desired output.

✉📓If however, you wish to send the report that is a slightly different ball of wax. You will need to create a core-report that shows the information with your totals etc... from there we would need to set the parameters for your query that feeds the report, use a "WhereCondition" in a DoCmd.OpenReport, (even OpenArgs is a possibility) (so many ways to do this), and then do the fancy email automation.
♻More than likely we can actually use the report that you have using the "WhereCondition" in the DoCmd.OpenReport method.
1 Week Ago #4
kjhyder
5 Nibble
Thank you zmbd.

My goal is to simply send the information from within the email. So if my table consists of the following data:
Pay Bill Date Datepaid Totalamount Email
Ali 123 Street 3/18/22 3/21/22 100.00 a@abc.com
Ali 456 Street 3/14/22 200.00 a@abc.com
Brent 789 Street 3/11/22 3/13/22 100.00 b@abc.com
Brittany 123 S Street 2/21/22 2/23/22 100.00 b2@abc.com
Brittany 456 S Street 1/15/22 1/16/22 200.00 b2@abc.com
Bryan 789 S Street 2/25/22 200.00 b3@abc.com

I would like to email Ali at a@abc.com (in one email) their data of 123 Street and 456 Street (with 300.00 subtotal if possible)
email Brent at b@abc.om their data
email Brittany at b1@abc.com their data (in one email)
email Bryan at 2b@abc.com their data

And accomplish this without having to manually select each agent and send their data

Is that possible with my original code and adding your Private Sub z_send_single_email_to_each_person_with_all_querie s() code?

Thank you
3 Days Ago #5
ADezii
8,804 Expert 8TB
Forgive me for jumping in late, but the following Code, based on your Data (tblData), will send (Display) four unique EMails to your Customers. The Body of the EMail will contain the Data unique to each as well as a Total for TotalAmount. It was literally thrown together, is crude, and can definitely be improved upon. For the sake of brevity, no Error Checking, Resetting of Variables, etc. is performed.
Expand|Select|Wrap|Line Numbers
  1. Dim objOut As Object
  2. Dim objMail As Object
  3. Dim olns As Object
  4. Dim strSQL1 As String
  5. Dim strSQL2 As String
  6. Dim MyDB As DAO.Database
  7. Dim rstUnique As DAO.Recordset
  8. Dim rstData As DAO.Recordset
  9. Dim curTotal As Currency
  10. Dim strBuild As String
  11. Dim strBody As String
  12. Dim strCustomer As String
  13. Dim strTO As String
  14.  
  15. Set objOut = CreateObject("Outlook.Application")
  16. Set olns = objOut.GetNamespace("MAPI")
  17.  
  18. Set MyDB = CurrentDb
  19.  
  20. strSQL1 = "SELECT DISTINCT [Pay] FROM tblData ORDER BY [Pay]"       'Unique individuals
  21.  
  22. Set rstUnique = MyDB.OpenRecordset(strSQL1, dbOpenForwardOnly)
  23.  
  24. With rstUnique
  25.   Do While Not .EOF
  26.     Set rstData = MyDB.OpenRecordset("SELECT * FROM tblData WHERE [Pay] ='" & ![Pay] & "'", dbOpenSnapshot)
  27.       strCustomer = rstData![Pay]
  28.       strTO = rstData![email]
  29.         Do While Not rstData.EOF
  30.           Set objMail = objOut.CreateItem(0)
  31.           curTotal = curTotal + rstData![TotalAmount]
  32.           strBuild = strBuild & rstData![Bill] & ", "
  33.             rstData.MoveNext
  34.         Loop
  35.           objMail.To = strTO
  36.           objMail.Subject = "My Demo"
  37.           objMail.Body = "Data for Customer " & strCustomer & ", " & Left(strBuild, Len(strBuild)-2) & _
  38.                          "for a Total of " & Format(curTotal, "Currency")
  39.             objMail.Display
  40.           curTotal = 0    'RESET for next Customer
  41.           strBuild = ""   'RESET Address
  42.             .MoveNext
  43.   Loop
  44. End With
  45.  

Attached Images
File Type: jpg Capture.JPG (47.0 KB, 30 views)
3 Days Ago #6
zmbd
5,444 Expert Mod 4TB
@kjhyder
+ ADezii has bodged a fair bit of code for you
+ More than likely the routine I've put together in the first attachment will work as you've asked; HOWEVER, If instead you would like a report attachment let us know and I'll post a second database that does this for you using the "WhereCondition" in the DoCmd.OpenReport method later this evening

@ADezii
You're never too late to the party, if you don't come, how are you supposed to have cake and icecream?!
2 Days Ago #7
ADezii
8,804 Expert 8TB
Thanks for the invite, zmbd, NeoPa and I go way back as 'Party Animals'! (LOL)
2 Days Ago #8
ADezii
8,804 Expert 8TB
@kjhyder:
You can actually have a single Code Segment that will cover both Options for you (EMail/Report). This Code would exist in a Sub-Routine with a single Argument indicating whether to place the Data in the Body of the EMail, or attach a Report with the Data instead. The Declaration would look like:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SendEMails(blnAttachReport As Boolean)
  2.   'Main Code segment here
  3. End Sub
  4.  
  5. Call SendEMails(True)         'Attach Report to EMail
  6. Call SendEMails(False)        'Put Data in Body of EMail
  7.  
2 Days Ago #9
kjhyder
5 Nibble
Thank you both zmbd and ADezii.

Outside of some formatting on the email body, I cam very close here.

In case the powers that be want to change their mind and have the information dispersed in an attached PDF instead of the email body, could you post the second database you mentioned with the "WhereCondition" in the DoCmd.OpenReport method?

Thanks
2 Days Ago #10
zmbd
5,444 Expert Mod 4TB
In case the powers that be want to change their mind and have the information dispersed in an attached PDF instead of the email body, could you post the second database you mentioned with the "WhereCondition" in the DoCmd.OpenReport method?
Oh... that NEVER happens (ROTFL)
See attached....

Open the form, click the second button... first button just rehashes what the first database so that you have it all in one place.
Of course, design view the form, view the event code...

The report isn't pretty, it's just to show one way to make things happen, I have much fancier ones for work; however, those go out to the corporate staff and one does need to put on the Ritz.
Attached Files
File Type: zip Bytes_977301.zip (150.5 KB, 4 views)
2 Days Ago #11
kjhyder
5 Nibble
Just as you might have guessed, they would like the report as a pdf attachment. I have used your code and updated accordingly. I am receiving the following error.

Select VBNO
Error Number: 3075
Error Detail: Syntax error (missing operator) in query expression 'Brent Kendrick'.

Brent is the first name in my table. I run the debug and nothing comes up. Below is the code I have adapted from yours.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5.  
  6. Private Sub Command2_Click()
  7. '
  8.  
  9. Dim zdb As DAO.Database
  10. Dim zq As DAO.QueryDef
  11. Dim zrs_email As DAO.Recordset
  12. Dim zvbyesno As Long, znumber As Long
  13. Dim zsql As String, zfname As String, zlname As String, zemail As String
  14. Dim zmessage As String, zmessagelead As String, zsubject As String
  15. Dim zWhere As String
  16. '
  17. On Error GoTo z_error_trap
  18. '
  19.  
  20. zvbyesno = MsgBox("[Yes] You are about to send out a lot of" & vbCrLf & _
  21.                   "      emails to people in the list." & vbCrLf & _
  22.                   "      I understand:" & vbCrLf & _
  23.                   "      This may result in my people flogging me" & vbCrLf & _
  24.                   "      and may also result in the ISP tagging me" & vbCrLf & _
  25.                   "      as a SPAM-BOT thus shuting me down!" & vbCrLf & _
  26.                   vbCrLf & _
  27.                   "[NO] Please do not do anything.", vbDefaultButton2 + vbYesNo, _
  28.                   "Please Confirm the Intent To Flood the Internet...")
  29. '
  30. If zvbyesno = vbYes Then
  31.  
  32.     Set zdb = CurrentDb
  33.     '
  34.  
  35.     zsql = "SELECT YTD.Pay," & _
  36.            " YTD.email" & _
  37.            " FROM YTD" & _
  38.            " WHERE (((YTD.email) Is Not Null));"
  39.     Set zrs_email = zdb.OpenRecordset(zsql, dbOpenDynaset)
  40.     '
  41.  
  42.     If zrs_email.RecordCount > 0 Then
  43.  
  44.         zrs_email.MoveLast
  45.         zrs_email.MoveFirst
  46.         '
  47.  
  48.         Do
  49.  
  50.             zfname = zrs_email![Pay]
  51.             zemail = zrs_email![email]
  52.             zsubject = "YTD Transactions"
  53.             '
  54.  
  55.             zmessage = "Dear " & zfname & ":" & vbCrLf & _
  56.                 "Attched is your YTD transactions"
  57.             '
  58.  
  59.             zWhere = zrs_email![Pay]
  60.             DoCmd.OpenReport ReportName:="RYTD", View:=acViewPreview, WhereCondition:=zWhere
  61.             Reports("RYTD").Visible = False
  62.             '
  63.  
  64.             DoCmd.SendObject objecttype:=acSendReport, objectname:="RYTD", Outputformat:=acFormatPDF, _
  65.               To:=zemail, Subject:=zsubject, messagetext:=zmessage, EditMessage:=True
  66.             '
  67.  
  68.             DoCmd.Close objecttype:=acReport, objectname:="RYTD", Save:=acSaveNo
  69.             '
  70.  
  71.             zrs_email.MoveNext
  72.  
  73.         Loop Until zrs_email.EOF
  74.     Else
  75.  
  76.         Err.Raise Number:=vbObjectError + 3297, Description:="No Records Returned With emails"
  77.     End If
  78. Else
  79.     Exit Sub
  80. End If
  81. '
  82.  
  83. z_return_from_error:
  84. If zdb Is Nothing Then
  85.     Exit Sub
  86. Else
  87.  
  88.     If Not zrs_email Is Nothing Then
  89.       zrs_email.Close
  90.       Set zrs_email = Nothing
  91.     End If
  92.  
  93.     Set zdb = Nothing
  94. End If
  95. '
  96. Exit Sub
  97. z_error_trap:
  98.  
  99. If Err.Number = 2501 Then Resume Next
  100. '
  101. MsgBox "Please report the following error to the DBA:" _
  102.  & vbCrLf & "Select VBNO" _
  103.  & vbCrLf & "Error Number: " & Err.Number _
  104.  & vbCrLf & "Error Detail: " & Err.Description
  105. Resume z_return_from_error
  106.  
  107. End Sub
  108.  
  109.  
  110.  


Any idea where the error is coming from?

Thanks,
10 Hours Ago #12

Post your reply

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

Similar topics

15 posts views Thread by news.microsoft.com | last post: by
2 posts views Thread by .Net Newbie | last post: by
4 posts views Thread by Michelle | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.