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

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

• In VBA Go to Tools > References

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

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

Follow the below steps to write your first email macro.

Step #1

Start the subprocedure 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. 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

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

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

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 for whom we are sending the email. So for this, we need to access “TO” property.

Step #7

Enter the email Id of the receiver in double-quotes.

Code:

EmailItem.To = "Hi@gmail.com"

Step #8

After addressing to 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 BCC email id as well.

Code:

EmailItem.BCC = "hhhh@gmail.com"

Step #10

Now we need to include Subject of the email we are sending.

Code:

EmailItem.Subject = "Test Email From Excel VBA"

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

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

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

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

Code:

EmailItem.Send

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.

Recommended Articles

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

• Excel VBA ChDir Function
• Examples of VBA Wait
• VBA TimeValue Function
• VBA DatePart
• How to Extract Number from String Excel?
• Create Charts in VBA
• Dictionary in VBA
• Create Progress Bar in VBA
• GetOpenFilename VBA