Instructions for sending emails from Excel spreadsheets using VBA scripts

Using VBA scripts in Excel does not require any programming knowledge. VBA helps you do many advanced tasks such as creating a spreadsheet report containing all PC information, emailing directly from a spreadsheet ...

To send email from Microsoft Excel just need a few simple scripts. Do not be afraid to add this feature to your spreadsheet if you want to really get the most out of Excel office software .

Email from Excel

There are many reasons why you want to send email right at Microsoft Excel. For example: Your employees update documents and spreadsheets weekly and you want to receive email notifications when that action is complete or sometimes, you want to email all contacts in a same workbook. at…

You must have thought of using a complicated Excel emailing script, but it's actually very simple. This article will guide you to take advantage of a feature that has long been available in Excel VBA, called Collaboration Data Objects (CDO) .

CDO is a messaging component used in Windows right from the early versions. It used to be called CDONTS. After Windows 2000 & XP was born, "CDO for Windows 2000" replaced it. This component is included in the VBA installation in Microsoft Word or Excel and is available for use at any time.

Using this component to email in Windows products with VBA is extremely easy. For example, this article uses CDO in Excel to send emails that contain results in one cell.

Step 1: Create a VBA Macro

First, go to Excel Developer tab > click Insert in Controls panel , then select a command button.

Drag it into this worksheet, then create a new macro by clicking Macros in the Developer ribbon.

When you click the Create button , it opens the VBA editor.

Add a reference to the CDO library by navigating to Tools> References in the editor.

Scroll down the list until you see Microsoft CDO for Windows 2000 Library . Check the checkbox and click OK .

When you click OK , note the name of the function where the script is pasted, because you will need it later.

Step 2: Set the CDO fields "From" and "To"

To do this, you first need to create email-related objects and set up all the required email delivery fields.

Remember that many fields may or may not, but From and To are required.

The interesting thing is that you can create any desired string to customize the entire email message and assign it the strBody variable.

Piecing together the contents of an email message using a string & to insert data from any Microsoft Excel spreadsheet right in an email (as shown above).

Step 3: Configure CDO to use external SMTP

The next piece of code is where you configure CDO to use any external SMTP server to send email.

This example does not set up SSL via Gmail, CDO can replace SSL but it is outside the scope of this article. If you need to use SSL, the advanced code in Github can help you.

Step 4: Complete CDO setup

You have now configured your SMTP server to send email. All you have to do is fill in the appropriate fields for the CDO_Mail object and issue the Send command .

Here's how to do it:

None of the pop-ups or security alerts appear as you usually see when using the Outlook email object.

CDO simply puts email components side by side and uses server connection details to enable sending information. This is the easiest way to combine emails into Microsoft Word or Excel VBA scripts.

To connect the command button to this script, access the code editor and click Sheet1 to see the VBA code of that worksheet.

Type the name of the function where you pasted the script above.

This is the information you receive in the inbox.

Note : If you receive the error The transport failed to connect to the server , make sure you have entered the correct username, password, SMTP server and port number in the code line listed at With SMTP_Config .

Enhance and automate the whole process

Sending emails from Excel with one button is really safe and convenient. If you want to use this feature often, you can automate it.

To do this, you need to change the macro. Go to the Visual Basic Editor, copy and paste the entire code gathered. Next, select ThisWorkbook from the Project hierarchy .

From the two drop-down fields at the top of the code window, select Workbook and click Open from the Methods menu . Paste the email script above into Private Sub Workbook_Open () . This action will run the macro whenever you open the Excel file.

Next, open Task Scheduler . You will use this tool to ask Windows to automatically open spreadsheets at specified intervals. At that time, your macro will be activated and emailed.

Select Create Basic Task ... from the Action menu and follow the instructions until you reach the Action screen.

Select Start a program and click Next .

Use the Browse button to find the Microsoft Excel location on the computer or copy & paste the path in the Program / script field .

Then enter the path to the Microsoft Excel document in the Add arguments field .

Complete instructions and schedule you will be made.

You should test this feature by scheduling the action in the next few minutes, then revising the task after you see it working effectively.

Note : You may need to adjust the Trust Center settings to make sure that macro runs as follows:

Open the spreadsheet and navigate to File> Options> Trust Center .

Here, click Trust Center Settings . On the next screen, select Never show information about blocked content .

Microsoft Excel is an extremely powerful tool, but learning how to use it can sometimes be challenging for many. If you want to master this software, you need to know how to use VBA. It is not easy.

However, with a little VBA experience, you can automate basic tasks on Microsoft Excel and have more time focusing on more important tasks.

Hope the article is helpful to you. Good luck!