Categorized | excel download, excel macro and vba, trendspotting

Tags | email, send email using vba, send keys, shellexecute, vba email

3 Nifty Ways to Send Email Using VBA in Excel

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

Prevent Users Ctrl Break (ing) Your VBA Code During Execution

Multithreaded VBA – An Approach To Processing Using VBScript

VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code

Treemap Add-In For Excel – Treemap Software Demo

Treemap in Excel – Coming Soon

VBA Select Case – Using VBA Select Case Statement in Excel

What Do You Think ?

Comments and Trackbacks

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!

November 29th, 2008 at 12:35 am
Ajay wrote:

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

November 29th, 2008 at 3:56 am
Naren wrote:

It clear & working fine. Great!

June 1st, 2009 at 2:26 am
Ajay wrote:

Thanks Naren and welcome to da TaB !!!

June 3rd, 2009 at 1:05 am
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.

June 11th, 2009 at 6:32 pm
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.

June 11th, 2009 at 11:30 pm
David Palmer wrote:

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

July 13th, 2009 at 6:09 pm

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.

July 14th, 2009 at 1:56 am

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

July 20th, 2009 at 5:42 pm
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

July 21st, 2009 at 5:34 am
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.

September 3rd, 2009 at 9:32 am

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,

September 3rd, 2009 at 3:00 pm

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.

September 4th, 2009 at 2:25 pm
Adam wrote:

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

November 10th, 2009 at 12:49 pm
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,

November 10th, 2009 at 1:38 pm
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

June 25th, 2010 at 7:21 am
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.

October 5th, 2010 at 7:34 pm
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

October 27th, 2010 at 5:54 am
Lorencodof wrote:

Is it possible to contact administration?
Thanks

December 11th, 2010 at 10:12 pm
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.

April 1st, 2011 at 7:08 am
abockyzok wrote:

Just looked through the thread. Awesome work.

June 14th, 2011 at 3:04 pm
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.

July 21st, 2011 at 10:43 am

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 […]

June 17th, 2011 at 2:20 pm

Translate

LATEST POSTS