How to Send Emails From an Excel Spreadsheet Using VBA Scripts

Why Send Email From Excel?

There are a lot of reasons why you might want to send an email from inside Microsoft Excel.

Maybe you have staff that updates documents or spreadsheets on a weekly basis, and you’d like to receive an email notification of when those updates are done. Or you might have a spreadsheet of contacts and you want to send one email to all of them at once.

You’re probably thinking that scripting an email broadcast from Excel is going to be complicated. That’s not the case at all.

The technique in this article will make use of a feature that’s been available in Excel VBA for a long time, Collaboration Data Objects (CDO).

CDO is a messaging component used in Windows since very early generations of the OS. It used to be called CDONTS, and then with the advent of Windows 2000 and XP, was replaced with “CDO for Windows 2000”. This component is already included in your VBA installation within Microsoft Word or Excel and is ready for use.

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

Step 1: Create a VBA Macro

The first step is to go to the Excel Developer tab.

Inside the Developer tab, click on Insert in the Controls box, and then select a command button.

Draw it into the sheet and then create a new macro for it by clicking on Macros in the Developer ribbon.

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

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

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

When you click OK, make note of the name of the function where you’re pasting the script. You’ll need it later.

Step 2: Set Up the CDO “From” and “To” Fields

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

Keep in mind that while many of the fields are optional, the From and To fields are required.

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 cool thing about this is that you can create up any string you want to customize a full email message and assign it to the strBody variable.

Piece together components of the message by using the & string to insert data from any of the Microsoft Excel sheets right into the email message, just like shown above.

Step 3: Configure CDO to Use an External SMTP

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

This example is a non-SSL setup through Gmail. CDO is capable of SSL, but that’s outside the scope of this article. If you need to use SSL, this advanced code in Github can help.

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: Finalize the CDO Setup

Now that you’ve configured the connection to the SMTP server for sending the email, all you have to do is fill in the appropriate fields for the CDO_Mail object, and issue the Sendcommand.

Here is how you do that:

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 won’t be any pop-up boxes or security alert messages, which can happen when you resort to using the Outlook mail object.

CDO simply puts together the email and utilizes your SMTP server connection details to fire off the message. It’s the easiest way to incorporate email into Microsoft Word or Excel VBA scripts.

To connect your command button to this script, go into the code editor and click onSheet1 to view the VBA code for that worksheet.

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

Here’s what the message looked like that I received in my inbox:

Note: If you receive an error that reads The transport failed to connect to the server, make sure you’ve entered the correct username, password, SMTP server, and port number in the lines of code listed underneath With SMTP_Config.