Assistance with emailing list of open invoices to each Vendor in Message

[supportservice]

Hello,
I haven't come across posts that could help me on this issue.

I have a table with open invoices for vendors.
Each vendor needs to be emailed the list of invoices with the total to charge in the body of the email message.

Subject: "Please charge credit card on file the following invoice(s):"
Message:

Dear: [VendorName],

Please charge credit card on file for these invoice(s)
InvoiceNo.   InvoiceDueDate   InvoiceAmount
XXXXXXX      xx/xx/xxxx       $xxx.xx
XXXXXXX      xx/xx/xxxx       $xxx.xx
XXXXXXX      xx/xx/xxxx       $xxx.xx
XXXXXXX      xx/xx/xxxx       $xxx.xx

The TOTAL AMOUNT TO CHARGE: $xxx.xx

(<--- this would be the total of all the open invoices for the vendor).

I came across this code and edited with my

Private Sub EmailReport_Click()
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim qdf As DAO.QueryDef
   Dim baseSQL As String
   Dim rptSQL As String
   Set dbs = DBEngine(0)(0)
   Set rst = dbs.OpenRecordset("SELECT VendorNo, EmailAddress FROM Emailing-OpenInvoices")
   Set qdf = dbs.QueryDefs("Emailing-OpenInvoices")
   baseSQL = "SELECT [Emailing-OpenInvoices].VendorNo, [Emailing-OpenInvoices].VendorName, [Emailing-OpenInvoices].InvoiceNo, [Emailing-OpenInvoices].InvoiceDueDate, [Emailing-OpenInvoices].InvoiceAmt, [Emailing-OpenInvoices].DiscountAmt, [Emailing-OpenInvoices].NetInvoice, [Emailing-OpenInvoices].EmailAddress FROM [Emailing-OpenInvoices]"
   With rst
      Do Until .EOF
         rptSQL = baseSQL & " WHERE VendorNo = " & !VendorNo
         qdf.SQL = rptSQL
         DoCmd.SendObject acSendReport, "rptEmailing-OpenInvoices", acFormatRTF, !Email, , , "Subject: Please charge credit card on file the following invoice(s):", "Your message", False
         .MoveNext
      Loop
      .Close
   End With
   qdf.SQL = baseSQL
   Set qdf = Nothing
   Set rst = Nothing
   Set dbs = Nothing
   
End Sub

The problem is how will it add all the open invoices (if more then one) for each Vendor?
Please see the Report. It is Grouped by Vendor and the TOTAL is at the Vendor Group level.

NOTE: I will also need to set an exclude if the Vendor Group total = $0.

BTW: Initially I had a macro that exported a file (data source) and then used Word's Mail Merge. However it creates an email for each invoice.
I need all of the Vendor's invoices as one email with the TOTAL of their invoices.

Thank you in advance. I was not able to get any help from the original post.

 

[supportservice]

I found this as a possible option.
Since it's easy enough to export the data or even use the Access table as the data source for a Mail Merge in Word.
Issue I was coming across is listing all the invoices per Vendor.

It's easy to send one invoice per Vendor per page but that will not work.

Anyway, I can't seem to get it to do what it says it's supposed to do.
The 2nd part in particular.

MS Support article

I tried and it is not listing all related invoices to the Vendor. It's still one invoice per page.

If there isn't VBA in Access to do perhaps someone can help with the code in Word to make it work?
Though there is still the problem with Totaling all of the invoice per Vendor if done in Word as a Mail Merge.

What do you think?