Sending e-mails from Gmail and Outlook using Excel VBA

Last updated on May 29, 2018 By

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.

the Gmail instructions.
the Outlook instructions.
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.

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)

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.