How To Send Email From Excel (With Outlook) Using VBA: Tutorial And Code Examples

Several VBA tutorials within Power Spreadsheets explain how to carry out certain activities within Excel. Examples of such activities are saving or opening an Excel workbook, saving an Excel file as PDF, finding the last row, copying and pasting or deleting blank rows.

However, in certain circumstances, you might have to (or want to) work with other applications from Excel. This makes sense. After all, a particular process may involve applications other than Excel.

Visual Basic for Applications allows you to automate other Office Applications directly from Excel.

In this particular VBA tutorial, I provide a thorough step-by-step introduction to how you can work with Outlook from Excel using VBA. This is a very common (although not the only) type of integration. In fact, it’s likely that you’ve integrated both applications in the past. As explained by Excel MVP Mike Alexander in Excel Macros for Dummies:

If you’ve sent or received an Excel workbook through Outlook, you’ve integrated the 2 programs; albeit manually.

More precisely, however, this VBA tutorial focuses on how you can send an email with Outlook while working from Excel. On the way to achieving this objective you’ll learn about other topics, such as the following:

• The Outlook object model, and some of the most relevant constructs you must understand for purposes of sending an email using VBA.
  
  
• What is Automation, and how you can use it to control Outlook from Excel.

As usual, I provide practical examples of VBA code that you can easily adjust and start using right now. In addition to the step-by-step explanations that I include below, I’ve created sample Excel workbooks to accompany this VBA tutorial. You can get immediate free access to these workbooks (and all the others accompanying the posts within Power Spreadsheets) by clicking here.

The following table of contents lists the main topics I cover in this Excel tutorial. You can use it to quickly navigate to the section that interests you the most.

Since the main topic of this blog post is how to manipulate Outlook from Excel, let’s start by taking a look at…

The Microsoft Outlook Object Model: A Quick Introduction

My focus in Power Spreadsheets is Microsoft Excel. Therefore, I’ve previously created a comprehensive tutorial about Excel’s object model. You can find this here. I’ve also written other blog posts that explain different object models, such as that of the Ribbon Commander.

However, here’s the deal:

Each application (Excel and Outlook in this particular case) has its own object model. When working with Visual Basic for Applications, you’re (basically) manipulating the objects from the relevant application.

Therefore, you need to have a good understanding of the object model of the application(s) you’re working with. For this reason, I provide a quick introduction to the Outlook object model in this section.

In any case, several of the more general explanations I provide in the blog post about Excel’s object model to which I link to above apply (with some adjustments) to the Outlook object model.

Let’s start by reviewing:

What Is The Outlook Object Model

You can think of a VBA object model as a hierarchy that contains all the objects you can work with.

Within such a hierarchy, there’s an object at the top. That object may contain other objects. Those objects may, in turn, contain other objects.

You probably see the pattern there…

In more general terms, each object (anywhere in the hierarchy) can contain other objects. If you continue going down the hierarchy, you eventually find an object that doesn’t hold other objects.

Even though the components of the Excel and Outlook object models differ, the basic idea behind them is pretty much the same.

The object at the top of the Outlook hierarchy is Outlook itself or, more precisely, the Application object. In other words, the Application object represents the Outlook application.

This is similar to what happens with the Excel object model. In Excel, the object at the top of the hierarchy is also the Application object. In this case, the Application object represents the Excel application.

The Outlook Application object has several useful purposes. For this VBA tutorial, the following 2 are particularly important:

• Purpose #1: Since the Outlook Application object is at the top of the hierarchy, it’s the root from which you access the other objects within the Outlook object model.
  
  
• Purpose #2: It has the CreateItem method (which I explain in the next section). CreateItem allows you to create an Outlook item (such as an email) without having to go through the whole object model hierarchy.

In order to better understand why the above makes the Outlook Application object important, let’s take a look at the…

Application.CreateItem Method

As I mention above, you can use the Application.CreateItem method to create an Outlook item.

The basic syntax of CreateItem is as follows:

expression.CreateItem(ItemType)

Within this syntax, “expression” represents an Application object.

The only parameter of the CreateItem method is ItemType. This parameter is required. You use it to specify what type of item you want to create. For these purposes, you use the OlItemType enumeration which appears below:

Value	Name	Object	Represented item
0	olMailItem	MailItem	Mail message
1	olAppointmentItem	AppointmentItem	Meeting, one-time appointment or recurring appointment
2	olContactItem	ContactItem	Contact
3	olTaskItem	TaskItem	Task
4	olJournalItem	JournalItem	Journal entry
5	olNoteItem	NoteItem	Note
6	olPostItem	PostItem	Post
7	olDistributionListItem	DistListItem	Distribution list

The Application.CreateItem method returns the new Outlook item that you’ve specified through the appropriate OlItemType value.

Despite the OlItemType enumeration allowing you to create 7 different types of Outlook items, we’re (currently) only interested in 1:

The MailItem Object

As I explain above, the MailItem object represents a mail message.

Since the main topic of this VBA tutorial is how to send an email from Excel, it makes sense to spend some time exploring this particular object, particularly its main properties and methods.

According to the data currently available at the Microsoft Dev Center, the MailItem object has 90 properties, 18 methods and 26 events. Covering each of these items exceeds the scope of this tutorial. However, in the following sections, I explain several of the most important VBA constructs from within the MailItem object that you can use when sending an email from Excel.

VBA Properties To Specify Email Recipients

Let’s start by taking a look at how can you specify the recipients of the email you’re preparing.

