Send an email using VBA and Outlook

This article shows a VBA macro to send an email using VBA and Outlook in its simplest form. This simple solution is then extended in other articles.

Create the Outlook MailItem

To begin, in any Office application (e.g. Excel,) open the VB Editor and in a module and create an empty Sub 'EmailDemo' for the macro.

Now we create the Outlook MailItem using the Code VBA add-in to connect to Outlook and create the mail: down the Code VBA menu, select Outlook followed by Create...

menu Create Outlook MailItem

This opens a Fragment Builder dialog to specify the use of the MailItem object:

dialog to specify the use of the MailItem object
  • Enter text for subject line;
  • Agree to have a new String variables strBody and strTo created to set the .To and .Body properties;
  • Accept the default value .Display for the MailItem's Action setting. You will probably change this to change this to .Send once things are woking to your satisfaction.

After OK,

  1. a reference is set to Outlook (without this the code won't run), and
  2. the following code block is inserted:

Dim mimEmail As Outlook.MailItem
Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
Dim strBody As String: strBody =
Set mimEmail = appOutlook.CreateItem(olMailItem)
With mimEmail
    .To = strTo
    .Subject = "Taskforce meeting"
    .Body = strBody 'use .HTMLBody in case of HTML!'
    .Display
End With

Create variables strFirstName and strLastName to later supply the values for email salutation, for the time being we give them fictitious values for testing.

Note: use the Variable Builder to save time... ... in typing, in placing the variable at the top of the procedure, and copying its name for further use. For example: Create Variable using Builder String FirstName
  1. Paste to insert the name strFirstName variable from the clipboard;
  2. Complete the line: strLastName = "Thomas";
  3. Same for strLastName.
The line with variable strBody still needs completion. For this the String Builder is a useful tool... start string builder using intellisense

The line with variable strBody still needs completion. Put the cursor after the '=' and press Shift-Space to use Code VBA IntelliSense. This opens a context menu with many options to build your string from. Here, we chose the String Builder to make the combining text with variables easy.

string builder dialog

As you see, the string builder dialog is filled with the email's invitation text. Using the variables we have a customized email with for each recipient a separate salutation. We added the first and last name variables from the popup selection box and the Add button. Pressing OK inserts the code.


strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
    "Just a reminder that our meeting to discuss the environment is later this week. See you Thursday!"

You can now run the macro (F5). The email is opened in draft. If you extended this code to your satisfaction and all's good, change .Draft to .Send for future enjoyment.


Sub EmailDemo()
    Dim strEmailAddess As String
    Dim strFirstName As String
    Dim strLastName As String
    Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
            strEmailAddess = "t.axen@rivm.nl"
            strFirstName = "Thomas"
            strLastName = "Axen"
            Dim mimEmail As Outlook.MailItem
            Dim strBody As String: strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
                                             "Just a reminder that our meeting to discuss the environment is later this week. See you Thursday!"
            Set mimEmail = appOutlook.CreateItem(olMailItem)
            With mimEmail
                .To = strEmailAddess
                .Subject = "Taskforce meeting"
                .Body = strBody
                .Display
            End With
    End Sub
simple email