Sending e-mails from Gmail and Outlook using Excel VBA
![[Image - Excel VBA Emails]](https://www.launchexcel.com/wp-content/uploads/2018/05/email-excel-vba.jpg)
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.
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") = _ "[email protected]" 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 = "[email protected]" .To = "[email protected];[email protected]" .CC = "[email protected]" .BCC = "[email protected]" .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 = "[email protected]" '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)