Outlook has a Recipients object and a Recipient object. You access the Recipients object through the MailItem.Recipients property.

The Recipient object represents a “user or resource” in Outlook. This “user or resource” is usually an email recipient. The Recipients object simply represents a collection of Recipient objects for a particular Outlook item. This item, for our purposes, is an email (MailItem).

You can work with the Recipients and Recipient objects for purposes of specifying the recipients of an email you’re creating using Visual Basic for Applications. In fact, the suggestion at the Microsoft Dev Center (here, here and here) is to use the Recipients collection to modify the email recipients. You can also check out JP’s comment (which expresses a similar opinion) in this post at Daily Dose of Excel.

However, in this VBA tutorial, I’ll show you how to specify email recipients by using the properties of the MailItem object. This approach is commonly used by other Excel and VBA experts, including Excel authorities Ron de Bruin, Mike Alexander, Jacob Hilderbrand and Allen Wyatt.

Let’s start by taking a look at the…

MailItem.To Property

The MailItem.To property is read/write. It’s purpose changes depending on whether you’re reading or writing:

• When reading, it returns “a semicolon-delimited String list of display names for the To recipients”.
  
  
• When writing, you can use it to set the semicolon-delimited (;) list of display names.

You can use the To property for purposes of specifying the email recipients that you want to include in the To field.

The syntax of the MailItem.To property is as follows:

expression.To

“expression” is a MailItem object.

MailItem.CC Property

The MailItem.CC property is substantially similar to the MailItem.To property that I describe in the previous section. More precisely:

• The CC property is also read/write.
  
  
• You can use CC for purposes of returning or setting the list of display names for the CC (carbon copy) recipients. If you have several names, separate them with a semicolon (;).

The basic syntax of the CC property is as follows:

expression.CC

“expression” is, once again, a MailItem object.

MailItem.BCC Property

The MailItem.BCC property is also substantially similar to the To and CC properties I explain above. In other words:

• BCC is a read/write property.
  
  
• The main purpose of the BCC property is to return or set the list of display names for the BCC (blind carbon copy) recipients of an email. If you want to add several recipients, separate them with a semicolon (;).

The syntax of MailItem.BCC is as follows:

expression.BCC

As in the previous 2 properties (To and CC), “expression” is a variable representing a MailItem object.

VBA Property To Specify Email Subject

You can use the MailItem.Subject property for purposes of specifying the subject of the email you create with VBA from Excel.

The Subject property is read/write. Additionally, it’s the default property for Outlook items (including MailItem). Its basic syntax is as follows:

expression.Subject

“expression” is a MailItem object.

VBA Properties To Draft And Format The Email

For purposes of determining the contents and format of your email’s body, you use 2 separate properties:

• Property #1: MailItem.Body, MailItem.HTMLBody or MailItem.RTFBody property.
  
  
• Property #2: MailItem.BodyFormat.

Let’s take a look at each of these properties separately:

MailItem.BodyFormat Property

The MailItem.BodyFormat property allows you to return or set the format standard of the email’s body.

You determine the body’s format by specifying the appropriate value from the OlBodyFormat enumeration:

Name	Value	Description
olFormatUnspecified	0	Unspecified
olFormatPlain	1	Plain
olFormatHTML	2	HTML
olFormatRichText	3	Rich text

When working with Outlook, however, you generally specify either plain text, Rich Text Format (RTF), or HTML. If you change the MailItem.BodyFormat property (i) from Rich Text Format to HTML or (ii) from HTML to RTF, you lose any previous text formatting.

The property you use to specify the actual body of the email generally depends on which OlBodyFormat value you choose. More precisely:

• If you work with olFormatPlain (1) or don’t use the BodyFormat property, use the MailItem.Body property.
  
  
• If you choose olFormatHTML (2), check out the MailItem.HTMLBody property.
  
  
• If you set the value of BodyFormat to be equal to olFormatRichText (3), work with the MailItem.RTFBody property.

Let’s take a look at each of these 3 properties:

MailItem.Body Property

The main purpose of the MailItem.Body property is to either return or set a string that represents the “clear-text body” of an Outlook item. The item, in this case were looking at, is an email message (MailItem).

The basic syntax of the Body property is as follows:

expression.Body

“expression” is a MailItem object.

MailItem.HTMLBody Property

You can use the MailItem.HTMLBody property to return or set a string that represents the HTML body of an item (such as the MailItem we’re working with). If you work with the HTMLBody property, Visual Basic for Applications updates the Body property immediately.

The syntax of the HTMLBody property is roughly the same as that of Body (above):

expression.HTMLBody

“expression” is a variable representing a MailItem object.

If you choose to work with the HTMLBody property, you must specify the relevant string using HTML syntax.

MailItem.RTFBody Property

The MailItem.RTFBody property returns or sets a Byte array representing the body of an Outlook item (such as a MailItem) in Rich Text Format (RTF).

The following is the syntax of RTFBody:

expression.RTFBody

“expression”, as in previous cases, is a MailItem object.

VBA Method To Add Email Attachments

For purposes of adding attachments to your email, you can use the MailItem.Attachments property. This property returns an Attachments object. Its basic syntax is as follows:

expression.Attachments

“expression” is a MailItem object.

In turn, the Attachments object (returned by the Attachments property) is the collection of Attachment objects of a particular Outlook item (MailItem in this case).

Finally, the Attachment object represents a document or link to a document within an Outlook item (for purposes of this tutorial, MailItem).

This may sound complicated…

