VBA Send Email from Excel

VBA Code to Send Emails From Excel

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

VBA’s versatility is just amazing. The reason why VBA coders love excel because using VBA, we not only can work within excel. Rather we can also access other Microsoft tools as well. We can access PowerPoint, Word, and Outlook by using VBA. The thing which impressed me was when I heard of “sending email from excel itself.” Yes, it is true. We can send emails from excel. In this article, we will show you how to send emails from excel with attachments using VBA CodingUsing VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

VBA Send Email from Excel

Set Reference to Microsoft Office Library

We need to send emails from Outlook. Since Outlook is an outside object first thing we need to do is to set the object reference to “Microsoft Outlook 16.0 Object Library”.

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

You can download this VBA Send Email Excel Template here – VBA Send Email Excel Template

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

Step #1

Start the subprocedure in VBASubprocedure In VBASUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more.

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. We need to create an instance of a new object separately. To create a new instance of the outside object, below is the code.

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

In order 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

Ok, now the variable “EmailApp” will launch the outlook, and in the variable “EmailItem,” we can start writing the email.

Step #6

Now we need to be aware of what are the items we have while writing an email. The first thing is 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

Now 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

Now we need to write the body of the email by 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

If we want to add an attachment as the current workbook, we are working on. Then we need to use 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

Now finally, we need to send the email to the mentioned email IDs. This can be done by using the “Send” method.

Code:

EmailItem.Send
example 1.17

Ok, we are done with 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, and it will send the email with the mentioned body of the email with the current workbook as the attachment.

This has been a guide to VBA Send Email from Excel. Here we learn how to write VBA code to send emails from excel with attachment along with an example and downloadable excel template. You can learn more about VBA from the following articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>