VBA Send Email From Excel - WallStreetMojo

All Courses 🛒Login

VBA Resources

• E-Books
• Career Guides
• Interview Prep Guides
• Free Practice Tests
• Excel Cheatsheets

VBA ResourcesVBA Send Email from ExcelVBA Basics

• Introduction to VBA
  • VBA Tutorial
  • VBA Examples
  • VBA Macros
  • VBA Code
  • VBA Editor
  • VBA Comment Block

• VBA Syntax and Functions
  • VBA Operators
  • VBA Option Explicit
  • VBA Sub
  • VBA ME
  • VBA Set Statement
  • VBA ByRef
  • VBA ByVal

• VBA Workbook and Projects
  • VBA ThisWorkbook
  • VBA Workbook
  • VBA Workbook Open
  • VBA ENVIRON
  • VBA Project Password

• VBA Basic Applications
  • VBA Send Email from Excel
  • VBA SendKeys
  • VBA Outlook
  • VBA PowerPoint
  • VBA Web Scraping

• VBA Resources
  • Excel VBA Macros Books

VBA Basics🏅 WSM MEMBERSHIP

ALL COURSES@ADDITIONAL50% OFF

UNLOCK DEAL VBA Send Email from Excel

Publication Date :

21 Jul, 2019

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Share

Download FREE VBA Send Email from Excel Template and Follow Along!VBA Send Email Excel Template.xlsm

Table of Contents

VBA Code to Send Emails From Excel

In VBA, to send emails from Excel, we can automatically automate our mailing feature to send emails to multiple users at a time. However, to do so, we need to remember that we may do it by outlook, another product of outlook, so we need to enable outlook scripting in VBA. Once done, we use the .Application method to use outlook features.

VBA's versatility is just amazing. VBA coders love Excel because by using VBA, we not only can work within Excel. Rather, we can also access other Microsoft tools. For example, we can access PowerPoint, Word, and Outlook by using VBA. So I was impressed when I heard of sending emails from Excel. Yes, it is true. We can send emails from excel. This article will show you how to send emails from Excel with attachments using VBA Coding.

Set Reference to Microsoft Office Library

We need to send emails from Outlook. Since Outlook is an external objec, we first need object reference to “Microsoft Outlook 16.0 Object Library.”

1. In VBA, Go to Tools > References.

2. Now, we will see the object reference library. In this window, we need to set the reference to “Microsoft Outlook 16.0 Object Library.”

3. After setting the object reference, click on “OK.”

Now, we can access Outlook objects in VBA coding.

While VBA can help send emails from Excel, there are multiple other functions that you can perform using Excel VBA. To get an idea of how certain events get automated and triggered using the tools, you can have a look at this Excel Events Automation Using Advanced VBA Functions Course.

13 Easy Steps to Send Emails from Excel

Writing the code to send an email with an attachment from Excel is quite complicated but worth spending some time.

Follow the below steps to write your first email excel macro.

Step #1

Start the sub procedure in VBA.

Code:

Sub SendEmail_Example1()

End Sub

Step #2

Declare the variable Outlook.Application

Code:

Dim EmailApp As Outlook.Application 'To refer to outlook application

Step #3

The above variable is an object variable. Therefore, we need to create an instance of a new object separately. Below is the code to create a new instance of the external object.

Code:

Set EmailApp = New Outlook.Application 'To launch outlook application

Step #4

Now, to write the email, we declare one more variable as “Outlook.MailItem”.

Code:

Dim EmailItem As Outlook.MailItem 'To refer new outlook email

Step #5

To launch a new email, we need to set the reference to our previous variable as “CreateItem.”

Code:

Set EmailItem = EmailApp.CreateItem(olMailItem) 'To launch new outlook email

Now, the variable “EmailApp” will launch outlook. In the variable “EmailItem,” we can start writing the email.

Step #6

We need to be aware of our items while writing an email. First, we need to decide to whom we are sending the email. So for this, we need to access the “TO” property.

Step #7

Enter the email ID of the receiver in double quotes.

Code:

EmailItem.To = "Hi@gmail.com"

Step #8

After addressing the main receiver, if you would like to CC anyone in the email, we can use the “CC” property.

Code:

EmailItem.CC = "hello@gmail.com"

Step #9

After the CC, we can set the BCC email ID as well.

Code:

EmailItem.BCC = "hhhh@gmail.com"

Step #10

We need to include the subject of the email we are sending.

Code:

EmailItem.Subject = "Test Email From Excel VBA"

Step #11

We need to write the email body using HTML body type.

Code:

EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _ vbNewLine & vbNewLine & _"Regards," & vbNewLine & _"VBA Coder" 'VbNewLine is the VBA Constant to insert a new line

Step #12

We are working on if we want to add an attachment to the current workbook. Then, we need to use the attachments property. First, declare a variable source as a string.

Code:

Dim Source As String

Then in this variable, write ThisWorkbook.FullName after Email body.

Code:

Source = ThisWorkbook.FullName

In this VBA Code, ThisWorkbook is used for the current workbook and .FullName is used to get the full name of the worksheet.

Then, write the following code to attach the file.

Code:

EmailItem.Attachments.Add Source

Step #13

Finally, we need to send the email to the mentioned email IDs. We can do this by using the “Send” method.

Code:

EmailItem.Send

We have completed the coding part.

Code:

Sub SendEmail_Example1() Dim EmailApp As Outlook.Application Dim Source As String Set EmailApp = New Outlook.Application Dim EmailItem As Outlook.MailItem Set EmailItem = EmailApp.CreateItem(olMailItem) EmailItem.To = "Hi@gmail.com" EmailItem.CC = "hello@gmail.com" EmailItem.BCC = "hhhh@gmail.com" EmailItem.Subject = "Test Email From Excel VBA" EmailItem.HTMLBody = "Hi," & vbNewLine & vbNewLine & "This is my first email from Excel" & _ vbNewLine & vbNewLine & _ "Regards," & vbNewLine & _ "VBA Coder" Source = ThisWorkbook.FullName EmailItem.Attachments.Add Source EmailItem.Send End Sub

Run the above code. It will send the email with the mentioned body with the current workbook as the attachment.

INVESTMENT BANKING RESOURCESLearn the foundation of Investment banking, financial modeling, valuations and more.Join Wallstreetmojo YoutubeFREE EXCEL COURSESLearn MS Excel right from scratch. Master excel formulas, graphs, shortcuts with 3+hrs of Video.Join Wallstreetmojo InstagramFREE FINANCE MODELING IN EXCELLearn Financial Modeling in Excel with this Step by Step Guide (Colgate Case Study)Join Wallstreetmojo LinkedinINVESTMENT BANKING RESOURCESLearn the foundation of Investment banking, financial modeling, valuations and more.Join Wallstreetmojo YoutubeJoin Wallstreetmojo Instagram