How to send email from excel with attachment

Last updated: 22nd August 2022

We often use Microsoft Office Outlook application to manage emails, contacts etc. from home and office. It is arguably one of the safest and secure ways to manage confidential emails. It has many useful features. Here in this article, I am going to show you how to send emails from Excel dynamically using VBA and Outlook.

👉   You may like this... VBA code to send email from excel with table in body

Although you can send and receive emails from Outlook directly, you can actually automate the process using a simple macro.

Automation is a process where an application gets access to methods and properties of another application.

To get access to Outlook methods and properties, we have to create an instance of Outlook in VBA. To initialize the Outlook application, we will use the CreateObject() function to create an object of the application.

Dim objOutlook as Object
Set objOutlook = CreateObject("Outlook.Application")

Important Note: Before reading this article further, you must first configure Microsoft Office Outlook in your computer. Else, the code example that I am going to show you here will not produce the desired result.

👉 You may also like this: How to parse Outlook emails and show it in your Excel worksheet using VBA

Add Outlook Reference

First, we need add a reference of the Outlook object in VBA. In the top menu find Tools and choose References…. In the References dialog box, find Microsoft Outlook 16.0 Object Library, click the check box and press OK.

Note: If you are using Office 2007, add Microsoft Outlook 12.0 Object library.

We will now write the macro to send an email from our Excel workbook.

Option Explicit

Private Sub send_email
    Dim objOutlook As Object        
    Set objOutlook = CreateObject("Outlook.Application")
    
    
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .to = ""       
        .Subject = "This is a test message from Arun Banik"
        .Body = "Hi there"
        .Send   		
    End With
    
    
    Set objEmail = Nothing:    Set objOutlook = Nothing
End Sub

If everything is right, then it will send an email with a subject and a message saying "Hi there".

As you can see, I have created two objects (objOutlook and objEmail), one for outlook application and another for creating email.

01) Object objOutlook: Using the CreateObject() function, I have initialized Outlook. I can now access email properties with the CreateItem() method.

02) Object objEmail: Using this object, I'll create an Outlook item. This will give access to properties such as to, body and subject etc.

Using .Display property to display message before sending email

Here's another important feature that you can use. You can actually display or see the email message like body, subject, attachements etc. in Outlook before sending the email.

To do this simply comment the .Send property and add .Display property. Please remember, the properties are case-sensitive.

With objEmail
    .to = ""
    .Subject = "This is a test message from Arun Banik"
    .Body = "Hi there"
    
    .Display   		
End With

👉 Now you can send emails from your Excel worksheet with Table in Body. See this post.

This will now open Ms-Outlook application and shows the message with all the parameters. You can see the To address, with the Subject and Body. Click the Send button (in outlook) to email the message to its address.

👉 Do you know you can send emails automatically on special occasions from Excel to multiple recipients? Check this out.

Add Attachments, CC and BCC in Excel

Similarly, you can test with other important properties such, CC, BCC, Attachments etc.

With objEmail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "This is a test message from Arun"
    .Body = "Hi there"
    .Attachments.Add ("e:\report.doc")
    .Send
End With

👉 I am sure you will like this: How to send emails to Multiple recipients from your Excel workbook using VBA and Outlook.

Conclusion

Now you know how to send emails from an Excel file using VBA and Outlook. Here we also learned how to add Attachments, Blind Carbon Copy or BCC etc., using VBA. It is very simple. Try working with other properties too.

Thanks for reading. ☺

← PreviousNext →

How do you attached an email in Excel?

Which type of file can be automatically sent as an email attachment from Excel?

How do I transfer data from Excel to email?