Sending Multiple Records Via Outlook Using Html In Access, Access 2013 (1 Viewer)

O

Oladapo

New member
Local time
Today, 07:47
Joined
Aug 28, 2017
Messages
3
Hello All,


Please, i need an urgent assistance.

I was trying to send some records via Outlook from Access to different customers, showing records like SupplierRef,Email,Amount,VAT, and a unique Payment Reference generated for each batch of payment. Let's say a batch with Payment Reference "FHN000001" contains 15 records for 3 suppliers 1,2 and 3 and each supplier has 5 records each. I want 5 records to be sent to each supplier in outlook mail, showing in a tabular form. As it is, each customer gets 5 different emails showing a line of transaction for each email. I want 3 emails to be sent, showing 5 lines of transaction per customer. I am sharing my codes below and screen shots.

1. Query to select customer "qryEmail_NGN"

[SELECT tbl_PayNGNArchieve.SupplierRef, tbl_PayNGNArchieve.InvoiceNo, tbl_PayNGNArchieve.InvoiceDate, tbl_PayNGNArchieve.Gross, tbl_PayNGNArchieve.VAT, tbl_PayNGNArchieve.WHT, tbl_PayNGNArchieve.LCD, tbl_PayNGNArchieve.Payment, tbl_PayNGNArchieve.Curr, tbl_PayNGNArchieve.DateReceived, tbl_PayNGNArchieve.Pay, tbl_PayNGNArchieve.Status, tbl_PayNGNArchieve.PaymentDate, tbl_PayNGNArchieve.PaymentRef FROM tbl_PayNGNArchieve GROUP BY tbl_PayNGNArchieve.SupplierRef, tbl_PayNGNArchieve.InvoiceNo, tbl_PayNGNArchieve.InvoiceDate, tbl_PayNGNArchieve.Gross, tbl_PayNGNArchieve.VAT, tbl_PayNGNArchieve.WHT, tbl_PayNGNArchieve.LCD, tbl_PayNGNArchieve.Payment, tbl_PayNGNArchieve.Curr, tbl_PayNGNArchieve.DateReceived, tbl_PayNGNArchieve.Pay, tbl_PayNGNArchieve.Status, tbl_PayNGNArchieve.PaymentDate, tbl_PayNGNArchieve.PaymentRef HAVING (((tbl_PayNGNArchieve.SupplierRef)=[forms]![PayNGN]![SupplierRef]) AND ((tbl_PayNGNArchieve.PaymentRef)=[forms]![SendNGN]![txt_PayRef])) ORDER BY tbl_PayNGNArchieve.SupplierRef;[/code]

2. Code to send Email "Public Function SendEmail2

[Function SendEmail2() 'Horizontal with border Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(" SELECT * FROM tbl_PayNGNArchieve WHERE PaymentRef='" & Forms![SendNGN]!txt_PayRef & "' ")
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
Dim Msg As String
Dim Email As String
Msg = DCount("SupplierRef", "tbl_PayNGNArchieve", "[PaymentRef]='" & Forms![SendNGN]!txt_PayRef & "' AND [Pay]='Yes' ")
Email = DLookup("Email1", "tbl_PayNGNArchieve", "[PaymentRef]='" & Forms![SendNGN]!txt_PayRef & "' AND [SupplierRef]= '" & Form_PayNGN.txt_SupplierName & "'AND [Pay]='Yes' ")


Do Until rs.EOF

On Error Resume Next 'Keep going if there is an error
Set objMail = olApp.CreateItem(olMailItem)
With objMail

.To = Email
.subject = "Payment Advice -" & " " & rs!SupplierRef & ""
.Importance = olImportanceHigh


'Set body format to HTML
.bodyFormat = olFormatHTML
.bodyFormat = olFormatHTML


.HTMLBody = "<font face=Calibri><h3>Dear " & rs!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 />" _


.HTMLBody = .HTMLBody & " <html>"
.HTMLBody = .HTMLBody & " <head>"
.HTMLBody = .HTMLBody & " <style>"
.HTMLBody = .HTMLBody & " table,th, td {"
.HTMLBody = .HTMLBody & " border: 1px solid black;"
.HTMLBody = .HTMLBody & " border-collapse: collapse;"
.HTMLBody = .HTMLBody & "}"
.HTMLBody = .HTMLBody & " table,th, td {"
.HTMLBody = .HTMLBody & "padding: 5px;"
.HTMLBody = .HTMLBody & "}"
.HTMLBody = .HTMLBody & "th {"
.HTMLBody = .HTMLBody & "text-align: left;"
.HTMLBody = .HTMLBody & "}"
.HTMLBody = .HTMLBody & " </style>"
.HTMLBody = .HTMLBody & " </head>"
.HTMLBody = .HTMLBody & " <body>"
.HTMLBody = .HTMLBody & "<table>"
.HTMLBody = .HTMLBody & " <tr>"
.HTMLBody = .HTMLBody & " <th>Payment Reference</th>"
.HTMLBody = .HTMLBody & " <th>Invoice Number</th>"
.HTMLBody = .HTMLBody & " <th>Invoice Date</th>"
.HTMLBody = .HTMLBody & " <th>Gross Amountz</th>"
.HTMLBody = .HTMLBody & " <th>VAT</th>"
.HTMLBody = .HTMLBody & " <th>WHT</th>"
.HTMLBody = .HTMLBody & " <th>LCD</th>"
.HTMLBody = .HTMLBody & " <th>Net Amount</th>"
.HTMLBody = .HTMLBody & " <th>Currency Code</th>"
.HTMLBody = .HTMLBody & " </tr>"
.HTMLBody = .HTMLBody & " <tr>"
.HTMLBody = .HTMLBody & "<td>" & rs!PaymentRef & "</td>"
.HTMLBody = .HTMLBody & "<td>" & rs!InvoiceNo & "</td>"
.HTMLBody = .HTMLBody & "<td>" & rs!InvoiceDate & "</td>"
.HTMLBody = .HTMLBody & "<td>" & Format(Trim(rs!Gross), "#,##0.00;(#,##0.00)") & " </td>"
.HTMLBody = .HTMLBody & "<td>" & Format(Trim(rs!VAT), "#,##0.00;(#,##0.00)") & "</td>"
.HTMLBody = .HTMLBody & "<td>" & Format(Trim(rs!WHT), "#,##0.00;(#,##0.00)") & "</td>"
.HTMLBody = .HTMLBody & "<td>" & Format(Trim(rs!LCD), "#,##0.00;(#,##0.00)") & "</td>"
.HTMLBody = .HTMLBody & "<td>" & Format(Trim(rs!Payment), "#,##0.00;(#,##0.00)") & "</td>"
.HTMLBody = .HTMLBody & "<td>" & rs!Curr & "</td>"
.HTMLBody = .HTMLBody & " </tr>"
'.HTMLBody = .HTMLBody & " <th>Total</th>"
'.HTMLBody = .HTMLBody & "<td>" & Format(Trim(Total), "#,##0.00;(#,##0.00)") & "</td>"
'.HTMLBody = .HTMLBody & "<td>" & Format(Trim(rs!Payment), "#,##0.00;(#,##0.00)") & " </td>"
.HTMLBody = .HTMLBody & " </table>"
.HTMLBody = .HTMLBody & "<br><br>Regards "
.HTMLBody = .HTMLBody & "<br>Oladapo."
.HTMLBody = .HTMLBody & "</body></html>"

.display
'.Send

rs.MoveNext
End With
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox (Msg & " " & "Mails successfully sent"), vbInformation, Title

End Function [/code]

I also want to be able to attach a pdf. file for each customer showing payments made.

Will appreciate you kind/urgent assistance.

Thank you all.
 

Attachments

  • Access Issue.zip
    297 KB · Views: 317
O

Oladapo

New member
Local time
Today, 07:47
Joined
Aug 28, 2017
Messages
3
Hello All,

I just uploaded a sample database (Access version 2013, 64 bit) for your kind assistance. follow the steps to replicate please.

1. Open access file
2. Browse to upload NGN (excel file attached)
3. Click on “Upload Payment”………do you want to continue, “Yes”
4. Click on “Process”
5. Check “Select all for payment”
6. Click on “Send Notification”…system generates payment Reference
7. Click on ok
8. Mail is displayed.

See attached “Access Issue” document…

Thank you for your kindness
 

Attachments

  • TNotify.zip
    978.3 KB · Views: 537
Cronk

Cronk

Registered User.
Local time
Tomorrow, 00:47
Joined
Jul 4, 2013
Messages
2,772
It looks like you need a number of embedded loops with the outer loop being the payment ref, so you loop through each payment. Then open up another recordset with all the suppliers and loop through each supplier. Inside the second loop, have a third one for each occurrence you want to send to that supplier.

Something like
Code: 
do for each payment ref
  do for each supplier associated with that payment
    do for each email you want to send to that supplier

    loop
  loop
loop
 
You must log in or register to reply here.

Similar threads

M
Solved VBA access to outlook email with table: aligning table cells
Replies
5
Views
1,897
miacino
M
L
Solved Help on blank Email body
2
Replies
27
Views
1,842
bastanu
bastanu
D
  • Question
Send an outlook email to each record in the table in a table format
Replies
4
Views
857
Donnie1ben
D
T
HTML EMAIL
Replies
6
Views
769
cheekybuddha
cheekybuddha
A
combine multiple email addresses into .bcc from recordset to send outlook email from access vba
Replies
8
Views
803
adhoustonj
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom