Are you sending email to supplier or customer? You use both nouns in your OP.
Need two loops. One approach can use two recordsets - one recordset for the SupplierRef and email and another recordset for the payments info. The outer loop reads the suppliers recordset and the inner loop reads the payments recordset. A one recordset approach would require using a variable to hold the SupplierRef. Manage the looping by comparing variable to the value in record as code loops through the recordset. When the SupplierRef changes, exit inner loop, send email, reset the variable, repeat outer loop.
If you want to also attach the report as a PDF, options:
1. open report, use SendObject command to send email, close report
2. open report, use OutputTo command to save report to external PDF file, attach to Outlook object, close report
Consider two recordset approach:
Code:
Set rsSupp = db.OpenRecordset("SELECT DISTINCT SupplierRef, Email1 FROM tbl_PayNGNArchieve WHERE PaymentRef='" & Forms![SendNGN]!txt_PayRef & "'";")
Do Until rsSupp.EOF
On Error Resume Next 'Keep going if there is an error
Set objMail = olApp.CreateItem(olMailItem)
With objMail
.To = rsSupp!Email
.Subject = "Payment Advice - " & rs!SupplierRef
.Importance = olImportanceHigh
'Set body format to HTML
.BodyFormat = olFormatHTML
Set rs = db.OpenRecordset("SELECT * FROM tbl_PayNGNArchieve WHERE SupplierRef = " & rsSupp!SupplierRef & " AND PaymentRef='" & Forms![SendNGN]!txt_PayRef & "'")
Do Until rs.EOF
.HTMLBody = .HTMLBody & "<tr>" _
& "<td>" & rs!PaymentRef & "</td>" _
& "<td>" & rs!InvoiceNo & "</td>" _
& "<td>" & rs!InvoiceDate & "</td>" _
& "<td>" & Format(Trim(rs!Gross), "#,##0.00;(#,##0.00)") & "</td>" _
& "<td>" & Format(Trim(rs!VAT), "#,##0.00;(#,##0.00)") & "</td>" _
& "<td>" & Format(Trim(rs!WHT), "#,##0.00;(#,##0.00)") & "</td>" _
& "<td>" & Format(Trim(rs!LCD), "#,##0.00;(#,##0.00)") & "</td>" _
& "<td>" & Format(Trim(rs!Payment), "#,##0.00;(#,##0.00)") & "</td>" _
& "<td>" & rs!Curr & "</td>" _
& "</tr>"
rs.MoveNext
Loop
.HTMLBody = "<font face=Calibri><h3>Dear " & rsSupp!SupplierRef & ",</h3> " _
& "Please be informed of the payment of " & rs!Curr & Format(Trim(rs!Payment), "#,##0.00;(#,##0.00)") & " made into your company’s bank account.<b> " _
& "<p><b>Find below, breakdown of invoice(s) for which payment was made and please acknowledge receipt of funds upon confirmation.</b><br />" _
& "<html><head><style>" _
& "table,th,td{border: 1px solid black; border-collapse: collapse;}table,th,td{padding: 5px;}th{text-align: left;}</style></head>" _
& "<body><table>" _
& "<tr><th>Payment Reference</th>" _
& "<th>Invoice Number</th>" _
& "<th>Invoice Date</th>" _
& "<th>Gross Amountz</th>" _
& "<th>VAT</th>" _
& "<th>WHT</th>" _
& "<th>LCD</th>" _
& "<th>Net Amount</th>" _
& "<th>Currency Code</th></tr>" _
& .HTMLBody _
& "<th>Total</th>" _
& "<td>" & Format(Trim(Total), "#,##0.00;(#,##0.00)") & "</td>" _
& "<td>" & Format(Trim(rs!Payment), "#,##0.00;(#,##0.00)") & " </td>" _
& "</table>" _
& "<br><br>Regards " _
& "<br>Olajumoke Nwabuisi." _
& "</body></html>"
rs.Close
DoCmd.OpenReport "reportname", acViewPreview, , "SuppID = " & rsSupp!SuppID & " AND PaymentRef='" & Forms![SendNGN]!txt_PayRef & "'"
DoCmd.OutputTo acOutputReport, "reportname", acFormatPDF, "path\payments.pdf"
DoCmd.Close acReport, "reportname"
.Attachment.Add ("path\payments.pdf")
.Display
End With
rsSupp.MoveNext
Loop
rsSupp.Close
Sending Multiple records via Outlook using HTML in Access
Windows 7 64bit 
Access 2013 64bit
Reply With Quote
