Using VBA to Send Emails from Excel through Outlook

The VBA code below opens the Microsoft Outlook application, if not already open, and creates a new email for each row that requires a reply. The email address, subject, and body come from cells in the worksheet depending on the logic in the program. All of these parameters can be adjusted as you see fit.

Note, you will need to ensure the Microsoft Outlook Object Library is activated. This can be done by opening the visual basic editor, clicking the Tools drop down menu, selecting the References option, clicking the check box associated with the Outlook Object Library, and then clicking the OK button.

Microsoft Excel - Andon Excel - Sending emails through Microsoft Outlook from a spreadsheet list.

Sub Email()

Dim SH1 As Worksheet

Set SH1 = ThisWorkbook.Worksheets("Sheet1")

Dim LR_SH1 As Integer

Dim NOLK As Object

Dim NEML As Object

Dim Receiver As String

Dim Address As String

Dim Subject As String

Dim Email As String

Set NOLK = GetObject(, "Outlook.Application")

LR_SH1 = SH1.Cells(Rows.Count, "A").End(xlUp).Row

For A = 6 To LR_SH1

If SH1.Cells(A, "E").Value = "Yes" Then

Receiver = SH1.Cells(A, "A").Value

Address = SH1.Cells(A, "C").Value

Subject = "Inquiry Reply"

If SH1.Cells(A, "D").Value = "Reply 1" Then

Email = "Hello " & Receiver & "," & vbNewLine & vbNewLine & SH1.Cells(2, "B").Value & vbNewLine & vbNewLine & "Sincerely," & vbNewLine & "SheetsFreaks Team"

ElseIf SH1.Cells(A, "D").Value = "Reply 2" Then

Email = "Hello " & Receiver & "," & vbNewLine & vbNewLine & SH1.Cells(3, "B").Value & vbNewLine & vbNewLine & "Sincerely," & vbNewLine & "SheetsFreaks Team"

End If

Set NEML = NOLK.CreateItem(0)

NEML.To = Address

NEML.Subject = Subject

NEML.Body = Email

NEML.Send

SH1.Cells(A, "E").Value = "No"

SH1.Cells(A, "F").Value = Now()

SH1.Cells(A, "F").Value = SH1.Cells(A, "F").Value

End If

Next A

End Sub