Access Report to Send to Multiple Recipients

[kjhyder]

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:

Public Sub SendSerialEmail()

 Dim db As DAO.Database
 Dim rs As DAO.Recordset
    
 Dim emailTo As String
 Dim emailSubject As String
 Dim emailText As String
    
 Dim outApp As Outlook.Application
 Dim outMail As Outlook.MailItem
 Dim outlookStarted As Boolean
    
 On Error Resume Next
 Set outApp = GetObject(, "Outlook.Application")
 On Error GoTo 0
 If outApp Is Nothing Then
     Set outApp = CreateObject("Outlook.Application")
     outlookStarted = True
 End If
 Set db = CurrentDb
 Set rs = db.OpenRecordset("SELECT Pay, Bill, Datepaid, Totalamount, Email FROM Query1")
 Do Until rs.EOF
     emailTo = rs.Fields("Email").Value
                    
     emailSubject = "YTD Transactions"
        
     emailText = emailText & _
                 "Below is your year to date transactions."
                    
                    
     Set outMail = outApp.CreateItem(olMailItem)
     outMail.To = emailTo
     outMail.Subject = emailSubject
     outMail.Body = emailText
     outMail.Send
                    
     rs.MoveNext
 Loop
    
 rs.Close
 Set rs = Nothing
 Set db = Nothing
    
   If outlookStarted Then
     outApp.Quit
 End If
    
 Set outMail = Nothing
 Set outApp = Nothing
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

[zmbd]

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_e mail_to_each_pe rson_with_all_q ueries() 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

[kjhyder]

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.

[zmbd]

🌟 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.OpenRepor t, (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.OpenRepor t method.

[kjhyder]

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_e mail_to_each_pe rson_with_all_q ueries() code?

Thank you

[ADezii]

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.

Dim objOut As Object
Dim objMail As Object
Dim olns As Object
Dim strSQL1 As String
Dim strSQL2 As String
Dim MyDB As DAO.Database
Dim rstUnique As DAO.Recordset
Dim rstData As DAO.Recordset
Dim curTotal As Currency
Dim strBuild As String
Dim strBody As String
Dim strCustomer As String
Dim strTO As String

Set objOut = CreateObject("Outlook.Application")
Set olns = objOut.GetNamespace("MAPI")

Set MyDB = CurrentDb

strSQL1 = "SELECT DISTINCT [Pay] FROM tblData ORDER BY [Pay]"       'Unique individuals

Set rstUnique = MyDB.OpenRecordset(strSQL1, dbOpenForwardOnly)

With rstUnique
  Do While Not .EOF
    Set rstData = MyDB.OpenRecordset("SELECT * FROM tblData WHERE [Pay] ='" & ![Pay] & "'", dbOpenSnapshot)
      strCustomer = rstData![Pay]
      strTO = rstData![EMail]
        Do While Not rstData.EOF
          Set objMail = objOut.CreateItem(0)
          curTotal = curTotal + rstData![TotalAmount]
          strBuild = strBuild & rstData![Bill] & ", "
            rstData.MoveNext
        Loop
          objMail.To = strTO
          objMail.Subject = "My Demo"
          objMail.Body = "Data for Customer " & strCustomer & ", " & Left(strBuild, Len(strBuild)-2) & _
                         "for a Total of " & Format(curTotal, "Currency")
            objMail.Display
          curTotal = 0    'RESET for next Customer
          strBuild = ""   'RESET Address
            .MoveNext
  Loop
End With

[imgnothumb]https://bytes.com/attachment.php? attachmentid=10 547&stc=1&d=165 3348225[/imgnothumb]

[zmbd]

@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.OpenRepor t 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?!

[ADezii]

Thanks for the invite, zmbd, NeoPa and I go way back as 'Party Animals'! (LOL)

[ADezii]

@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:

Public Sub SendEMails(blnAttachReport As Boolean)
  'Main Code segment here
End Sub

Call SendEMails(True)         'Attach Report to EMail
Call SendEMails(False)        'Put Data in Body of EMail

[kjhyder]

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.OpenRepor t method?

Thanks

[zmbd]

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.OpenRepor t 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.

[kjhyder]

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.

Option Compare Database
Option Explicit



Private Sub Command2_Click()
'

Dim zdb As DAO.Database
Dim zq As DAO.QueryDef
Dim zrs_email As DAO.Recordset
Dim zvbyesno As Long, znumber As Long
Dim zsql As String, zfname As String, zlname As String, zemail As String
Dim zmessage As String, zmessagelead As String, zsubject As String
Dim zWhere As String
'
On Error GoTo z_error_trap
'

zvbyesno = MsgBox("[Yes] You are about to send out a lot of" & vbCrLf & _
                  "      emails to people in the list." & vbCrLf & _
                  "      I understand:" & vbCrLf & _
                  "      This may result in my people flogging me" & vbCrLf & _
                  "      and may also result in the ISP tagging me" & vbCrLf & _
                  "      as a SPAM-BOT thus shuting me down!" & vbCrLf & _
                  vbCrLf & _
                  "[NO] Please do not do anything.", vbDefaultButton2 + vbYesNo, _
                  "Please Confirm the Intent To Flood the Internet...")
'
If zvbyesno = vbYes Then
  
    Set zdb = CurrentDb
    '
   
    zsql = "SELECT YTD.Pay," & _
           " YTD.email" & _
           " FROM YTD" & _
           " WHERE (((YTD.email) Is Not Null));"
    Set zrs_email = zdb.OpenRecordset(zsql, dbOpenDynaset)
    '
    
    If zrs_email.RecordCount > 0 Then
       
        zrs_email.MoveLast
        zrs_email.MoveFirst
        '
        
        Do
            
            zfname = zrs_email![Pay]
            zemail = zrs_email![email]
            zsubject = "YTD Transactions"
            '
            
            zmessage = "Dear " & zfname & ":" & vbCrLf & _
                "Attched is your YTD transactions"
            '
           
            zWhere = zrs_email![Pay]
            DoCmd.OpenReport ReportName:="RYTD", View:=acViewPreview, WhereCondition:=zWhere
            Reports("RYTD").Visible = False
            '
           
            DoCmd.SendObject objecttype:=acSendReport, objectname:="RYTD", Outputformat:=acFormatPDF, _
              To:=zemail, Subject:=zsubject, messagetext:=zmessage, EditMessage:=True
            '
         
            DoCmd.Close objecttype:=acReport, objectname:="RYTD", Save:=acSaveNo
            '
           
            zrs_email.MoveNext

        Loop Until zrs_email.EOF
    Else
        
        Err.Raise Number:=vbObjectError + 3297, Description:="No Records Returned With emails"
    End If
Else
    Exit Sub
End If
'

z_return_from_error:
If zdb Is Nothing Then
    Exit Sub
Else
   
    If Not zrs_email Is Nothing Then
      zrs_email.Close
      Set zrs_email = Nothing
    End If
   
    Set zdb = Nothing
End If
'
Exit Sub
z_error_trap:

If Err.Number = 2501 Then Resume Next
'
MsgBox "Please report the following error to the DBA:" _
 & vbCrLf & "Select VBNO" _
 & vbCrLf & "Error Number: " & Err.Number _
 & vbCrLf & "Error Detail: " & Err.Description
Resume z_return_from_error
  
End Sub

Any idea where the error is coming from?

Thanks,

[zmbd]

I suspect that you're using a string value where a numeric value is expected

Please alter the code as follows and then run

[iCODE]Comment out Line 17 by inserting an single quote " ' "[/iCODE]
Line 17            [iCODE]'[/iCODE]On Error GoTo z_error_trap
(...)
[iCODE]insert Line 34)>[/iCODE]             Stop
(...)
Line 35            zsql = "SELECT YTD.Pay," & _
Line 36                    " YTD.email" & _
Line 37                    " FROM YTD" & _
Line 38                    " WHERE*(((YTD.email) Is Not Null));"

