VBAEXCELNew
New Member
- Joined
- Apr 3, 2023
- Messages
- 38
- Office Version
- 365
- 2021
- Platform
- Windows
VBA Code:
Sub SendEmails()
If ActiveSheet.Name <> "Master Table" Then Exit Sub
Dim Applications As Object
Set Applications = CreateObject("Outlook.Application")
Dim Applications_Item As Object
Set Applications_Item = Applications.CreateItem(0)
Dim Rlist As Range
Set Rlist = Range("A2", Range("a2").End(xlDown))
Dim R As Range
For Each R In Rlist
Set Applications_Item = Applications.CreateItem(0)
HtmlContent = "<table>"
HtmlContent = "<br><table border=2px><tbody>"
HtmlContent = HtmlContent & "<tr>"
HtmlContent = HtmlContent & "</tr>"
'strQuotation = Trim(ThisWorkbook.Sheets("Master Table").Range("B" & R.Offset(0, 1)).Text)
HtmlContent = HtmlContent & "<tr>Quotation / DC No.:<td>" & R.Offset(0, 1) & "</td>"
HtmlContent = HtmlContent & "<tr>Description:<td>" & R.Offset(0, 2) & "</td>"
HtmlContent = HtmlContent & "<tr>Status:<td>" & R.Offset(0, 0) & "<br>" & R.Offset(0, 5) & "<br>" & R.Offset(0, 6) & "<br>" & R.Offset(0, 7) & "</td>"
HtmlContent = HtmlContent & "<tr>Remarks:<td>" & R.Offset(0, 8) & "</td>"
HtmlContent = HtmlContent & "</table>"
With Applications_Item
.To = R.Offset(0, 9)
.CC = R.Offset(0, 10)
.Subject = R.Offset(0, 1) & " Requires Your Attention - " & R.Offset(0, 0)
'.Body = "Dear Buyer/AM, " & vbNewLine & vbNewLine & "We would like to inform that the following ITQ / DC requires your attention." & vbNewLine & vbNewLine
.HTMLBody = "Dear Buyer/AM, <br> <br>" & "We would like to inform that the following ITQ / DC requires your attention.<br>" & HtmlContent & "<br> <br> Should you require further clarification, please contact your respective Finance Partners: <br> MOE_FPD_FP_North@moe.gov.sg <br> MOE_FPD_FP_South@moe.gov.sg <br> MOE_FPD_FP_East@moe.gov.sg <br> MOE_FPD_FP_West@moe.gov.sg <br> Thank you."
.Display
' Send out email out
'.Send
End With
Next R
Set Applications = Nothing
Set Applications_Item = Nothing
'Only for setup reference if u select the outlook library else multiple computer wouldnt work without setup
'Dim EApp As Outlook.Application
'Set EApp = New Outlook.Application
'
'Dim EItem As Outlook.MailItem
'Set EItem = EApp.CreateItem(olMailItem)
End Sub
if i have multiple list in my excel list it wouldnt display this issue
Can anyone help with this ?