Hi all
I'm new to VBA and been trying to figure out how to send multiple emails to different recipients individually, with customised messages, with tables included.
I've done this thus far and been stuck since as i can't seem to modify the codes that I've searched online. Hope to seek some advice here. thanks!
the outcome I've been trying towards is to have each individual email with customised msg and table. Meaning, even though my spreadsheet as 4 to 5 rows of records, but each email i only require 1 record of the respective recipients.
.
.
.
Sub TextBox1_Click()
Dim OApp As Object, OMail As Object, Signature As String, rng As range
Dim cnt As Integer
Dim i As Integer
MsgBox "Are you sure you wanna mass send?"
Set OApp = CreateObject("Outlook.Application")
Set ws = Worksheets("Sheet1")
cnt = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 2 To cnt Step 1 'Start from Row 2, increase by 1 till end of row
Set OMail = OApp.CreateItem(0)
On Error Resume Next
With OMail
.To = Worksheets("Sheet1").range("H" & i)
.Subject = "Mass HSP email"
htmlbodytext = "Dear " & range("A" & i).Value & range("B" & i).Value & "," & vbNewLine & vbNewLine & _
"<ol><li> Pls note that...</li>" & vbNewLine & vbNewLine & _
"<li> Kindly proceed to ..</li>" & vbNewLine
.HTMLBody = htmlbodytext
.display
End With
On Error GoTo 0
Application.Wait (Now + TimeValue("0:00:02"))
'Application.SendKeys "^~", True
Set OMail = Nothing
Next i
Set OApp = Nothing
End Sub
Bookmarks