VBA Send Email from Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents
VBA Send Email from Excel

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. 1.     In VBA, Go to Tools > References.


    vba send email references1

  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."


    vba send email references1

  3. After setting the object reference, click on "OK."

Now, we can access Outlook objects in VBA coding.

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
vba send email example 1.1

Step #2

Declare the variable Outlook.Application

Code:

Dim EmailApp As Outlook.Application 'To refer to outlook application
vba send email example 1.2

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
vba send email example 1.3

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
vba send email example 1.4

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
vba send email example 1.5

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.

vba send email example 1.6

Step #7

Enter the email ID of the receiver in double quotes.

Code:

EmailItem.To = "Hi@gmail.com"
vba send email example 1.7

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"
vba send email example 1.8

Step #9

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

Code:

EmailItem.BCC = "hhhh@gmail.com"
vba send email example 1.9

Step #10

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

Code:

EmailItem.Subject = "Test Email From Excel VBA"
example 1.10

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
example 1.11

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
example 1.14

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

Code:

Source = ThisWorkbook.FullName
example 1.15

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
example 1.16

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
example 1.17

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.