Sending e-mails from Gmail and Outlook using Excel VBA

[Image - Excel VBA Emails]

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

[Image] Download 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.

[Image - Activate Microsoft CDO for Windows]

Step 2: Allow less secure apps

Go to from your browser and turn on the “Allow less secure apps” option.

[Image - Allow less secure apps]

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:

[Image - Execute Gmail VBA code]

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 _
   ("") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("") = _
   Mail.Configuration.Fields.Item _
   ("") = 25
   Mail.Configuration.Fields.Item _
   ("") = 2

   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("") = _
   Mail.Configuration.Fields.Item _
   ("") = _

   'Update the configuration fields

   'Set All Email Properties
   With Mail
      .Subject = "Write subject here"
      .From = ""
      .To = ";"
      .CC = ""
      .BCC = ""
      .textbody = "write your mail here"
      .AddAttachment ("Folder Address") 'To attach Documents in mail
   End With
   'to send the mail

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.

[Image - Activate Microsoft Outlook Object Library]

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:

[Image - Excecute VBA code with relevant parameters

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 = ""
      'Subject of mail
      .Subject = "Write Subject Here"
      'TO add an attachment
      .Attachments.Add ("Folder path")
      'sends the mail
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:

[Image - 6 Directly calling parameter]

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)

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


Recent Posts
Showing 30 comments
  • Peter

    Nice article. Thank you for it. In the beginning you refer to activate CDO for Windows. Is it also possible for a Mac?

    Thank you.

  • Chamai Shahim

    This article is really easy to follow and implement. I unfortunately am having issues getting the code to run and I am not sure why. I changed all the required information and then hit run. I get the Error, “Run -time error ‘-2147220973(80040213)’: The transport failed to connect to the server.
    I would greatly appreciate your help!

    • Jyothirmai

      Hi Chamai,

      This Run time error occurs when the port number is incorrect under SMTP configuration settings.

      In the code, please check if the smtpserverport is set to 25
      ” Mail.Configuration.Fields.Item _
      (“”) = 25 ”

      Please let me know If this helps!

      • Mahadev Uttam Shirodkar

        Error still persists.

      • Srikanth K

        When i tried running Excel vb script to send an email to outlook using SMTP, but I’m getting the Error, “Run -time error ‘-2147220973(80040213)’: The transport failed to connect to the server.As you have mentioned i have checked my port number and it seems correct, actually i need to send an email from my work account. The below are the details i’m using to send an email.
        .Item(msConfigURL & “/smtpserver”) = “”
        .Item(msConfigURL & “/smtpserverport”) = 25

        I have aslo cross checked the details using command prompt and using the command (telnet 25) and was able to send a test message, but using excel VBA i could not send. Could you please check and provide your inputs/suggestions to solve this issue. I guess, in outlook macros were disabled and due to this i’m facing error or not. Can you check on this also.

  • Malthe

    Hi all,

    Great article. However, I get the error;

    “Run-time error ‘-2147220975 (80040211)’: This message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available.”

    What to do?

    • Jyothirmai

      Hi Malthe,

      This happens when you do not give your Gmail access to Excel VBA.
      Please make sure you enable “Allow less secure apps” ON (Step 2 in ‘Send e-mail from Gmail using Excel VBA’ section) before you run the code.

      Please let me know If this helps!

  • Sia

    Hello there,

    I am having the same issue as the previous comments; “Run -time error ‘-2147220973(80040213)’: The transport failed to connect to the server. However, my server-port is 25 and I have turned ON the “Allow less secure apps” on my gmail account. What else could be wrong in this case?? Looking forward to see your solution!!!

  • Mahadev Uttam Shirodkar

    smtpserverport is set to 25. Still, the error persists.

  • Marius

    Hi is there a way to send email without enabling less secure apps?
    I dont have this option in my gmail.
    And its its off then this macro does not work.

  • Ann Marie Sean Foley

    Hi, Is there a way to send a gmail from MAC? I have spent hours researching but still cannot find a solution..

  • Fausto

    Worked perfectly! Thank you so much!

  • Anupam Sthapit

    OK I found the solution. smtpserverport is 465. (change 25 to 465)

    (“”) = 465

    It will work now.


  • Lyla

    All your contributions are very useful for professionals and non-professionals. I appreciate your availability to share these types of great and valuable info And you did it very well! Can’t wait to read more… You nailed it……..

  • Kannan

    Thank You So much…! This is working fine!

  • Siddhant

    Im getting an error near ‘New.Outlook.MailItem’ Says invalid user of key word

  • AK

    It does’nt work

  • AK

    Run-time error-2147220973(80040213)’:
    The transport failed to connect to server.

  • Daniel

    Awesome! Worked great with smtpserverport of 465

  • Antoine TAWA

    Excellent article. I used it and created a small app that sends Gmail from Excel VBA.
    Unfortunately however, for some adressees it does not work: I get a “mail delivery” message 550 with sometimes “spam detected”!
    Any clue? Do i need to add/change anything? or maybe change some parameter at the receiver’s end??
    Your help is very much appreciated …

  • ms

    wrong code

  • Kevin

    It worked great. Is there a way to create the email in GMAIL but not send it to allow me to make adjustments before I send?


    Hi, tried your macro. Get a error message that says
    Compire Error
    User defined type not defiend.

  • xxxxx

    maybe remove the “.send” part.

  • mayur shashikant marathe

    I need to pass parameters such as ‘to’, can be directly accessed from Excel sheet itself. i used the the code above Worksheets(“sheet”).Range(“c9).value but it is failing in compilation

    have any alternative

  • Jason

    Google have killed The allow less secure Apps option, like their platform is secure when they a phishing all our data for their own ends. Is there a workaround for this.

  • naim said

    .AddAttachment (“Folder Address”)
    how can I create these folders with Gmail

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.