However, for purposes of this VBA tutorial, you only need to familiarize yourself with one particular construct:

The Attachments.Add Method

You can use the Attachments.Add method for purposes of adding a new Attachment object to the Attachments collection.

The basic syntax of Attachments.Add is the following:

expression.Add (Source, Type, Position, DisplayName)

“expression” represents an Attachments object. The 4 parameters of the Add method are the following:

• Parameter #1: Source.
  
  Source is the only required parameter. You use it to specify the source of the attachment you want to add. You can specify either of the following 2 sources:
  
  #1: A file. You specify a file by using its full file system path, including the file name.
  
  #2: An Outlook item.
  
  
• Parameter #2: Type.
  
  Type is an optional parameter. It allows you to specify the type of the attachment. For these purposes, you use the OlAttachmentType constants, which include the following values:
  
  
  • 1 (olByValue): If you choose this value, the attachment is a copy of the original file in the file system. Therefore, it can be accessed even if you remove the original file.
    
    
  • 5 (olEmbeddeditem): In this case, the attachment is an .msg (Outlook message format) file. The attached file is a copy of the original message.
    
    
  • 6 (olOLE): If you use this value, the attachment is an OLE document.
    
    
• Parameter #3: Position.
  
  Position is an optional parameter that applies solely when you’re working with Rich Text as body format (I explain this topic above). If you use Position, it allows you to specify the position where the attachment is placed within the body of the message. If that’s the case, you’re likely to use 1 of the following 3 values:
  
  
  • 0: Hides the attachment.
    
    
  • 1: Positions the attachment at the beginning of the body.
    
    
  • A value greater than the number of characters in the body of the message: Places the attachment at the end of the body.
    
    