[iCODE]insert (38A)>[/iCODE]             Debug.Print "zsql = " & zsql

Line 39           Set zrs_email = zdb.OpenRecordset(zsql, dbOpenDynaset)
(...)
Line 59            zWhere = zrs_email![Pay]

[iCODE]insert (59A)>[/iCODE]             Debug.Print "zWhere= " & zWhere 

Line 60            DoCmd.OpenReport ReportName:="RYTD", View:=acViewPreview, WhereCondition:=zWhere
Line 61            Reports("RYTD").Visible = False
(...)

The code execution will "break" at the new stop at Line 34 at this point I would like for you to do three things:
1) press <Ctrl><G> to open the "Immediate" window.
2) press function key <F8> slowly to step thru the remaining lines of code. Note the line at the point where the VBA error triggers when you press <F8> and report that information along with...
3) Copy and paste into this thread zsql = TheSQLThatResol ves AND any zWhere= SomeTextWillBeH ere text that prints to the "Immediate window"
Please note, if the text-strings that resolve in the immediate window have confidential information please PM/DM me instead of posting to the public thread - this is a one-time exception to the no direct message rule.

[kjhyder]

The following show in the "Immediate" window

Line 39 - zsql = SELECT YTD.Pay, YTD.email FROM YTD WHERE (((YTD.email) Is Not Null));

Line 60 - zWhere= Brent Kendrick

Thanks again

[zmbd]

I take it that the error occured when you pressed <F8> on: DoCmd.OpenRepor t (...)

So what we need to do is wrap the resulting text from zWhere = zrs_email![Pay] in double=quotes so that the resolved string looks like "Brent Kendrick". If you will look at the demo database I was using the numerical primary key to initiate the filters.

I'm going to borrow one of NeoPa's tricks (Thank You NeoPa!) using the Replace() function to insert the double-quotes. Cannot count the number of times this trick has fixed problems with SQL strings.

>Refering to the line numbers in the code in Post#12

(...)
[iCODE][B]Change (59)[/B][/iCODE]     [iCODE]zWhere = "%1" & zrs_email![Pay] & "%1"[/iCODE]
Insert (59A)      [iCODE]zWhere=Replace(zWhere,"%1",Chr(34))[/iCODE]  
insert (59B)      [iCODE]Debug.Print "zWhere= " & zWhere [/iCODE]
(...)

I do the Replace() in two steps because I like to be able to troubleshoot the strings at each step - that is to say, that you could take Line59 and Line59A and combine in to one command: zWhere = Replace( "%1" & zrs_email![Pay] & "%1", "%1",Chr(34 ))
as a personal preference, I don't like to do that because it's harder to detect when a string goes funky.

Here again, once you make the changes, execute the code, <F8> to step thru, report back any errors, line/code, and the resolved strings from the immediate window.
HOWEVER,
If this all goes to plan, and everything works, you can then take out the Stop and Debug.Print lines and return Line 17 back to normal by removing the single-quote so that the error trap catches any user aborts on emails.

Please post back to let us know how things are working - or not working.👍👊