How To Send Emails From An Excel Spreadsheet Using VBA Scripts

emaillaptop   How To Send Emails From An Excel Spreadsheet Using VBA ScriptsUsing 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.

In the past, I’ve used email a whole lot in my batch jobs and other automated scripts, just like I’ve described in past articles here on using tools like Sendmail or Blat to issue emails straight from the command line, or from within a command line 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 my favorite remains CDO.

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.

The first step is to go to the “Developer” menu 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.

outlookvba1   How To Send Emails From An Excel Spreadsheet Using VBA Scripts

When Excel opens up the VBA editor, you’re going to need to add the reference to the CDO library. You can access this in the Tools menu, and then scroll down the list until you find “Microsoft CDO for Windows 2000 Library“. Select the checkbox and click OK.

outlookvba2   How To Send Emails From An Excel Spreadsheet Using VBA Scripts

Now you’re ready to use CDO to issue emails from inside Excel. To do this, you first need ot create the mail objects and set up all of the fields that will be required for sending 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 = "ryxxxxxx@xxxxxcast.net"
strTo = "rdxxxxxx@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 an 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.

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.metrocast.net"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Update
End With

With CDO_Mail
    Set .Configuration = CDO_Config
End With

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

outlookvba4   How To Send Emails From An Excel Spreadsheet Using VBA Scripts

No hassle – just the data straight from within the Excel sheet delivered right to my email account. If you’re creative with how you put together the body string variable with all sorts of data from your Excel sheet, you can just imagine the cool automated email reports that you could put together. And if you don’t want to use a command button, just have the script run on the sheet or application close event.

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.

Shutterstock

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

13 Comments -

Alanjudson

For us newbs:  I get a compile error that says “Syntax Error” for this line:
strBody = “The total results for this quarter are: ” & Str(Sheet1.Cells(2, 1))
 
Also – this line is highlighted in red
 If Err.Description <> “” Then MsgBox Err.Description

Any thoughts?

Anonymous

Try  Cstr(Sheet1.Cells(2, 1))

Ryan Dube

Yes it shouldn’t be “Str”, it should be “Cstr”….thanks so much for helping Delarge.

David Black

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.

Alanjudson

Thanks Delarge, that worked. But I still get a red line error
It says “Compile Error: expects Go To or Then”

If Err.Description <> “” Then MsgBox Err.Description

Anonymous

MsgBox(Err.Description)

Ryan Dube

Delarge got it right – it looks like my code got jumbled in translation, I guess the “pre” tags don’t work quite as well as advertised.

Anonymous

Thank you Ryan, great articles.

Joe Bloggs

1. A working example would be nice.

2. For a learner this is really bad step-by-step quide:

Quote “first need ot create the mail objects and set up all of the fields that will be required for sending the email.”

Where? do i create a module or do i add the source to a sheet object? you need to be more spacific in your detail.

Ryan Dube

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.

Make sense?

Aren

i pasted all the code into the Macro editor and customized it … but when i run macro, it gives an error message saying Object Required.
i really can’t get what that is ..
Help PLEASE :)

JLee

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)

David Black

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

Hope that helps.