How to Send Email Using Excel VBA

Download the sheet here!

—- CODE —–


Option Explicit

Sub SendEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim i As Integer

Application.ScreenUpdating = False

Set OutApp = CreateObject(“Outlook.Application”)

On Error GoTo cleanup

For Each cell In Columns(“A”).Cells.SpecialCells(xlCellTypeConstants)
i = cell.Row

If Not i = 1 Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = “Project A: ” & Sheets(“Sheet1”).Range(“C” & i).Value
.HTMLBody = “<p> Hello ” & Sheets(“Sheet1”).Range(“B” & i).Value & “</p>” & “<p><strong><u>This is just a test </u></strong></p>”
End With
On Error GoTo 0
Set OutMail = Nothing
End If

Next cell


Set OutApp = Nothing
Application.ScreenUpdating = True

End Sub



Please follow and like us:

Author: admin2344

Leave a Reply