3 Nifty Ways to Send Email Using VBA in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

This article shows three methods using which you can send email using VBA in Excel. You can download a working example of sending email using vba in Excel here.

Excel allows you to create the best-in-class charts, work with large amount of data and data sources work with images, flowcharts and what not. And if that was not enough, you can use the inbuilt vba editor to automate sending emails from excel on your behalf.

(Download example at the end of this post)

Here’s how:

Send Email Using VBA

One of the easiest means to automate sending emails from Excel is to invoke the Create(“ObjectOutlook.Application”) function. This returns the reference to an (activeX) object, in this case the Outlook Application, which is then used further for creating and dispatching an email.

You can copy paste the code below to make this happen:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Send_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "Trying to send email using VBA"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using VBA !!!!"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

As a reminder: When you send email using the vba code mentioned above, a pop-up warning the users that the “A program is trying to automatically send email on your behalf. Do you want to allow this?” appears. Now that’s a valid security warning and there is no direct work around to it. However there are two other means by which this task can be accomplished, one through the use of CDO and the other ones that simulates the use of keyboard key press events.

Send Email Using CDO

What is CDO. According to Technet, CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI). CDO allows you to manipulate Exchange data and to send and receive messages.

The use of CDO may be preferable in cases where you’d want to prevent the security pop-up from appearing with “a program is trying to automatically send email on your behalf” warning and therefore delaying the sending of email till the user provide a response.

In our example, we use the CreateObject(“CDO.Message”) function. An important point to note here is to set the SMTP configuration properly so as to prevent the dreaded “Run-time error
-2147220973(80040213)” or the “sendUsing configuration value is invalid” errors from appearing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub Send_Email_Using_CDO()
Dim CDO_Mail_Object As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Email_Subject = "Trying to send email using CDO"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using CDO !!!!"
Set CDO_Mail_Object = CreateObject("CDO.Message")
On Error GoTo debugs
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
'please put your server name below
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOURSERVERNAME"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
With CDO_Mail_Object
Set .Configuration = CDO_Config
End With
CDO_Mail_Object.Subject = Email_Subject
CDO_Mail_Object.From = Email_Send_From
CDO_Mail_Object.To = Email_Send_To
CDO_Mail_Object.TextBody = Email_Body
CDO_Mail_Object.cc = Email_Cc 'Use if needed
CDO_Mail_Object.BCC = Email_Bcc 'Use if needed
'CDO_Mail_Object.AddAttachment FileToAttach 'Use if needed
CDO_Mail_Object.send
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

Send Email Using Send Keys

Another way to send an email is to use the ShellExecute command to execute any program within vba. The ShellExecute command can be used to load a document with the associated program. In essence, you create a string object and pass that as a parameter to the SheelExecute function. The rest of the work is done by windows. It automatically decides which program is associated with the given document type and uses that to load the document. You can use the ShellExecute function to open explorer, word, paint and a host of other applications. The operation may be slow and you don’t want to send keys even before the email appears on screen so we wait a couple of seconds, make sure that the email request is fully processes and then send the keys. Sending keys before time will prevent the email from being dispatched.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Send_Email_Using_Keys()
Dim Mail_Object As String
Dim Email_Subject, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Email_Subject = "Trying to send email using Keys"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using Keys !!!!"
Mail_Object = "mailto:" &amp; Email_Send_To &amp; "?subject=" &amp; Email_Subject &amp; "&amp;body=" &amp; Email_Body &amp; "&amp;cc=" &amp; Email_Cc &amp; "&amp;bcc=" &amp; Email_Bcc
On Error GoTo debugs
ShellExecute 0&amp;, vbNullString, Mail_Object, vbNullString, vbNullString, vbNormalFocus
Application.Wait (Now + TimeValue("0:00:03"))
Application.SendKeys "%s"
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

(Code courtesy: www.ozgrid.com)

