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