How to send outlook emails from your excel sheets with VBA? 

Please see the following code for creating several emails with VBA.

vba send emails automatically

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

send emails from worksheet and attach files from folder


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.

Download ZIP • 24KB

0 views0 comments