Excel VBA: send simple Outlook emails with subject and content from existing email list in excel

1. Send simple emails from existing list.

Prepare simple template list like below then run the macro:

Open the VBA Editor (Alt+F11), insert a New Module, then paste the code below. Remember to adjust the parameters to fit your data structure:

* Note:remember to tick on Microsoft Outlook object library before running this.

Option Explicit

Sub CreateEmails()

Dim OlApp As Object

Dim OlMail As Object

Dim ws As Worksheet

Dim fso As Object

Dim ts As Object

Dim lastRow As Long

Dim i As Long

Set OlApp = CreateObject("Outlook.Application")

Set ws = ActiveSheet

Set fso = CreateObject("Scripting.FileSystemObject")

' Find the last row with data in column A

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each row from 2 to lastRow

For i = 2 To lastRow

If ws.Cells(i, 1).Value <> "" Then

Set OlMail = OlApp.CreateItemFromTemplate("C:\Users\PKUser\AppData\Roaming\Microsoft\Templates\signature.oft")

'=>red hightlight is the template contains signature, if you don't know how to make a template and don't want to including signature, use this command instaed.

Set OlMail = OlApp.CreateItem(olMailItem)

' Add To recipient

OlMail.Recipients.Add ws.Cells(i, 1).Value

' Set the subject from column B

OlMail.Subject = ws.Cells(i, 2).Value

' Set the email body from column C a

OlMail.HTMLBody = ws.Cells(i, 3).Value

OlMail.Display ' <--OlMail.Display is used for testing, to send, use OlMail.Send

End If

Next i

End Sub