EFF_Santad

Option Explicit

Sub ProcessEmails()
Dim lst As Long, i As Long
Dim email As String, dealer As String
Dim codeno As String, tel As String

lst = Range("A1").CurrentRegion.Rows.Count

For i = 2 To lst
    email = Range("A" & i).Value
    dealer = Range("B" & i).Value
    codeno = Range("C" & i).Value
    tel = Range("D" & i).Value
    'send email
    SendEmail email, dealer, codeno, tel
Next i
End Sub

Sub SendEmail(email As String, dealer As String, codeno As String, tel As String)
Dim oApp As New Outlook.Application
Dim oMail As MailItem
Dim msg As String

Set oMail = oApp.CreateItem(olMailItem)

msg = msg & "<p>Dealer Name: <span style='color:red'>" & dealer & "</span><br/>"
msg = msg & "Code: <span style='color:red'>" & codeno & "</span> Tel: <span style='color:red'>" & tel & "</span><br/>"
msg = msg & "----------------------------------------------</p>"
msg = msg & "<p>Dear sir,</p>"
msg = msg & "<p>Please prepare data for this month.</p>"
msg = msg & "<p>Regards</p>"
msg = msg & "<p>Your Boss</p>"


With oMail
    .To = email
    .Subject = "Recheck Data for " & dealer
    .HTMLBody = msg
    .Display 'or .Send
End With
End Sub