How to Send Emails From an Excel Spreadsheet Using VBA Scripts
Whatsapp Pinterest
Advertisement

To send emails from Microsoft Excel only requires a few simple scripts. Add this functionality to your spreadsheets and you can really enhance how much you can accomplish in Excel.

Unlock the "Essential Microsoft Office Shortcuts" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

We’ve covered a lot of great Excel macros, which can accomplish the same things VBA scripts can, but without the need for programming knowledge. But there are many advanced things you can only do with VBA, like creating a spreadsheet report with all of your PC information.

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 in excel

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.

adding button in excel

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

add macro for command button

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.

adding references in excel

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

adding cdo for windows reference

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

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 on Sheet1 to view the VBA code for that worksheet.

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

connect command button to script

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

excel email received

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.

Take It Further and Automate the Whole Process

It’s all well and good to be able to send email from Excel 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 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More .

To do so, you’ll need to make a change to the macro. Head to the Visual Basic Editor and copy and paste the entirety of the code we put together.

Next, select ThisWorkbook from the Project hierarchy.

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

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

This will run the macro whenever you open up the Excel file.

Paste the email script

Next, open up Task Scheduler.

You’re going to use this tool to ask Windows to open up the spreadsheet automatically at regular intervals, at which point your macro will be initiated, sending the email.

create basic task in task scheduler

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.

select program task scheduler

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

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

Complete the wizard, and your scheduling will be in place.

It’s worth running a test by scheduling the action How to Auto-Empty the Recycle Bin on a Schedule and Free Up Wasted Space How to Auto-Empty the Recycle Bin on a Schedule and Free Up Wasted Space If you don't regularly empty the Recycle Bin, it could be wasting gigabytes of space on your data drive. But now Windows 10 can empty it automatically on a schedule. Read More for a couple of minutes in the future, then amending the task once you can confirm that it’s working.

Note: 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 Microsoft Excel Work for You

Microsoft 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 Microsoft Excel perform basic tasks automatically, giving you more time to concentrate on more pressing matters.

It takes time to build expertise with VBA, but you’ll soon see the fruits of your labors if you can stick with it.

One great place to start is our authoritative tutorial on using VBA in Excel A Beginner's Tutorial on Writing VBA Macros in Excel (And Why You Should Learn) A Beginner's Tutorial on Writing VBA Macros in Excel (And Why You Should Learn) If you use Excel regularly, it's worth learning how to create VBA macros and get access to many more functions and capabilities. Read More . Once you’re done with that, this simple script to send emails from Excel will feel like child’s play.

