How to send email from Excel spreadsheet with VBA script

Sending emails from Microsoft Excel requires only a few simple scripts. Add this function to a spreadsheet and you can really make the most of all features in Excel.

Quantrimang has presented many great Excel macros, can do things like VBA scripts, but don't need programming knowledge. Besides, there are more advanced things you can only do with VBA, like creating a report in the spreadsheet about all the information on your computer.

Why send email from Excel?

There are many reasons why you might want to send emails from within Microsoft Excel.

You may have staff update your documents or spreadsheets weekly and you want to receive email notifications when those updates are made. Or you can have a spreadsheet containing contact email addresses and you want to send an email to all those addresses at once.

You may be thinking that using Excel scripts will be complicated. But rest assured! Doing that is extremely simple.

This article will show you how to use a feature available in Excel VBA for a long time. That is Collaboration Data Objects (CDO) .

How to send email from Excel spreadsheet with VBA script Picture 1

CDO is a component of the email process, used from the first Windows operating systems. Previously, it was called CDONTS, and then with the advent of Windows 2000 and XP, this name was replaced by " CDO for Windows 2000 " (CDO for Windows 2000). This component has been included in the installation of VBA in Microsoft Word or Excel applications and is ready for use.

Using components makes sending emails from within Windows products extremely easy with VBA. In this example, you will use the CDO component in Excel to send emails that contain results from a specific Excel cell.

How to send email from Excel spreadsheet with VBA script

Step 1: Create VBA Macro

The first step is to enter the Excel Developer tab .

Inside the Developer tab , click Insert in the Controls box , then select the appropriate Command Button .

How to send email from Excel spreadsheet with VBA script Picture 2

Draw it on the worksheet and then create a new macro for the worksheet by clicking on the Macro in the ribbon Developer.

How to send email from Excel spreadsheet with VBA script Picture 3

When you click the Create button , it will open the VBA editor.

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

How to send email from Excel spreadsheet with VBA script Picture 4

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

How to send email from Excel spreadsheet with VBA script Picture 5

When you click OK, keep in mind the name of the function you are pasting the script. You will need it later.

Step 2: Set the 'From' and 'To' fields of CDO

To do this, you first need to create objects in the mail and set up all necessary fields to send the email.

Keep in mind that although many fields are optional, you must enter the From and To fields .

 Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String strSubject = "Results from Excel Spreadsheet" strFrom = "rdube02@gmail.com" strTo = "rdube02@gmail.com" strCc = "" strBcc = "" strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1)) 

The most interesting thing is that you can create any string you want to customize a full email and assign it to the strBody variable .

Merge the components of the email together, using the & string to insert data from any Microsoft Excel worksheet right into the email message, as shown above.

Step 3: Configure CDO to use external SMTP

The next section is where you will configure CDO to use any external SMTP server for the purpose of sending emails.

This example is a non-SSL setting through Gmail. CDO has SSL capability, but is outside the scope of this article. If you need to use SSL, this advanced code in Github may be useful for you.

 Set CDO_Mail = CreateObject("CDO.Message") On Error GoTo Error_Handling Set CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Set SMTP_Config = CDO_Config.Fields With SMTP_Config .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True .Update End With With CDO_Mail Set .Configuration = CDO_Config End With 

Step 4: Complete setting up CDO

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

Here's how you do it:

 CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.Description <> "" Then MsgBox Err.Description 

There will not be any security warnings. This can happen when you use the Outlook mail object.

CDO just needs to put all the components of the email together and use the SMTP server connection details to enable mail delivery. It's the easiest way to combine email into Microsoft Word or Excel VBA scripts.

To connect the Command Button to this script, go to the code editor and click Sheet1 to see the VBA code for that sheet.

Enter the name of the function that you pasted the script above.

How to send email from Excel spreadsheet with VBA script Picture 6

This is the mail you will receive in your inbox:

How to send email from Excel spreadsheet with VBA script Picture 7

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 lines listed below With SMTP_Config.

Automate the whole process

All goes well and you can send emails from Excel with just a click of a button. However, you may want to use this function on a regular basis, so automating the process will be extremely important.

To do so, you need to make changes to the macro. Go to the Visual Basic Editor , then copy and paste the entire code you have included.

How to send email from Excel spreadsheet with VBA script Picture 8

Next, select ThisWorkbook from the Project hierarchy .

From the two dropdown fields at the top of the code window, select Workbook and Open from the dropdown Methods menu .

Paste the email script above into Private Sub Workbook_Open () .

This 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 a spreadsheet according to the preset intervals. At that point your macro will be activated and emailed.

How to send email from Excel spreadsheet with VBA script Picture 9

Select Create Basic Task . from the Action menu and work your way through the wizard until you reach the Action screen .

Select Start a program and click Next.

How to send email from Excel spreadsheet with VBA script Picture 10

Use the Browse button to find the location of Microsoft Excel on your computer or copy and paste the path into the Program / script field.

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

Complete your process and schedule will be done.

You should run the test by scheduling the action for a few minutes, then modifying the time period, when you confirm that it is working.

Note : You may have to adjust the Trust Center settings to make sure that the macro runs correctly.

To do so, open the spreadsheet and navigate to File> Options> Trust Center .

From here, click Trust Center Settings and on the next screen, select Never show information about blocked content .

Microsoft Excel is an extremely powerful tool, but learning how to make the most of it can be a challenge. If you really want to own the software, you will need to be comfortable with VBA and that is not an easy task.

However, with a little experience with VBA, you will soon be able to program Microsoft Excel to perform basic tasks automatically, giving you more time to focus on more important issues.

It takes time to accumulate professional knowledge about VBA, but you will soon see results if you are serious.

Good luck!

See more:

1. Create your own Internet "browser" with VBA
2. How to break VBA password in Excel
3. OpenOffice.org Calc adds Excel VBA support