How to send an email in Outlook with VBA?

by

Applicable to Microsoft Outlook 365 / 2019 / 2016

Here’s a note we got from a reader:

Thanks for your very useful Outlook coding help. I am wondering if you can share some code to help automate sending an email in Outlook 365? I know that there are several ways to send recurring message but i am specifically interested in using VBA for my this task.

Thanks for the question. Automatically sending emails seem to be among the most useful and popular tasks you can automate using VBA. Read on for more details and a simple script to get you started here.

Sending Outlook emails automatically using VBA

To send out email messages in Outlook using Visual Basic for Applications, proceed as following:

  1. First off, use the VBA Outlook object model to create a new email item.
  2. Then go ahead and set the message importance, recipients (separated by semi colons) and body text style and content.
  3. Next, display the message in your Microsoft Outlook application.
  4. Last, go ahead and send it your email recipients.

Here’s the simple VBA code to automatically create and send simple emails. Note that you are to easily improve this script to include file attachments, tasks etc’, but let’s start with a very basic script.

Sub SendMail()
Dim MyEmail As MailItem
Set MyEmail = Application.CreateItem(olMailItem)

With MyEmail
.To = "<type your recipient email address/ess here>"
.Importance = olImportanceHigh
.Subject = "<type the subject of your email here>"
.Body = "<type the email message text here>"
.BodyFormat = olFormatHTML
.Display
End With

'I purposely commented the next line, uncomment it by removing the "'" sign to send your email.
' MyEmail.Send
End Sub

Create a Outlook VBA macro shortcut

You might want to look into associating the VBA code you just wrote with a command button in Outlook. This will allow you to easily launch it from a shortcut placed in the Outlook Ribbon.

Here’s how to do that:

  1. First, go ahead and open Microsoft Outlook.
  2. Hit File then select Options
  3. Now go ahead and select Customize Ribbon.
  4. In the Customize Ribbon section, you’ll notice the Choose Commands from list box.
  5. Select Macros
  6. Then Select your newly created VBA code, in our case the name should be SendMail.
  7. Then hit Add and then OK. Optionally you might want to assign an icon to your Macro.
  8. Your Macro will be available in the Quick Access toolbar in the upper left side of your Outlook application.

Send multiple emails using Outlook Macro

Next use case is a bit more complicated – consists of sending several emails in a batch. The email addresses are kept in a text file. You can modify the script below to set the location of your text file in your computer, Onedrive or server.

Sub SendMultipleMails()
    Dim fso As Object
    Dim ts As Object
    Dim line As String
    Dim emailAddresses As Collection
    Dim emailAddress As Variant
    Dim MyEmail As MailItem
    
    ' Initialize FileSystemObject and Collection
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set emailAddresses = New Collection
    
    ' Open the text file and read each line (email address), replace with the specific location in your computer
    Set ts = fso.OpenTextFile("C:\addresses.txt", 1)
    
' Add each email address to your file    
Do While Not ts.AtEndOfStream
        line = ts.ReadLine
        emailAddresses.Add line
    Loop
    
    ts.Close
    
    ' Now send email to each address in your file

    For Each emailAddress In emailAddresses
        Set MyEmail = Application.CreateItem(olMailItem)
        
        With MyEmail
            .To = emailAddress
            .Importance = olImportanceHigh
            .Subject = "Your Email Subject Here"
            .Body = "Your email message text here."
            .BodyFormat = olFormatHTML
            .Display
            ' Uncomment the next line to send your emails
            ' .Send
        End With
    Next emailAddress
End Sub

Send emails with attachments with Outlook VBA

Next case consists of allowing your user to attach ore or multiple files to their outgoing Outlook message. Note that you can set the AllowMultiSelect property of your FileDialog object to False to allow single file selection.

Here is the code to automatically launch emails with one or more file attachments using a VBA macro:

Sub SendMailWithAttachments()
    Dim MyEmail As MailItem
    Dim fd As FileDialog
    Dim selectedItems As FileDialogSelectedItems
    Dim i As Integer
    
    ' Create a new email item
    Set MyEmail = Application.CreateItem(olMailItem)
    
    ' Initialize the FileDialog object
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    ' Set dialog title
    fd.Title = "Select File(s) to Attach"
    
    ' Allow multiple file selection
    fd.AllowMultiSelect = True
    
    ' Show the file selection dialog
    If fd.Show = -1 Then
        ' Get the selected items
        Set selectedItems = fd.SelectedItems
        
        ' Attach each selected file to the email
        For i = 1 To selectedItems.Count
            MyEmail.Attachments.Add selectedItems(i)
        Next i
    End If
    
    'Here you define your email parameters
    With MyEmail
        .To = "<type your recipient email address/ess here>"
        .Subject = "<type the subject of your email here>"
        .Body = "<type the email message text here>"
        .BodyFormat = olFormatHTML
        .Display
    End With

    ' Uncomment the next line to send your email
    ' MyEmail.Send
End Sub

Possible issues with VBA macro settings

Your Outlook macro settings might need to be tuned in order to run macros. If so, here’s how to do that.