Explore more about: Email Tips, Microsoft Excel, Microsoft Office Tips, Programming, Visual Basic Programming.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Chuck&MikeD
    February 26, 2017 at 11:59 pm

    I get an error "The transport failed to connect to the server"

  2. Sheldon Aden
    December 29, 2016 at 10:37 am

    Hi there,
    I need help.
    I did copy and paste all of the data that was provided. However I am still getting a compile error message. I have used the **** to indicate which line of code is the problem.

    My spreadsheet basically has three columns:
    Email address, Assessment due date, Staff name. I just need to know how I can get the email to auto send say two days before the due date.

    Private Sub CommandButton1_Click()

    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 = "ORE reminder from Excel Spreadsheet"
    strFrom = "saden4@live.com"
    strTo = "sheldon.aden@live.com"
    strCc = ""
    strBcc = ""
    strBody = "ORE reminder" & Str(Sheet1.Cells(2, 1)) - error message*************

    Set CDO_Mail = CreateObject("CDO.Message")

    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

    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

    End Sub

    Many Thanks and hope to hear from you soon

    • Intsar
      February 9, 2017 at 6:11 am

      hi,
      if the content of Sheet1.Cells(2, 1) is a string no need for the function Str() and yo can remove it from the code
      in this case, change the line with error as follows:

      strBody = "ORE reminder" & Sheet1.Cells(2, 1)

  3. Cary
    December 12, 2016 at 3:12 am

    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.

  4. seshan
    October 20, 2016 at 12:42 pm

    how we can do that with gmail can you help me with that

  5. john
    October 6, 2016 at 6:51 pm

    How would I use this to send a message with an attachment?

    Thanks!
    John

    • sam
      April 12, 2017 at 7:01 pm

      Have youfigured this out? I am curious.

  6. rajat
    September 19, 2016 at 5:32 am

    Error
    "The transport failed to connect to server."

    • Sakshi
      June 20, 2017 at 9:52 am

      hi rajat,

      What is the solution if we are getting this error?

      Regards,
      Sakshi

  7. Happy Coder
    September 16, 2016 at 2:20 pm

    Thanks Ryan! Worked like a charm. (All I had to do was fill the blanks and swap the dash for double quotes in the error check. Why is there a dash there?)

  8. Iulian
    June 24, 2016 at 6:44 am

    Excellent work Ryan!

    What about if my sheet includes an object that I need to send it as it is?

  9. Anonymous
    June 9, 2015 at 9:59 am

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

    Thank you

    • Roman
      November 13, 2016 at 1:22 pm

      You should use loop statement

  10. Sgsh
    May 18, 2015 at 7:36 am

    Yes. Like an if statement of sorts. Also is it possible to use this VBA in access ?

  11. Sgsh
    May 17, 2015 at 1:22 pm

    Is it possible to insert a condition for the email to be sent. For example if the cell has a boolean yes then the email must be sent ?

    • Ryan Dube
      May 17, 2015 at 6:47 pm

      Do you mean having the code check that field before sending, and not sending if there's a "No" in the field?

  12. Raj
    May 7, 2015 at 4:26 pm

    thanks a lot for this wonderful writeup. It helped me a lot. Please keep the good work.

  13. Raj
    May 7, 2015 at 4:21 pm

    thanks a lot for this wonderful writeup. It helped me a lot. Please keep the good work.

  14. Matt
    January 22, 2015 at 3:49 pm

    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?

  15. Matt
    January 22, 2015 at 3:48 pm

    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?

  16. Marty
    January 14, 2015 at 3:43 pm

    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.

    Below is my code.

    Sub Button1_Click()
    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 = "XXX@comcast.net"
    strTo = "XXX@comcast.net"
    strCc = ""
    strBcc = ""
    strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))

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

    With CDO_Mail
    Set .Configuration = CDO_Config
    End With

    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

    End Sub

  17. kenny
    December 23, 2014 at 1:24 pm

    I would like to know how to have it use an email address from a field for the TO address, i.e. email address is in cell B8

    • Angela
      February 8, 2017 at 1:54 pm

      Replace your .To string with below, obviously replacing "Nameofsheet" with the actual name of your sheet i.e. Sheet1, etc.

      .To = Sheets ("NAMEOFSHEET") .Range("B8")

  18. KJN
    December 13, 2014 at 4:34 pm

    What would be ther proper code to add a new line within the body of the email?

    • Simon
      January 8, 2015 at 8:28 am

      ...which is the end of my text sentence." & Chr(13) & "This is a new paragraph, and...

      I think that'll work? Or

      ...which is the end of my text sentence." & Chr(13) & Chr(13) & "This is a new paragraph, and...

      if you want a full blank line rather than just starting on a new line with no space in between.

  19. JLee
    July 3, 2012 at 2:27 pm

    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
      July 11, 2012 at 1:26 am

      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.

  20. Joe Bloggs
    June 12, 2012 at 12:33 am

    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
      June 12, 2012 at 2:08 am

      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
        July 9, 2012 at 9:55 am

        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 :)

  21. Alanjudson
    February 9, 2012 at 5:33 pm

    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
      February 9, 2012 at 7:57 pm

      MsgBox(Err.Description)

      • Ryan Dube
        February 10, 2012 at 1:00 am

        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
          February 10, 2012 at 5:34 pm

          Thank you Ryan, great articles.

  22. Alanjudson
    February 9, 2012 at 2:29 pm

    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
      February 9, 2012 at 4:42 pm

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

      • Ryan Dube
        February 10, 2012 at 1:01 am

        Yes it shouldn't be "Str", it should be "Cstr"....thanks so much for helping Delarge.

    • David Black
      July 11, 2012 at 1:33 am

      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.