How to Send Email from Excel using VBA Macro: A Complete Guide
Featured

How to Send Email from Excel using VBA Macro: A Complete Guide

Many of us often use Microsoft Office Outlook emailing the application to manage email messages, calendars, contacts, etc. The application is used worldwide for sending, receiving, and managing business email effectively and efficiently. Unarguably, the emailing app offers countless useful benefits too. In this article, we will discuss the procedure to send email from Excel using VBA Macro and MS Outlook.

User can write a small program using VBA. This program will transform XLS worksheets into a dynamic and versatile application.

Steps to Send Email from Excel using VBA Macro

To begin with, create an instance of Outlook to gain access to the application’s methods and properties. Before getting started, add a Command Button (an ActiveX Control) on the Excel sheet. The click event of this button will trigger the send mail procedure. To view the ActiveX Controls, click on the Developer tab which is located on the top of the Excel sheet, next to the View tab. If you won’t be able to find the Developer tab, you can add it manually to the top tabs.

  1. On hitting the Developer tab, find Insert in the Controls group and click on it. A list of Form Controls and ActiveX Controls will appear.
  2. Under ActiveX Controls, click on the Command Button and add it to the Excel sheet.
  3. Now place the command button anywhere on the Excel sheet.
  4. Under Controls group click on the Design Mode. This will enable you to edit button control right from the sheet.
  5. Now, right-click the button, click on CommandButton Object tab and click on Edit. Next, change the button name to Send Mail.

Display Message via .Display Property

Now, it’s time to write the program to send email from Excel using VBA. Double click on the Command button and find CommandButton1_Click() procedure to add the code. Go to Tools in the top menu and click on References.

Reference dialog box will appear. Find Microsoft Outlook 12.0 Object Library (make sure to select the higher version), click on the checkbox and click OK.

Option Explicit

Private Sub CommandButton1_Click()

    On Error GoTo ErrHandler

    ‘ SET Outlook APPLICATION OBJECT.

    Dim objOutlook As Object

    Set objOutlook = CreateObject(“Outlook.Application”)

   

    ‘ CREATE EMAIL OBJECT.

    Dim objEmail As Object

    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail

        .to = “admin@flamingo.com”

        .Subject = “This is a test message from Arpit”

        .Body = “Hi there”

        .Display                    ‘ DISPLAY MESSAGE.

    End With

‘ CLEAR.

    Set objEmail = Nothing:    Set objOutlook = Nothing

ErrHandler:

    ‘

End Sub

.Display property used in the code will display the message in the Outlook application with all the required parameters. Click on the Send button to send an email message to the recipient.

Send Message using .Send Property in Excel

Now change the .Display property with the .Send property.

With objEmail

    .to = “admin@flamingo.com”

    .Subject = “This is a test message from Arpit”

    .Body = “Hi there”

    .Send                 ‘ SEND THE MESSAGE.

End With

Add Cc, Bcc & Attachments in Excel

You can also add other important attributes such as Cc, Bcc and attachments to the email message using the following code.

With objEmail

    .To = “admin@flamingo.com”

    .CC = “arpit@gmail.com”

    .BCC = “arpit@gmail.com”

    .Subject = “This is a test message from Arpit”

    .Body = “Hi there”

    .Attachments.Add (“f:\filename.doc”)

    .Send

End With

Conclusion

Using the above mentioned code, you can easily send email from Excel using VBA and MS Outlook. Here, the article includes the code to send emails with attachment to add Bcc and Cc.