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 Microsoft 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 Microsoft 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 Microsoft 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 Microsoft Excel to send out an email that will deliver the results from a specific Excel cell.

Create a VBA Macro

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 Microsoft 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 Microsoft 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.

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 Microsoft 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.

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:

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 Microsoft Word or Excel VBA scripts.

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

Troubleshooting

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.

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.

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.

However, the results speak for themselves. With a little VBA experience under your belt, you’ll soon be able to make Microsoft 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 Microsoft Excel, Access, or Word projects? Share your thoughts and ideas in the comments section below.

essay Grammar and punctuation aren't important on a multipleoption.
d) Intended contributions to the community should
be well featured. Hospitality Management course further includes subjects like restaurant management, lodging
operations, global tourism, attractions management, event management and food preparations.

Dear Ryan,
Is it possible to have a column with mails and a column with data and automate it in a way where it sends each row of mails and results individually?
An example just in case I didn't make myself clear:
_____A_______B____
1 - a@.com --- 9
2 - b@.com --- 6
3 - c@.com --- 7,4

Now I want to send a mail to a@.com telling him that the result is 9.
Another mail to b@.com telling him that the result is 6 ...

This was great help! The only thing extra I need is to attatch a word document or pdf to the automated email. Is there a command that goes with the others used to set destinations, subject, and body that can attacth a document that is saved on the computer to the email? If not do you have any ideas how this could be done?

This was great help! The only thing extra I need is to attatch a word document or pdf to the automated email. Is there a command that goes with the others used to set destinations, subject, and body that can attacth a document that is saved on the computer to the email? If not do you have any ideas how this could be done?

I think I have the code and setting correct but still get the error message...."The transport failed to connect to the server."
I am using outlook 2007, I changed the to: and from to my address and my wife's and I changed the SMPT smpt.comcast.net
I then put the value of 50 in cell A2
I also tried changing the server port to 587. That was worst
Also, I would like to send the contents of cells A1 : K30
This would REALLY help me if it worked.

Writing this program, it seems excel does not like the semicolons. I don't now much of anything about VB. >>strBody = "The total results for this quarter are: " & Cstr(Sheet1.Cells(4, 4)) is in red and the message is compile error: syntax error. Same issue seems to repeat with >>If Err.Description <> "" Then MsgBox(Err.Description)

Could the problem be that it has been copy/pasted into a Word environment, along the way, and Word had "smart quotes" on. Word has changed the ascii double-quotation character Ascii(34) character to the prettier - but pretty useless - open and close quote charcaters Ascii(147) and Ascii(148). VBA won't accept these.
If the line of code now reads:-
If Err.Description “” Then MsgBox Err.Description
Try changing it (in the VBA Editor) to:-
If Err.Description "" Then MsgBox Err.Description

Joe - it's all in the Read_Emails macro that I mentioned before laying out the code. Paste all of the code that I gave you in the Macro script editor and you're done. All I meant by creating the objects and setting up the fields is that is what the code I gave you does. All you have to do is customize that code with fields that describe your email settings. Everything in the article is done for you, you just cut & paste, and then insert your own subject, From, and To fields.

Looks like all quotation marks in this line have been converted to Smart quotes. I suggest you go through the code In the VBA editor, delete any smart quotes (they may appear as left and/or right sloping quote marks), replace each one with the plain double qutotation mark.

Ryan has a BSc degree in Electrical Engineering. He's worked 13 years in automation engineering, 5 years in IT, and now is an Apps Engineer. A former Managing Editor of MakeUseOf, he's spoken at national conferences on Data Visualization and has been featured on national TV and radio.