Sending e-mails from Gmail and Outlook using Excel VBA

Last updated on May 29, 2018

Today we have a guest article from Perceptive Analytics. It explains how to send emails from Gmail and Outlook using Excel VBA.

For instance, you might want to set up an automated email system to send weekly email reports to colleagues – without having the manually specify To / Subject / CC / Email body / Attachments.

The VBA code and instructions (below) allow you to do just that…

Just adapt the relevant Macro and run it to generate your emails.

Click here for the Gmail instructions.
Click here for the Outlook instructions.
Click here for instructions on how to access email parameters stored on your Excel sheet.

GDPR notice: Given the new EU legislation around Data Protection and Privacy (that came into force worldwide on 25 May 2018)… it makes sense to respect and protect email addresses, especially when using any automation. Please do not abuse the code here to send spam emails!

Warning: While I provide this article for general education and information, I cannot be held accountable for any negative effects from using the VBA code below. Exercise reasonable caution when following these instructions, and use at your own risk.

Download the Sample Workbook

To follow along with article you can download the Sample Workbook “20180529-Send_Email_from_Gmail_Outlook_using_VBA.xlsm” by clicking here

When you open the workbook you need to allow Macros to run. Otherwise Excel stops the VBA code from running.

The example file contains two VBA code modules – one for Gmail and the other for Outlook.

Note: If you find that you can’t use this workbook on your version of Excel, please let me know using the comment section below.

VBA Cheat Sheets: Super handy one-page reference guides to help you write better Excel VBA Macros. Click here to learn more…

Send e-mail from Gmail using Excel VBA

Step 1: Activate Microsoft CDO for Windows

To access Gmail, we first need to activate Microsoft CDO for Windows. This can be accomplished by going to References in the tools drop down menu of the VBA editor.

Step 2: Allow less secure apps

Go to https://myaccount.google.com/security?pli=1#connectedapps from your browser and turn on the “Allow less secure apps” option.

Step 3: Execute VBA code

Execute VBA code with relevant parameters. You can copy the code from either the example file for this article or from the code in the box below.
You are expected to change the code according to your specifications. Please note that the code given below is just an example.
Parts of code that need to be customized are marked using red boxes in this screenshot:

This is the code for sending emails from Gmail:

Sub SendGmail()

   'creating a CDO object
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
   "username@gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
   "*********"

   'Update the configuration fields
   Mail.Configuration.Fields.Update

   'Set All Email Properties
   With Mail
      .Subject = "Write subject here"
      .From = "username@gmail.com"
      .To = "user1@gmail.com;user4@gmail.com"
      .CC = "user2@gmail.com"
      .BCC = "user3@gmail.com"
      .textbody = "write your mail here"
      .AddAttachment ("Folder Address") 'To attach Documents in mail
   End With
   'to send the mail
   Mail.Send

End Sub

Send e-mail from Outlook using Excel VBA

Step 1: Activate Microsoft Outlook Object Library

Like Gmail, to access Outlook with VBA, we first need to activate the Microsoft Outlook Object Library. This can be done by going to References in the tools drop down menu of the VBA editor.

Step 2: Execute VBA code

Execute the VBA code with relevant parameters. You can copy the code from either the example file for this article or from the box mentioned below.
You are expected to change code according to your specifications. Please note that the code given below is just an example.
Parts of code that need to be customized are marked using red boxes in this screenshot:

And here’s the code for sending emails from Outlook:

Sub SendOutlook()

   'Declaring Variables
   Dim OutlookApp  As Outlook.Application
   Dim OutlookEmail  As Outlook.MailItem

   'Assigning variables to create outlook application and mailitem
   Set OutlookApp = New Outlook.Application
   Set OutlookEmail = New Outlook.MailItem

   With OutlookEmail
      'Format of the mail
      .BodyFormat = Olformatplain
      'Body of the mail
      .body = "Dear Someone" & vbNewLine & "How are you?"
      'To whom you want to send mail
      .To = "Someone@somewhere.com"
      'Subject of mail
      .Subject = "Write Subject Here"
      'TO add an attachment
      .Attachments.Add ("Folder path")
      'sends the mail
      .Send
End With

End Sub

Accessing parameters stored in Excel

Important Note: For both Macros given in this article, parameters such as ‘to’, ‘subject’, ‘body, etc. can be directly accessed from Excel sheet itself.

An example of that is shown in the snippet below.

Let’s assume the subject of the mail that you want to send is in A1 of a worksheet named “Example”. Then the code parameter can be written as:

Author Bio – Perceptive Analytics

This article was contributed by Perceptive Analytics.

Nagendra Kumar, Ritwick P Rao, Jyothirmayee Thondamallu and Chaitanya Sagar contributed to this article.

Perceptive Analytics provides Excel Consulting, data analytics, business intelligence and reporting services to e-commerce, retail, healthcare and pharmaceutical industries. Its client roster includes Fortune 500 and NYSE listed companies in the USA and India.

Finally… (quick note from Launch Excel)

If you are using or learning Excel Macros & VBA you’ll sometimes struggle to write VBA code to do what you need.

Either you don’t know what code to use, or you can’t recall it…

Does this sound like you? (Don’t worry you’re not alone!)

I know the feeling of frustration that can build up when struggling to write VBA code.

So… you’re in luck – because I made a series of special one-page Cheat Sheets covering different aspects of Excel VBA.

You can learn more about them by clicking here.

– Victor (Launch Excel)

VBA Cheat Sheets: Super handy one-page reference guides to help you write better Excel VBA Macros. Click here to learn more…

Facebook Twitter Google+Share

Victor Chan

Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!

Join over 9,000 subscribers

GET BETTER AT EXCEL

CLICK TO GET FREE UPDATES