Updated by Brad Jones on 4 July 2017.
Using emails as part of any program is a nice way to automate important tasks, and it also significantly improves the value and functionality of any program or script.
These are great for those times when you have a script that’s monitoring the health of a computer or the status of a specific process, but what if you want to automate sending emails from within Office products like Word or Excel?
There are a lot of reasons why you might want to do so. Maybe you have staff that update documents or spreadsheets on a weekly basis, and you’d like to receive an email notification of when those updates take place, and even a report of the data from within those sheets. There are a few techniques you can use to program automated emails from within Excel, but Collaboration Data Objects (CDO) remains my favorite.
Sending Emails From Within Excel
You’re probably thinking that scripting outgoing email into an Excel VBA script is going to be painfully complicated. Well, that’s not the case at all.
CDO is a messaging component used in Windows for a few generations of the OS. It used to be called CDONTS, and then with the advent of Windows 2000 and XP, it was replaced with “CDO for Windows 2000”. This component is already included in your VBA installation within Word or Excel and it’s ready for use.
Using the component makes sending emails from within Windows products with VBA extremely easy. In this example, I’m going to use the CDO component in Excel to send out an email that will deliver the results from a specific Excel cell.
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.
When Excel opens up the VBA editor, you’re going to need to add the reference to the CDO library. Navigate 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.
Set Up the CDO From and To Fields
Now you’re ready to use CDO to issue emails from inside Excel. 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 = "email@example.com" strTo = "firstname.lastname@example.org" strCc = "" strBcc = "" strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))
The cool thing about this is that you can build 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 Excel sheets right into the email message, just like I’ve shown above.
Configure CDO to Use an External SMTP
The next section of code is where you will configure CDO to use any external SMTP server that you want to use. In this case, I don’t need to use SSL because my SMTP server doesn’t require it. CDO is capable of SSL, but that’s outside the scope of this article. If you need to use SSL, I highly recommend Paul Sadowski’s awesome writeup on using CDO.
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@example.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
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 Send command. 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
So there you have it. 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 probably the easiest way I know to incorporate email into Word or Excel VBA scripts.
Here’s what the message looked like that I received in my inbox:
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.
Automating the Process
It’s all well and good to be able to send information from Excel as an email at the touch of a button. However, you might want to use this functionality on a regular basis, in which case it would make sense to automate the process.
To do so, we’ll need to make a change to the macro we created. Head to the Visual Basic Editor and copy and past the entirety of the code we put together. Next, select ThisWorkbook from the Project hierarchy.
Copy and paste your code into ThisWorkbook. Then, replace the first line with Sub Workbook_Open(). This will run the macro whenever you open up the file.
Next, open up Task Scheduler. We’re going to use this tool to ask Windows to open up the spreadsheet automatically at regular intervals, at which point our macro will be initiated, sending the email.
Select Create Basic Task… from the Actions menu and work your way through the wizard until you reach the Action screen. Select Start a program and click Next.
Use the Browse button to find Excel’s location on your computer, or copy and paste the path into the Program/script field. Then, enter the path to your Excel document into the Add arguments field. Complete the wizard, and our scheduling should be in place. It’s worth running a test by scheduling the action for a couple of minutes in the future, then amending the task once you can confirm that it’s working.
You may have to adjust your Trust Center settings to ensure that the macro runs properly. To do so, open the spreadsheet and navigate to File > Options > Trust Center. From here, click Trust Center Settings, and on the next screen set the radio dial to Never show information about blocked content.
Make Excel Work for You
Excel is an incredibly powerful tool, but learning how to get the most out of it can be a little intimidating. If you want to truly master the software, you’ll need to be comfortable with VBA, and that’s no small task.
However, the results speak for themselves. With a little VBA experience under your belt, you’ll soon be able to make Excel perform basic tasks without your supervision, giving you more time to concentrate on more pressing matters. It takes time to get to grips with VBA, but you’ll soon see the fruits of your labors if you can stick with it.
Can you think up any cool uses for CDO in your own Excel, Access, or Word projects? Share your thoughts and ideas in the comments section below.