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
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") msg = Replace(msg, "NAME", Cell) 'Replaces the name in each email more a more personalized touch. 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. .Subject = "PlsFixThx" .HTMLBody = msg & OutMail.HTMLBody .Save 'Change this to .Send for sending the messing 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.