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:" & 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: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
Great post. Thank you so much for the info. I’ve been searching the net over for this. Works great for me !!! Thanks again!
November 29th, 2008 at 12:35 amWelcome to the da TaB and thanks so much !!!
November 29th, 2008 at 3:56 amIt clear & working fine. Great!
June 1st, 2009 at 2:26 amThanks Naren and welcome to da TaB !!!
June 3rd, 2009 at 1:05 amOne 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.
June 11th, 2009 at 6:32 pmJohn,
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.
June 11th, 2009 at 11:30 pmWhat if I want to send an e-mail from my gmail account? not from outlook.
July 13th, 2009 at 6:09 pmHi 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.
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
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.
July 14th, 2009 at 1:56 amHI 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
July 20th, 2009 at 5:42 pmDavid,
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:
Let me know if any of these work out for you.
regards,
July 21st, 2009 at 5:34 amAjay
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”. :-//
September 3rd, 2009 at 9:32 amI’m really new in VBA so I don’t really know what else to try.
Thank you.
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:
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
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,
September 3rd, 2009 at 3:00 pmHi 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.
September 4th, 2009 at 2:25 pmhow i can add Attachment with Send Email Using Send Keys code?
November 10th, 2009 at 12:49 pmAdam – 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..
November 10th, 2009 at 1:38 pmRegards,
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
June 25th, 2010 at 7:21 amWhen 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.
October 5th, 2010 at 7:34 pmHi, 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
October 27th, 2010 at 5:54 amIs it possible to contact administration?
December 11th, 2010 at 10:12 pmThanks
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.
April 1st, 2011 at 7:08 amJust looked through the thread. Awesome work.
June 14th, 2011 at 3:04 pmHi Ajay!
Everything works great except for one area. the ‘From’ doesn’t work. Keep getting an error object or method not supported.
July 21st, 2011 at 10:43 am
November 5th, 2011 at 3:17 amCDO
February 27th, 2012 at 11:50 amGreat 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 ?
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,
March 7th, 2012 at 10:29 pmRobert
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?
April 16th, 2012 at 12:36 pmThank you so much for this. Extremely helpful to have the excel doc example with the codes!
July 8th, 2013 at 5:21 amGreat 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
January 13th, 2014 at 10:08 amI 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?
January 16th, 2014 at 4:40 pm