How to Send Emails From Excel Using VBA?

This is the most efficient way to automatically send emails for alerts, notifications, updates, responses, and more. The concept of sending email from Excel using VBA scripts is quite simple. You can send emails from Excel with proper customization including name, address, and attachment.  

Email sending is not a big task, but the big task is to send lots of emails in automation. There are many tools available that help to send lots of emails at once. But the interesting thing is that we all have one default tool Excel on our PC for setting up an email automation. 

So, let’s understand in an easy way

Look Also: Email Deliverability: Everything You Need to Know

Why use Excel for Sending Emails?

Before setting up a VBA script in your spreadsheet. We need to just look out what is the benefit of using Excel for bulk email sending at once. Let’s understand

• If you are in a company with lots of team members, and you want to send an update or alert message about the project completion. It helps to send customized emails as per team members. 
• You can automate repetitive email tasks including sending reports, notifications, and updates to a group of recipients. 
• With this,  You can attach pdf files, images, links, and personalized reports with it. 
• It can improve your work efficiency and reduce the chance of errors. 
• VBA script can also generate random OTP for the login process. 

We can say, that the email sending method works well with all types of emails. But keep in mind it has many limitations like limited customization options, scalability, and compatibility. Higher use of VBA script can cause spam and undeliverability. It is very difficult to maintain a higher amount of data in a spreadsheet. 

But it can help to send automated emails for free. 

What is VBA in Excel?

Visual Basic for Applications or VBA in Excel is a type of event-programming language created by Microsoft. It helps users automate repetitive tasks, and design custom functions within Microsoft Excel. With Excel VBA macros, you can manipulate data, generate reports, integrate another system. and perform complex problems in Excel.

You can also use VBA code with other Microsoft applications including MS Word, MS PowerPoint, MS Outlook, and MS Excel. 

Steps to Send Email From Excel Using VBA Script

You must have the technical knowledge to write a programming language. But this is very easy than others and this programming language is suitable for all types of Microsoft Office tools. 

VBS language is very concise and has English-like commands that make it very easy to learn. This language is only compatible with the Windows Operating system. 

To set up automation in Excel, you must have an Excel file with some sort of data like name, address, and email account. 

So, let’s understand the step-by-step guide. 

Step 1: Open your Developer Tab

You need to make sure your developer tab is “ON” if you want to write code in the VBA editor. 

To access developer mode, go to File -> Options -> Customize Ribbon.

Here, you can see a new dialog box that includes many options with the developer tab. 

Step 2: Open the VBA code editor 

To open the VBA code editor in your Excel file, you can follow this path

Open Excel > Go to Developer section > Click Visual Basics

You can see a code editor like this

Step 3: Activate the Microsoft Outlook Object Library

After opening a VBA editor, you need to activate the Microsoft Outlook object library. 

To access this Go to “Tools” > “References” in the VBA editor.

Under reference, you need to select Microsoft Office 16.0 Object Library, OLE Automation, and Microsoft Outlook 16.0 Object Library, and EASendMailObj ActiveX Object 1.0 Type Library from the reference. 

Note: If you do not have installed EmailArchitect on your PC then first install it form here. 

Step 4: Create a new Module 

Right-click on the “VBAProject” in the Project Explorer on the left side and select “Insert” > “Module” to add a new module.

Now Write the new code in the module that we wrote below. But make sure your SMTP server and email settings are established properly. 

Step 5: Run the code

Run your written VBA code, if you want to test it. Click on the arrow button or press the F5 key. 

If there are any mistakes in your code, Excel will display a pop-up window with an error message. If there is no error then Excel will display another pop-up window of confirmation that the email has been sent.

When you complete your VBA code with satisfaction, you can save your Excel sheet.

Step 6: Make a Send Button

Every time we can’t open a VBA editor to send emails. For that, you need to connect your code to the command button.  

You need to pick a command button from control and ActiveX control. You need to drag it onto the worksheet. 

Now, click right on the button from the mouse and select the “Assign macro” option. Here, you need to select the VBA macro from the dialog box of an assigned macro.

How to set up Email Automation From Excel?

Once you completed all the steps for setting up a VBA macro code. It is good if your code is in a working position. 

Here, you can set a email automation without clicking on a single button. First, copy all the written code from a macro to send emails.

Now, Open the VBA editor and double-click on the workbook. 

In the code window that opens, you can see two dropdown menus at the top of it. You need to select a workbook. Now paste the code between two existing lines. This makes your code run whenever the Excel file is opened.

After that Open the Task Scheduler on your computer. Create a basic task and define its details. 

Finally, specify a macro trigger to automate the email sending at intervals. Set the task to run the Excel file with your code whenever you want the emails to be sent.

Note: Always keep in mind all email sending complies with legal and ethical considerations. Ensure you have proper authorization to send emails to recipients.

How to write code in the VBA editor?

If you do not have knowledge of programming language, you need to have knowledge about a few things to learn a code. 

Sub SendEmail(): Create a subroutine in VBA named “SendEmail.” 

“Sub SendEmail_Demo()

End Sub”

Dim OutlookApp As Object, OutlookMail As Object: Declare an object variable called OutlookApp or Outlookmail to refer to the object. 

Set OutlookApp = CreateObject(“Outlook. Application”): if you want to create a new instance of the Outlook application.

Set OutlookMail = OutlookApp.CreateItem(0): This declares another item within Outlook.

With OutlookMail: 

This sets up a “block” where we can configure the properties of the email.

.To = “[email protected]”:

This sets the email recipient’s address. 

.Subject = “Subject of the email”:

Set email body: Write your email body and change line using vbNewLine.

NewEmailItem.HTMLBody = “Hi,” & vbNewLine & vbNewLine & _

“Write content here” & _

“Regards,” & vbNewLine & _

“Angela”

NewEmailItem.Attachments.Add : Add other file by providing the right path. C:\Users\xxx\Desktop\Formula.pdf” 

OutlookMail.Send: Send the email as per the written code. 

Set OutlookMail = Nothing, Set OutlookApp = Nothing:

These lines “clean up” by releasing the memory used by the Outlook objects we created. 

End Sub:

This marks the end of our subroutine.

I feel this will help. 

Conclusion

Setting up an email automation from MS Excel helps to update users about new things. Users can use Visual Basic for Applications or VBA to automate repetitive tasks. This is a customization tool that is used to design custom code, integrate things, and automate tasks. Using the VBA editor, you can send personalized emails automatically from Excel with attachments. You do not need to write a fully customized email for each person. You just need to follow the above-mentioned steps for creating email automation. 

Mailerday is a Perfect Email Marketing Service provider to grow your business. You can either hire our Email Expert or use our email services including SMTP service, email verification, a bulletproof server, & DMCA-ignored bulletproof service. All services are affordable and have 24/7 support.

FAQS