• Parameter #4: DisplayName.
  
  DisplayName is an optional parameter that only applies if (i) you’re working with RTF as body format (a topic I cover above), and (ii) you set the Type parameter (#2 above) to 1 (olByValue). If all of these conditions are met, the DisplayName you specify is displayed in an Inspector object for the attachment or when the user views the properties of the attachment.
  
  If you’re not working with rich text as body format (but rather plain text or HTML), the displayed name is the file name within the source parameter (#1 above).

According to the Microsoft Dev Center, it’s advisable to save items before adding or removing objects from the Attachments collection. Note that, when you add an Attachment object to the Attachments collection of an item, the Type property of the relevant Attachment object is olOLE (6) “until the item is saved”. Saving the item prior to adding or removing objects helps you to ensure consistent results.

VBA Properties And Methods To Control Email Delivery, Display And Saving

Visual Basic for Applications allows you to control what happens once you’ve created your email. Some of the most common actions that you carry out once the email is ready are the following:

• Action #1: Send the email.
  
  
• Action #2: Display the email for reviewing.
  
  
• Action #3: Save the email.

The following sections cover some of the most common VBA properties and methods that you can use for these purposes. Let’s start by taking a look at the…

MailItem.DeferredDeliveryTime Property

You can use the MailItem.DeferredDeliveryTime property for purposes of returning or setting the date and time in which the email message is to be delivered.

The basic syntax of the DeferredDeliveryTime property is as follows:

expression.DeferredDeliveryTime

“expression” is a MailItem object.

The DeferredDeliveryTime property uses a date and time value. There are different ways in which you can specify this. The following are common ways of doing this:

• Specifying the relevant date and time as a string, as I do in the example below.
  
  
• Using Functions such as the DateAdd Function, as explained by MVP Ron de Bruin.

MailItem.Send And MailItem.SendUsingAccount Methods

The MailItem.Send method allows you to send the relevant email message.

The syntax of the MailItem.Send method is as follows:

expression.Send

“expression” is a variable representing a MailItem object.

As a general rule, the MailItem.Send method uses the default email account for the session you’re in to send the email. This is particularly relevant if, for example, you’re in a session where there are multiple such accounts defined in the relevant profile. In such case, the default account (used by the Send method) is the one that was added first to the profile.

Despite the above, you can use a different email account to deliver the message. For these purposes, you use the MailItem.SendUsingAccount property. This property allows you to return or set the Account object (the account) from which the email (MailItem) is sent.

The syntax of this property is the following:

expression.SendUsingAccount

“expression” is a MailItem object.

MailItem.Display Method

The main purpose of the MailItem.Display method is to display an Inspector object for the MailItem. An Inspector object represents the window in which an Outlook item is displayed.

In other words, the MailItem.Display method allows you to display the email you’re working on in a message window.

The following is the syntax of the Display method:

expression.Display(Modal)

“expression” is a variable representing a MailItem object.

The only (and optional) parameter of this method is Modal. This allows you to make the relevant window modal. To make the window modal, set the method to True. The default (and more commonly used) value of the Modal parameter is False.

MailItem.Save Method

You can use the MailItem.Save method for purposes of saving the email you’re working on to the Outlook default folder for MailItems. In most cases, the folder to which the email is saved to is the Drafts folder.

The basic syntax of the Save method is the following:

expression.Save

“expression” is a MailItem object.

VBA Property To Set Email Importance

You can use the MailItem.Importance property for returning or setting the relative importance level of the email you’re creating.

The basic syntax of the Importance property is as follows:

expression.Importance

“expression” represents a MailItem object.

You set the value of this property by using the OlImportance enumeration. The following table shows this enumeration:

Name	Value	Description
olImportanceLow	0	Low importance.
olImportanceNormal	1	Medium importance.
olImportanceHigh	2	High importance.

VBA Property To Request A Read Receipt

The MailItem.ReadReceiptRequested property allows you to request a Read Receipt.

The basic syntax of this property is the following:

expression.ReadReceiptRequested

“expression” is a MailItem object.

You can set the value of the ReadReceiptRequested property to True in order to request a read receipt.

Now that you know what are the most commonly used VBA methods and properties that you can use for purposes of creating an email with Outlook, let’s take a look at how you can access those VBA constructs from Excel.

Automation And Binding: Early Vs. Late Binding

As explained by author Richard Mansfield in Mastering VBA for Microsoft Office 2016, there are certain tools that allow you to access one application from another one. In the current VBA tutorial, we access (and work with) the Outlook application from Excel.

Mansfield goes on to explain several tools that you can use to communicate between applications, including the following:

• Tool #1: Automation. Automation is sometimes referred to by its previous name: Object Linking and Embedding (OLE).
  
  
• Tool #2: Data objects.
  
  
• Tool #3: Dynamic Data Exchange (or DDE).
  
  
• Tool #4: SendKeys.
  
  
• Tool #5: The Command Line.

In this Excel tutorial, I only work with tool #1: Automation. This is perhaps the most commonly used tool. In fact, even though Mastering VBA for Microsoft Office 2016 provides very detailed explanations about the tools I mention above, the author agrees that:

Automation is usually the most effective method for transferring information from one Windows application to another. If the applications you’re using support Automation, use it in preference to the alternatives.

I may write about the other tools in future blog posts. If you want to be informed whenever I publish new posts in Power Spreadsheets, please feel free to register for our Newsletter by entering your email address below:

In Excel 2016 Power Programming with VBA, Excel authorities Dick Kusleika and Mike Alexander define Automation (within this context) as:

The means of manipulating or controlling one application with another.

As explained in Mastering VBA for Microsoft Office 2016, in the context of Automation you have 2 applications that interact with each other:

• The Server or Object Application provides certain tools.
  
  
• The Client or Controlling Application uses the tools provided by the Server Application.

When you’re working with a particular Microsoft Office Application (for example, Excel), you have access to that application’s object library by default. This isn’t the case for the object libraries of other Microsoft Office Applications.

However, you can usually access the Component Object Model (COM) objects of a Server Application. The Client Application can then access and use the capabilities of the Server Application for different purposes.

Within the context of this VBA tutorial:

• The Server Application is Outlook.
  
  
• The Client Application is Excel.

Therefore, Outlook provides certain tools. I explain several of these in the first few sections above. Excel harnesses the power of these different tools to achieve a goal: create and send an email with the active Excel workbook as an attachment.

From a broad perspective, the main thing you need to do in order to use Automation is create a connection between the Server and the Client Applications. In this way, the tools that the Server Application has are available to the Client Application.

There are 2 ways in which you can set up the connection I refer to above. You do this by choosing one of the following types of binding:

• Early binding.
  
  
• Late binding.

I explore both of these types of binding in the following sections. Let’s start by taking a look at…

Early Binding

The basic idea behind early binding is that you make a reference to the relevant object library (for purposes of this tutorial, that’s Outlook’s Object Library). The main consequence of creating this reference is that the objects, properties and methods within the referenced library are available for you to work with while designing or coding your VBA application.

This has several more specific consequences that influence what you can do while creating the macro. For example:

• Consequence #1: The objects, properties and methods of the referenced library are available within the Object Browser of the VBE.
  
  The following screenshot shows my Object Browser after I’ve created a reference to the Outlook Object Library. Notice, for example, how the MailItem object that I explain above is listed.
  
  
  
  
• Consequence #2: The Visual Basic Editor is able to auto-list members using its IntelliSense features.
  
  In the following image, for example, the Visual Basic Editor displays a list of options to help me complete the statement I’m writing. This includes the CreateItem method that I explain above.
  
  
  
  I explain the Auto List Members option, and how you can enable or disable it, in this blog post.
  
  
• Consequence #3: You have access to Outlook’s built-in constants. This allows you to work with the built-in text constants instead of the values behind them.
  
  You can check out how this difference (text vs. value constants) looks like in an actual VBA application below.
  
  
• Consequence #4: In order to be able to use early binding, the relevant referenced object library must exist on the system you’re working on.
  
  What I mean is this:
  
  As I explain in the following section, you usually create a reference to a particular version of an Office Application (Outlook 2016 in the example below). If the referenced version doesn’t exist in the system where you’re running the macro, an error occurs. For example, the sample macro below (which references the Outlook 2016 Object Library) fails if you try to execute it from a computer that has a previous version of Outlook.

One of the main advantages of early binding is that it results in better speed and performance. As explained by Excel experts Bill Jelen (Mr. Excel) and Tracy Syrstad in Excel 2016 VBA and Macros:

Code written with early binding executes faster than code with late binding.

The main reason for this, as explained by Richard Mansfield in Mastering VBA for Microsoft Office 2016, is that Visual Basic for Applications is able to compile more information instead of resolving them during runtime. Mansfield argues that another advantage of early binding is the fact that, since you specify the object type you’re working with upon declaring it, you’re less likely to make mistakes in that regard.

Considering the above, let’s take a look at how you can…

Reference The Microsoft Outlook Object Library

You can easily create a reference to the Microsoft Outlook object library from the Visual Basic Editor in the following 3 quick steps:

• Step #1: Click on “References…” within the Tools menu.
  
  
  
  
• Step #2: Select the relevant Microsoft Outlook Object Library within the Available References box. For purposes of making the selection, simply click on the check box on the left side.
  
  The following screenshot shows how this looks like when working with Office 2016. Notice that the label of the object library includes the Outlook version (16 in this case). If you’re working with a different version of Microsoft Office, the number of the version changes.
  
  
  
  
• Step #3: Confirm your selection by clicking on the OK button on the upper right corner of the References dialog box or pressing the Enter key.
  
  

Once you’ve set the reference to the appropriate Outlook Object Library, you can start declaring object variables using the appropriate type. In particular, you’ll usually declare an object variable to represent a new instance of the Outlook application.

For example, in the VBA code example that I provide below, I declare the 2 object variables as Outlook.Application and Outlook.MailItem. In this case, both Application and MailItem are appropriate Outlook variable types.

Notice that, as explained by Bill Jelen (Mr. Excel) and Tracy Syrstad in Excel 2016 VBA and Macros, declaring an object variable generically As Object “forces the program to use late binding”.

As I explain in the previous section, you can only execute the sample macro below (with early binding) from a computer that has access to the Outlook 2016 Object Library. Otherwise, an error occurs during compilation.

For example, the following screenshots shows what happens when I try to execute the sample macro below on a computer that doesn’t have Outlook 2016 installed. Notice the following:

• #1: There’s a compile error caused by the fact that the relevant object library can’t be found.
  
  
  
  
• #2: Within the References dialog box, the Microsoft Outlook 16.0 Object Library is flagged as “MISSING”.
  
  

When this happens, you can try changing the reference to the available version of the relevant application’s object library. If the originally referenced application version and the newly referenced version aren’t too far apart, several of the elements of the object model should be substantially the same.

You may find the fact that early binding requires that the object library that you referenced macro creation is also available upon execution annoying.

This is a disadvantage of early binding. In practice, particularly if you create VBA applications designed to be executed on several systems, it may be difficult to guarantee that the object library that you originally referenced is always available. Furthermore, as explained in Mastering VBA for Microsoft Office 2016, some applications can’t provide access to their object models while you craft your VBA procedures (which is required for early binding).

If this is a concern, then you may want to consider using…

Late Binding

Late binding doesn’t require that you set up a reference to the relevant object library when crafting your VBA code.

More precisely, in late binding, you create an object that references the appropriate object library when the VBA code is executed. For purposes of this VBA tutorial, this object library is the Outlook Object Library. Then, a link to the appropriate library is created.

When working with late binding, you usually declare object variables generically As Object[s]. This is the case in the macro example below. As explained at the Microsoft Dev Center, the consequence of such declaration is that the declared object variable “can contain a reference to any type of object.”

The main practical consequences of late binding are as follows. Notice the contrast with the consequences of using early binding that I explain above.

• Consequence #1: The VBE isn’t able to auto-list members using IntelliSense and built-in constants aren’t available.
  
  Instead of using built-in text constants, you have to work with the actual values behind them. I show you an example of how this works (and what’s the difference vs. the early binding cases) below.
  
  
• Consequence #2: Since you’re not creating a reference to an object library when creating the macro, there’s no need to ensure that a particular object library is available in the system where the VBA application is executed. As explained in Excel 2016 VBA and Macros, what you have to ensure is that the objects, properties and methods you’re using actually exist.

You might be wondering:

What happens when the versions of the relevant application differ?

When you’re working with late binding, the relevant object variable can contain a reference to any type of object (as I explain above). The binding to the object occurs when the VBA application runs. At that time, the system is able to verify what is the appropriate version of the relevant Office Application. Only then does the code access the object through the object variable and the appropriate links to the applicable object model are created.

As a consequence of the above, Visual Basic for Applications is generally able to handle version differences appropriately and avoid failure due to compatibility issues. In the words of Excel authorities Dick Kusleika and Mike Alexander (in Excel 2016 Power Programming with VBA):

Late binding allows your automation procedures to be version-independent.

Late binding, however, isn’t without its disadvantages. After all, the lack of a reference to the appropriate object library means that Excel doesn’t really understand what you’re referring to.

Therefore, as I mention above, the Visual Basic Editor isn’t able to auto-list members. As explained by Excel authorities Bill Jelen (Mr. Excel) and Tracy Syrstad in Excel 2016 VBA and Macros, “built-in constants aren’t available”. As a consequence of this, certain errors aren’t evident until you execute the macro.

Another potential disadvantage of late binding is that your VBA code is likely to execute slower (vs. the equivalent code using early binding).

We now have the tools needed to create an actual VBA application that sends emails from Excel using Outlook. Let’s take a look at the…

VBA Code To Send Email From Excel With Workbook As Attachment

In this section, I thoroughly explain a practical example of VBA code that you can use for purposes of sending an email with the active workbook as attachment.

As I explain above, you can generally use early or late binding for purposes of accessing the Outlook Object Model from Excel. There are a couple of differences in the VBA code depending on the binding you’re using.

Therefore, I provide the full sample VBA code for both early binding and late binding. I also highlight and explain the main differences between them.

Let’s start by taking a look at the…

VBA Code To Send Email From Excel Using Early Binding

The following sample macro (Send_Email_Excel_Attachment_Early_Binding) sends an email message with the active workbook as an attachment. This VBA Sub procedure uses early binding. You can find the equivalent version (using late binding) below.

If you use early binding (including the sample code above), you must create a reference to the Microsoft Outlook Object Library by following the indications I provide above.

In order to understand what this macro does exactly, let’s take a look at each of the lines of code:

Line #1: Dim outlookApp As Outlook.Application

Declares the outlookApp object variable using the Dim statement. outlookApp is declared as of the type Outlook.Application.

The purpose of this line of code is to create a variable that represents the Outlook application. As explained in Excel Macros for Dummies, this object variable “exposes the Outlook Application object”.

Line #2: Dim outlookMail As Outlook.MailItem

Declares the outlookMail object variable using the Dim keyword. outlookMail is declared as of the type Outlook.MailItem.

The main purpose of this line of code is to create a variable that represents the email message that the sample macro creates and sends.

Line #3: Set outlookApp = New Outlook.Application

Uses the Set statement to assign a new (New) instance of the Outlook application (Outlook.Application) to the outlookApp object variable.

In practical terms, this line of code #3 does 2 things:

• #1: Creates a new instance of Outlook.
  
  This is achieved by “New Outlook.Application”.
  
  
• #2: Assigns this new instance to the outlookApp object variable. For these purposes, the equal sign (=) acts as an assignment operator. In other words, it (i) assigns the expression to its right side (New Outlook.Application) to (ii) the variable to its left side (outlookApp).

Line #4: Set outlookMail = outlookApp.CreateItem(olMailItem)

Uses the Set statement to assign a new email item (olMailItem) to the outlookMail variable. The new email item is created by using the CreateItem method.

As explained by Excel MVP Mike Alexander in Excel Macros for Dummies, this is:

Similar to manually selecting the New Message button in Outlook.

Lines #5 And #17: With outlookMail | End With

These lines of code are the opening and closing statements of a With… End With block.

The consequence of using a With… End With statement is that the series of statements within the block (lines #6 to #16 below) work with the object referred to in the opening line (outlookMail). In other words, all of the following lines of code work with the email item represented by outlookMail.

As you’ll see in the following sections, the lines within the With… End With block allow you to do the following:

• Step #1: Build the email you want.
  
  
• Step #2: Send the created email.

Let’s take a closer look at each of these statements:

Lines #6, #7 And #8: .To = “sampleRecipient1@sampleEmail1.com ; sampleRecipient2@sampleEmail2.com ; sampleRecipient3@sampleEmail3.com” | .CC = “sampleRecipient4@sampleEmail4.com ; sampleRecipient5@sampleEmail5.com ; sampleRecipient6@sampleEmail6.com” | .BCC = “sampleRecipient7@sampleEmail7.com ; sampleRecipient8@sampleEmail8.com ; sampleRecipient9@sampleEmail9.com”

These lines use the MailItem.To, MailItem.CC and MailItem.BCC properties for purposes of specifying the email recipients.

You can easily change these recipients in order to use the sample VBA code for your own purposes. I do this (use my own email address) for purposes of testing the sample macro below.

Line #9: .Subject = “How to send email from Excel with Workbook as attachment using VBA”

Uses the MailItem.Subject property to specify the subject of the email message.

Line #10: .BodyFormat = olFormatHTML

Uses the MailItem.BodyFormat property to specify that the format of the email messages body is in HTML.

Line #11: .HTMLBody = “Hi,<p>I ‘m sending this message from Excel using VBA.<p>Please find <strong>a sample Excel workbook</strong> attached.”

Uses the MailItem.HTMLBody property to set the text that goes in the email’s body.

The MailItem.HTMLBody property requires that you specify the string using HTML syntax. Notice how the string that I specify in line #12 contains 2 HTML tags:

• <p>, which defines a paragraph.
  
  If you’re working solely with VBA (no HTML), you can use the vbCr constant (or Chr(13)) for purposes of adding a carriage return.
  
  
• <strong></strong>, which “defines strong text”.

Line #12: .Attachments.Add ActiveWorkbook.FullName

Uses the Attachments.Add method for purposes of adding a new attachment to the Attachments collection.

In this particular case, the VBA code uses a single parameter: Source. Source represents the source of the attachment and, in this line #12, is “ActiveWorkbook.FullName”. This way of specifying the source relies on the following 2 properties:

• Property #1: Application.ActiveWorkbook, which returns the active workbook.
  
  
• Property #2: Workbook.FullName, which returns the (i) full disk path and (ii) name of the relevant workbook as a string.

By using the Application.ActiveWorkbook and Workbook.FullName properties, you’re therefore specifying the disk path and name of the current active workbook.

The practical consequence of this line #12 is that Outlook sets the active workbook as an attachment.

Line #13: .DeferredDeliveryTime = “8/8/2016 10:00:00 AM”

Uses the MailItem.DeferredDeliveryTime property for purposes of setting the date and time in which the email message is delivered.

In this particular case, I specify the date as a string. The email created by this sample macro is scheduled to be sent on August (month 8) 8, 2016 at 10 a.m.

Line #14: .Importance = olImportanceHigh

Uses the MailItem.Importance property for purposes of setting the importance level of the created email.

In the sample Send_Email_Excel_Attachment_Early_Binding macro, the Importance property is set to olImportanceHigh. This particular value of the OlImportance enumeration means that the email is marked as high importance.

Line #15: .ReadReceiptRequested = True

Sets the MailItem.ReadReceiptRequested property to True for purposes of requesting a Read Receipt.

Line #16: .Send

Uses the MailItem.Send method for purposes of sending the email message you’ve created.

If you want to check out the email draft before sending it, you can replace the Send method with the MailItem.Display method. The consequence of using the Display method is that Outlook shows you the email draft for review, instead of sending it automatically.

Note that, as explained by Excel experts such Vladimir Zakharov at MrExcel.com, Zack Barresse at VBA Express, Wyman Wong at Access-Excel.tips and Vijay Sharma at Chandoo.org, Outlook may create security warnings and require manual confirmation of the delivery when you try to send email programmatically.

Lines #18 And #19: Set outlookMail = Nothing | Set outlookApp = Nothing

Both of these lines of code use the Set statement to set the object variables used by the Sub procedure to Nothing.

The main purpose of both statements is to release the memory. In the words of Excel authority Mike Alexander (in Excel Macros for Dummies):

Releasing the objects assigned to your variables is generally good practice to reduce the chance of any problems caused by rouge objects that may remain open in memory.

Now that we’ve gone through each of the lines of the sample macro using early binding, let’s take a look at the…

VBA Code To Send Email From Excel Using Late Binding

As I anticipate above, I’ve also created a sample macro that is the equivalent of the Send_Email_Excel_Attachment_Early_Binding macro example above but uses late binding.

The following example of VBA code (Send_Email_Excel_Attachment_Late_Binding) creates an email message with the active workbook as an attachment.

Notice that the sample Send_Email_Excel_Attachment_Late_Binding macro above is almost identical to the Send_Email_Excel_Attachment_Early_Binding example above. The following image shows the only coding differences between both Sub procedures:

I explain each of these changing lines of code in the following sections:

Lines #1 And #2: Dim outlookApp As Object | Dim outlookMail As Object

Both lines of code use the Dim statement to declare an object variable (outlookApp and outlookMail) As Object. As I explain above, declaring an object variable As Object forces late binding.

These lines of code are substantially the same as lines #1 and #2 of the macro example that uses early binding (Send_Email_Excel_Attachment_Early_Binding). The difference is the type that I use:

• When using early binding, (i) outlookApp is declared as Outlook.Application and (ii) outlookMail is declared as Outlook.MailItem.
  
  
• When using late binding, both outlookApp and outlookMail are declared As Object.

Line #3: Set outlookApp = CreateObject(“Outlook.Application”)

This line of code is materially an equivalent of line #3 of the macro sample with early binding (Send_Email_Excel_Attachment_Early_Binding).

Therefore, it (i) creates a new Outlook instance and (ii) assigns this object reference to the outlookApp object variable. In both cases the object reference is to the new instance of the Outlook application.

Therefore, the main difference between this line of code and the one where early binding is used is the way the reference to the Outlook application is created.

• When working with early binding, the object reference is set to New Outlook.Application.
  
  
• When working with late binding, the object reference is built by (i) using the CreateObject function and (ii) setting it to reference the Outlook application (Outlook.Application).

The topic of the CreateObject function exceeds the scope of this particular tutorial. For purposes of understanding this macro, is probably enough if you know the following:

• The main purpose of the CreateObject function is to create and return a reference to an ActiveX object. An ActiveX object is “an object that is exposed to other applications or programming tools through Automation interfaces”.
  
  
• CreateObject has 2 parameters: (i) class and (ii) servername. class is a required parameter. servername is an optional parameter. As explained in Mastering VBA for Microsoft Office 2016, you can omit the servername parameter if you’re connecting with an application within the same hard drive.
  
  The line of code we’re analyzing only uses the class parameter. The syntax for specifying this parameter is as follows:
  

  appname.objecttype

  “appname” is the name of the application providing the object you want to reference. In the case we’re looking at, that’s Outlook.
  
  “objecttype” is the type of object you’re creating. In this case, that’s Application.

As explained in Excel 2016 VBA and Macros, you can also use the CreateObject function when working with early binding. However, you can’t use the New keyword (which I use in the early binding macro example) when working with late binding.

I may cover the CreateObject function in a future blog post. If you want to receive an email whenever I publish new content in Power Spreadsheets, please make sure to register for our Newsletter by entering your email address below:

Lines #4, #10 And # 14: Set outlookMail = outlookApp.CreateItem(0) | .BodyFormat = 2 | .Importance = 2

The purpose of these lines is exactly the same as that of lines #4, #10 and #14 of the previous macro example (Send_Email_Excel_Attachment_Early_Binding). In other words:

• Line #4 assigns a new email item (0) to the outlookMail object variable.
  
  
• Line #10 specifies that the format of the body is in HTML.
  
  
• Line #14 sets the importance of the email as high.

Notice that the difference between these lines of code and the equivalent ones when using early binding lies solely in the syntax:

• Line #4: When using early binding, the parameter of the CreateItem method is olMailItem. When using late binding, the parameter is 0.
  
  As I explain above, within the OlItemType Enumeration, the value 0 corresponds to olMailItem.
  
  
• Line #10: When using early binding, the BodyFormat property is set to olFormatHTML. When using late binding, the property is set to 2.
  
  Within the OlBodyFormat Enumeration, the value 2 represents olFormatHTML.
  
  
• Line #14: With early binding, the Importance property is olImportanceHigh. With late binding, the property is set to 2.
  
  Similar to the previous 2 lines, within the OlImportance Enumeration, the value 2 represents olImportanceHigh.

The explanation for these syntactical differences is that, as I mention above, built-in constants aren’t available when working with late binding. When working with late binding, you must use the actual values (numbers) when creating your VBA application.

Let’s take a look at the practical result of executing the sample macros:

Macro Examples To Send Email From Excel Using Outlook In Practice

For purposes of showing you the results of executing the sample macros that I explain in the previous section, I make the following adjustments to the VBA code:

• Modification #1: I change the value assigned to the MailItem.To property in line #6. The newly assigned value is an email address that I can actually check.
  
  
• Modification #2: I comment out lines #7 and #8. These lines used the CC and BCC properties to specify email recipients.
  
  
• Modification #3: I also comment out line #13. This line of code uses the MailItem.DeferredDeliveryTime property to set a future date and time in which the message is to be delivered.
  
  The consequence of this change is that the email is delivered immediately, as I write this blog post.

The following screenshot shows the VBA code of the sample Send_Email_Excel_Attachment_Early_Binding macro that I execute and highlights the changes I’ve made:

And the following image shows the code for the sample Send_Email_Excel_Attachment_Late_Binding macro. It also shows what are the changes I’ve made to the code:

The email Outlook sends looks the same in both (early and late binding) cases. The only difference is the fact that the attachment changes (I stored the sample macros in separate workbooks).

Notice how, in both cases, the main characteristics of the email are according to what the sample macros above specify. More precisely:

• The email Subject is “How to send email from Excel with Workbook as attachment using VBA”, as determined in line #9.
  
  
• The recipient is the one specified in line #6.
  
  
• The message is sent with High importance, as determined in line #14.
  
  
• The active workbook is attached, pursuant to line #12.
  
  
• The body of the email follows what’s specified in lines #10 and #11.

Finally, when I read the email within the Recipient Inbox, Outlook requests a Read Receipt, as required by line #15.

In some situations, you may not want to create a VBA application from scratch for purposes of sending an email from Excel. If that’s the case, you’ll be interested in the topic of…

Template To Send Email From Excel

Excel MVP Chris Newman is aware of the usefulness of being able to automate the delivery of email using Visual Basic for Applications. For these reasons, he created The Exporter Excel Template.

This particular template helps you automate certain tasks related to the conversion of Excel files into information-only files. The template has several features which you can check out by following the link above.

For purposes of this tutorial, I’ll just highlight the fact that, with The Exporter Excel Template, you can:

• #1: Automatically export your Excel spreadsheets and email those exports.
  
  
• #2: Customize and store the email recipients (To, CC and BCC).
  
  
• #3: Choose between a standardized or dynamic (by adding a formula) email subject and body text.
  
  
• #4: Determine whether the attached files should be saved or deleted from your hard drive once the relevant email has been created.

Conclusion

After reading this VBA tutorial, you have a very good understanding of how you can use Visual Basic for Applications for purposes of sending an email from Excel using Outlook. Some of the specific topics you’ve read about include the following:

• What is the Outlook Object Model.
  
  
• What are VBA methods and properties that you can use to create and send emails using Outlook.
  
  
• What is Automation, and why is important to learn about in order to be able to integrate Excel with other Microsoft Office Applications (including Outlook).
  
  
• What are the differences between early and late binding, as well as the advantages and disadvantages of each.

Towards the end of the blog post, I showed you a practical example of VBA code that you can easily adjust to start sending emails using Outlook while working in Excel. The sample code snippets covered both early and late binding.

Remember that you can get immediate free access to the Excel workbooks that accompany this tutorial (and store the sample macros) by clicking here.

Finally, I introduced to you The Exporter Excel Template, which can help you automate certain tasks related to the exporting and emailing of your Excel spreadsheets.

Now that you have all the tools to send emails while working from Excel, please take a couple of minutes to leave a comment below sharing your ideas about…

What other VBA constructs do you add to your macros to send emails from Excel using Outlook?

The sample macros I include in this Excel tutorial are relatively basic. As I explain at the beginning of this blog post, their purpose is to send an email from Excel by using Outlook.

However, if needed, you have plenty of possibilities to create more sophisticated VBA Sub procedures that leverage the Excel and Outlook object models to do amazing things. Some examples of the possibilities you have are the following:

• Sending an email with a table in the body of the email, as explained in this Mr. Excel thread.
  
  
• Sending a copy of the active worksheet as attachment (instead of the whole workbook), as explained by Excel authority Allen Wyatt.
  
  
• Sending the active workbook or certain selected worksheets as attachment, with several customization options, by using the VBA code suggested by Excel MVP Chris Macro at The Spreadsheet Guru.
  
  
• Saving an Excel file as PDF and attaching it to an email, as thoroughly explained at the Mr. Excel Forum, My Online Training Hub or AnalystCave.com.
  
  
• Create a list of recipient email addresses by looping through the cells in a particular cell range, as discussed here or by MVP Tom Urtis.
  
  
• Mail different files to each person in a range, mail a chart as a picture, zip the active workbook before sending it or doing any of the things covered by Excel MVP Ron de Bruin.
  
  
• Sending an email with a link to a workbook on a Share Drive, as explained by Excel authority Mike Alexander.
  
  
• Sending an email when a particular due date is reached, using the code proposed at Allen Wyatt’s ExcelTips Ribbon Interface.
  
  
• (i) Finding an email that meets certain criteria in the Inbox, (ii) changing its attachments prior to (iii) forwarding it, and (iv) moving the email to a new folder, as explained at globaliconnect.com.

If you’ve gone on to do any of this or have implemented other solutions, I’d love to hear your ideas as to what additional capabilities you like to include in your macros to send email from Excel.

I’m sure other readers of Power Spreadsheets will also find them helpful!

Books And Resources Referenced In This Excel Tutorial

Go to the official website of The Exporter Excel Template:

Click on any of the images below to purchase the book at Amazon now.