Updated: May 24, 2022
Please see the following code for creating several emails with VBA.
Step 1 before inserting this code into you VBE (Alt + F11) is to enable the library reference for Outlook so that you have access to Outlook classes for the below code.
Press (Alt + F11) keys, Go to Tools, References, and make sure "Microsoft Outlook 16.0 Object Library". if you have an earlier or later version than 16.0 that should be fine as well.
How my worksheet looks like
Code
Sub SendThoseEmails()
Application.ScreenUpdating = False 'Turns off screenupdating for faster script.
Dim msg As String 'Email message.
'Imports the Outlook class and library references.
Dim OutApp As Outlook.Application
Set OutApp = New Outlook.Application
For Each Cell In Range("A2:A10") 'Iterates up to cell A10.
If Cell <> "" Then
'Creating the Email here.
Dim OutMail As Outlook.MailItem
Set OutMail = OutApp.CreateItem(olMailItem)
'Email message
msg = Email_Sheet.Range("I1")
'Replaces the name in each email more a more
'personalized touch.
msg = Replace(msg, "NAME", Cell)
msg = Replace(msg, "(NEW)", "<br/>")
'Converting to HTML code as that is what
'we draft the message in.
msg = Replace(msg, "(NEWL)", "<br/><br/>")
'Converting to HTML code as that is
'what we draft the message in.
With OutMail
.Display 'This displays the message on screen.
.To = Cell.Offset(0, 1) 'Picks up the email address.
.CC = "" 'add .CC to CC in the email.
.BCC = "" 'add .BCC to BCC in the email.
.Subject = "PlsFixThx"
.HTMLBody = msg & OutMail.HTMLBody
.Save 'Change this to .Send for sending the
'message instead of displaying it.
End With
End If
Next Cell
Application.ScreenUpdating = True 'Turns on screenupdating
now that we are done.
MsgBox ("Done")
End Sub
Download Excel Workbook here.
Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.
Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba
Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python
If you have found this article or website helpful. Please show your support by visiting the shop below.