You can download a working example of sending email using vba here

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Saundra Waters wrote:

    Great post. Thank you so much for the info. I’ve been searching the net over for this. Works great for me !!! Thanks again!

  2. Ajay wrote:

    Welcome to the da TaB and thanks so much !!!

  3. Naren wrote:

    It clear & working fine. Great!

  4. Ajay wrote:

    Thanks Naren and welcome to da TaB !!!

  5. John wrote:

    One thing is unclear to me. In the CDO example, it says ‘put your server name here’ but what if I am distributing a worksheet and I don’t know what the server name will be for anyone who is using it? I have tried to use a variation of this and it works fine from my computer, but others have had the ‘sendusing’ error. Thanks.

  6. Ajay wrote:

    John,

    There are no easy ways around this. For me it works because most of the office (or at least in the close circle of people who use this) share the same server.

    One possible (though not very elegant) way is to have a user form take that information from the user at the time of execution. The other obviously is to share the different versions of the workbook with various users.

    I tried to find out the answer for you but there do not seem to be too much info on how one could automate fetching the server name. The best I could find was that you might be able to trap an error if there is a problem with sending the mail thus giving the programmer an indication that the action was unsuccessful.

    However you can try the following resource and see f they are of help:

    1. http://www.ozgrid.com/search/forum.htm?q=mail+server&cx=015666086187122392428%3A-4wevzccjzw&cof=FORID%3A9#1165

    2. http://www.ozgrid.com/search/forum.htm?q=cdo&cx=015666086187122392428%3A-4wevzccjzw&cof=FORID%3A9#1112

    3. http://www.motobit.com/tips/detpg_mailenable-support/

    Let me know if you are successful in your search.

  7. David Palmer wrote:

    What if I want to send an e-mail from my gmail account? not from outlook.

  8. Ajay wrote:

    Hi David,

    Replace the Sub Send_Email_Using_CDO() with the following and then make the changes to the credentials such as the account from which the email is being sent, password etc.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    Sub Send_Email_Using_CDO()
    Dim CDO_Mail_Object As Object
    Dim CDO_Config As Object
    Dim SMTP_Config As Variant
    Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String

    Email_Subject = "Trying to send email using CDO"
    Email_Send_From = "databison@gmail.com"
    Email_Send_To = "databison@gmail.com"
    Email_Cc = "databison@gmail.com"
    Email_Bcc = "databison@gmail.com"
    Email_Body = "Congratulations!!!! You have successfully sent an e-mail using CDO !!!!"

    Set CDO_Mail_Object = CreateObject("CDO.Message")

    On Error GoTo debugs
    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/smtpusessl") = True
        .Item("http://schemas.Microsoft.Com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.Microsoft.Com/cdo/configuration/sendusername") = "databison@gmail.Com"
        .Item("http://schemas.Microsoft.Com/cdo/configuration/sendpassword") = "password"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        'Put your server name below
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.Gmail.Com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
        .Update
    End With
           
    With CDO_Mail_Object
        Set .Configuration = CDO_Config
    End With

    CDO_Mail_Object.Subject = Email_Subject
    CDO_Mail_Object.From = Email_Send_From
    CDO_Mail_Object.To = Email_Send_To
    CDO_Mail_Object.TextBody = Email_Body
    CDO_Mail_Object.cc = Email_Cc                      'Use if needed
    CDO_Mail_Object.BCC = Email_Bcc                    'Use if needed
    'CDO_Mail_Object.AddAttachment FileToAttach        'Use if needed
    CDO_Mail_Object.send

    debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    End Sub

    If port 25 does not work try 587.

  9. David Palmer wrote:

    HI Ajay,

    First of all thank you so much for your help with this project.

    I thought I followed the instructions correctly but I get the following error (regardless of using 25 or 587)

    Error Msg: “The server rejected the sender address. The server response was :530 5.7.0 Must issue a STARTTLS command first. 4sm5021907qwe.27″

    Any thoughts? Did I miss something?

    Thanks again,

    David

  10. Ajay wrote:

    David,

    Here are a few pointers (am trying to cover up my lack of expertise here :-) )

    Configuring other mail clients for STARTTLS (or SSL)
    http://mail.google.com/support/bin/answer.py?hl=en&answer=13287

    If using Outlook for sending message with clients
    http://msdn.microsoft.com/en-us/library/ms873053(EXCHG.65).aspx

    I haven’t had a chance to check this (will try later) but see if turning of SSL helps:

    1
    .Item("http://schemas.Microsoft.Com/cdo/configuration/smtpusessl") = False

    Let me know if any of these work out for you.

    regards,
    Ajay

  11. Jakaloper wrote:

    Hi, thanks for the info, it’s nice to send an email with a text line but what if I want my email to contain a range of cells. I fill up a checklist everyday, and on a second sheet it compile results and display them nicely for the eye. I’d like to send that report by clicking my “send email” button. I tried “Email_Body = Range(“B2:J54″).Value” but it return an error 13 Type Mismatch. When I try “.Select” I receive an email with only “True”. :-//
    I’m really new in VBA so I don’t really know what else to try.
    Thank you.

  12. Ajay wrote:

    Hi Jakaloper,

    There’s no direct way to copy and paste cells directly into the body of an email without losing formatting. One quick and dirty method is given below:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    Sub Send_Email_Using_Keys()
        Dim Mail_Object As String
        Dim Email_Subject, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
        Dim rng As Range
       
    'SET THE RANGE AS PER REQUIREMENT
       Set rng = Range(Cells(1, 1), Cells(10, 1))
        For Each cel In rng.Cells
               Email_Body = Email_Body & " " & cel.Value
        Next cel
       
        Email_Subject = "Trying to send email using Keys"
        Email_Send_To = "databison@gmail.com"
        Email_Cc = "databison@gmail.com"
        Email_Bcc = "databison@gmail.com"
        'Email_Body = "Congratulations!!!! You have successfully sent an e-mail using Keys !!!!"
                 
        Mail_Object = "mailto:" & Email_Send_To & "?subject=" & Email_Subject & "&body=" & Email_Body & "&cc=" & Email_Cc & "&bcc=" & Email_Bcc
       
       
       
        On Error GoTo debugs
        ShellExecute 0&, vbNullString, Mail_Object, vbNullString, vbNullString, vbNormalFocus

        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"

    debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    End Sub

    The other method which is a little more evolved is to save the selected range as an html file, save it to a local drive and then use that file in the body of the email. This should help you.

    Regards,

  13. Jakaloper wrote:

    Hi Ajay,
    thanks for your quick answer. I copy-pasted your code and changed the range and the email addresses. But when I execute it I get an error message with the ShellExecute highlighted. The error message is: Compile Error: Sub or Function not defined. :-(

    Thank you for you time, again.

  14. Adam wrote:

    how i can add Attachment with Send Email Using Send Keys code?

  15. Ajay wrote:

    Adam – I am afraid that shell execute may no tbe able to handle attachments. Essentially it can send only a string. I suggest you look at using CDO to achieve this task..
    Regards,

  16. Mark wrote:

    This is a really simple code, i love it.

    does anyone know how to add a section that attaches one/more than one sheet from the workbook, but it must be able to work along with a Ontime function.

    so, all I want the sheet to do is to Send a sheet from the workbook to a contact at a certain time of the day.

    This is the code from above that i am using:
    ” Sub Send_Email_Using_VBA()
    Dim Email_Subject, Email_Send_From, Email_Send_To, _
    Email_Cc, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant
    Email_Subject = “Trying to send email using VBA”
    Email_Send_From = “databison@gmail.com”
    Email_Send_To = “databison@gmail.com”
    Email_Cc = “databison@gmail.com”
    Email_Bcc = “databison@gmail.com”
    Email_Body = “Congratulations!!!! You have successfully sent an e-mail using VBA !!!!”
    On Error GoTo debugs
    Set Mail_Object = CreateObject(“Outlook.Application”)
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
    .Subject = Email_Subject
    .To = Email_Send_To
    .cc = Email_Cc
    .BCC = Email_Bcc
    .Body = Email_Body
    .send
    End With
    debugs:
    If Err.Description “” Then MsgBox Err.Description
    End Sub”

    10 point to the person who can help me!

    many thanks,

    MarkCBB

  17. Michael Berangel wrote:

    When My PC undergoes hibenate mode i cannot use send key function anymore. Is there any other ways to send Email automatically without using sendkeys? i want to send mails automatically even though im not in the office.

    Somebody help me please.

  18. Rusty wrote:

    Hi, this is fantastic thanks,

    I have this code running on a submit button at the bottom of a form(the VBA code). How can i get the body of the email to contain what was typed into the form?

    Many thanks

  19. Lorencodof wrote:

    Is it possible to contact administration?
    Thanks

  20. Robert Payne wrote:

    Brilliant, thank you for the information on using CDO in Excel, I have several projects stacked up that will be based on this solution, this will save alot of time in our company.

    Cheers.

  21. abockyzok wrote:

    Just looked through the thread. Awesome work.

  22. John R wrote:

    Hi Ajay!

    Everything works great except for one area. the ‘From’ doesn’t work. Keep getting an error object or method not supported.

  23. dermaterializer wrote:

  24. Fin wrote:

    CDO
    Great code examples but I still can’t get a file to attach and send with the CDO example. emails work but attachments cause an error – unsupported protocol ?

  25. Robert wrote:

    This helped SO MUCH. Thanks! MY only question is – in this line:

    Email_Send_To = “databison@gmail.com”

    how do you send to multiple email addresses? I need to send an auto-generated email to 9 people.

    Thanks again,
    Robert

  26. jac wrote:

    Great information. Thank you! One question though. I’ve been unsuccessful in using the CDO example with Win7. Your code and every other example that I can find returns a “Class not Registered” error. Is it possible that Outlook MUST be installed to use CDO but then you have the option of using Outlook or not?

  27. Christine Wood wrote:

    Thank you so much for this. Extremely helpful to have the excel doc example with the codes!

  28. William.Britcher wrote:

    Great solution, I’ve been trying to fix an old vb app for weeks to work with 2010 and this duid the trick. They only part that I’m trying to resolve now is how to add an attachment using the VBA method shown above. Please let me know if you have one. Thank you

  29. Sathish Kumar wrote:

    I have list of emails in excel. I am looking for a code to fetch the one by one emails from the list automatically and send emails. Is it possible?

  30. Harry wrote:

    Is there a way to execute this vba but edit the email first? My aim is to open an email with the presets of the “From” and “BCC” fields to go from and to specified email addresses.

    How can we change the code to let me edit this before it is sent?

  31. Kissore wrote:

    Hi Sir,
    Very well done and explained.
    I am using the following code and get a constant ‘ invalid pointer error’ while i am trying to send email using vba. It happens only at work ( network ) .
    How to solve this error.
    I have tried many forums but there is no answer yet.
    Please help.
    Many thanks
    Kind regards

  32. Sumit Bansal wrote:

    Thanks for sharing.

    Here is another way to send email without using VBA code – http://trumpexcel.com/2014/09/send-email-from-excel-hyperlink/

  1. E-mail met excel - Pagina 2 - Worksheet.nl wrote:

    [...] 3 Nifty Ways to Send Email Using VBA in Excel | Excel & VBA – da Tab Is On voor 3 methodes de sendkeys methode werkt niet met vista en misschien ook niet met 7 gebruik de [...]

  2. Model email notification | IHACtips wrote:

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel