Send Email Using Gmail with Excel VBA


We can send email using Google mail directly from Excel with VBA. We can do this using CDO. What is CDO? CDO stands for Collaboration Data Objects. Using the CDO library from References via Tools in the Visual Basic for Applications editor, we can add email capabilities to our VBA code. In end effect, we actually use the SMTP (Simple Mail Transfer Protocol) server of the mail provider. We then configure the details like SSL (Secure Socket Layer) authentication, SMTP authentication, SMTP server name, SMTP port, username and password via VBA. Once that is done, we set the email properties like Subject, From, To, Body, CC, BCC, etc. We can also send attachments like text, word documents, PowerPoint, Excel files, etc.
View the training video before you study the complete VBA code given below:


View this video on YouTube.

Sub send_email_via_Gmail()
Dim myMail As CDO.Message

Set myMail = New CDO.Message

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “takyardinesh@gmail.com”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”

myMail.Configuration.Fields.Update

With myMail
.Subject = “Test Email from Dr. Takyar”
.From = “takyardinesh@gmail.com”
.To = “takyar@hotmail.com; takyar@exceltrainingvideos.com”
.CC = “dinesh.takyar@gmail.com”
.BCC = “”
.TextBody = “Good morning!”
.AddAttachment “C:\Users\takyar\Desktop\email-via-gmail.txt”
End With
On Error Resume Next
myMail.Send
‘MsgBox(“Mail has been sent”)
Set myMail = Nothing

End Sub

Using Yahoo with VBA:
Sub email_using_Yahoo_VBA()

Dim myMail As CDO.Message

Set myMail = New CDO.Message

‘Enable SSL Authentication
myMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

‘SMTP authentication Enabled

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1

‘Set the SMTP server and port details

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.mail.yahoo.com”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

‘Set your username and password for your Yahoo Account

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “fccin2000@yahoo.com”

myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”

‘Update all configuration fields
myMail.Configuration.Fields.Update

‘Set the email properties

With myMail
.Subject = “Test Mail from Dr. takyar”
.From = “fccin2000@yahoo.com”
.To = “takyardinesh@gmail.com; takyar@exceltrainingvideos.com”
.CC = “dinesh.takyar@gmail.com”
.BCC = “”
.TextBody = “Welcome to MS Excel Training!”
End With

myMail.Send
MsgBox (“Mail sent”)

‘Set myMail Variable to Nothing to free memory
Set myMail = Nothing

End Sub

Download a sample file:

Further reading:
Sending mail from Excel with CDO
How to send mail from VBA using CDO

33 thoughts on “Send Email Using Gmail with Excel VBA

  1. Moshe Asaf

    Hello,
    when I run the code [Send_email_via_Gmail] I received this message: Compil error: User-defined type not define”‘ on the first line: Dim myMail As CDO.Message
    How can I fix it?
    Thanks , Moshe Asaf, ISRAEL

    Reply
  2. Jody Zscheck

    I keep getting an Compile error: Syntax error message on this section:

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “wiucampusrec@gmail.com”

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”

    And it highlights the first line. This text is also red in my module. I have been over it several times and cannot find the error. It seems to be targeting the : after http in the first line. Do you have any suggestions?

    Reply
  3. Mark Paetkau

    I have tried to use this code to send through my gmail account. First gmail blocked the log on attempt so I had to allow less secure apps access. Now the code says the email is sent, no error shows up, but nothing happens. Any suggestions? I have tried port 25 and 587 as suggested by other sites. Is windows firewall an issue? Any other possible issues? (Using Excel 2007) Thanks.

    Reply
    1. Mark Paetkau

      update..it does throw an error -2147220973 …any suggestions (windows firewall off). The closest I have come is Google detecting an attempted login (with the correct password)
      Thanks again for the code to get me on the path…

      Reply
  4. Pingback: Automate Excel to PDF and Email PDF Document using VBA | Excel VBA Training Videos

  5. Tishri

    This was very helpful but I have it all set up with no errors displaying but will not run when I push the run button. I have changed the security settings, tried 25, 587, and 467 port, no curly quote marks, rechecked for any misspellings…..Any other suggestions?

    Reply
  6. Shawn

    I want to use this code to be able to send an email, but i have multiple accounts, and i want to have the vba code pull the username and password from a cell reference.

    Example:
    Store 1 – username1 – password1
    Store 2 – username2 – password2
    Store 3 – username3 – password3

    now in a cell i have a VLOOK up for find the username and another for password from that store.

    I’m trying to get the macro to look at the cell that has the VLOOKUP formula to use the result as the username and a second cell with the VLOOKUP formula to get the password.

    When i try to run the macro i get an error saying:

    “The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available.”

    I’m tried to “evaluate” the formula and insert in another cell, and have the macro pull the username from the new cell, but i get the same answer. And i’ve gone and retyped over the cell that “evaluated” the formula and manually type in the username and password, the macro works

    HELP!

    thank you

    Reply
  7. Brian

    How can I use this to send email a list of 100 people, and have the Macro add in the name of the person from to whom the email is going automatically from a row of data fields within the excel file? Also, instead of adding in the specific emails to whom the email is being sent to, can I refer the “BBC” line to add all the emails within a row or column in the excel file? If so, please share code that would accomplish this.

    Reply
  8. shevaworld

    Hi my code is identical as the one in the video and I get the MsgBox Mail has been sent but I do not receive the email to change my security setting (not even in my spam folder).

    What I am doing wrong:
    Sub send_email_via_Gmail()
    Dim myMail As CDO.Message

    Set myMail = New CDO.Message

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smptauthenticate”) = 1

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “cafeteriasnackshack@gmail.com”

    myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “xxx”

    myMail.Configuration.Fields.Update

    With myMail
    .Subject = “Test Email from Dr. Takyar”
    .From = “cafeteriasnackshack@gmail.com”
    .To = “mgarciagallont@gmail.com; marinesh@hotmail.com
    .CC = “mg@sheva.com”
    .BCC = “”
    .TextBody = “Good morning!”
    ‘.AddAttachment “C:\Users\takyar\Desktop\email-via-gmail.txt”
    End With
    On Error Resume Next
    myMail.Send
    MsgBox (“Mail has been sent”)
    Set myMail = Nothing

    End Sub

    Reply
  9. Ryan

    +shevaworld i am having the same issue. i have the code and it compiles properly but the email is never sent or recieved to the users defined in the code

    Reply
  10. Muhammad

    I am getting message that email is sent but email is not sending.
    i change the cdo settings also but its not work.

    please help

    Reply
  11. sourabh vishwakarma

    Hi Dinesh,
    Thanks for your video,This is very helpful.
    Please help us with video for sending mail using Lotus Note with vba.
    Thanks in advance.

    Reply
  12. Jitu kakoty

    Hi when I tried this code error comes server not found.when I checked error comes when execute the line .send

    I also enbeled low level security setting in gmail but it did not work.

    Please help

    Reply
      1. Abdulrahman

        Hi Densih,
        I want to select part of the sheet i have and send it in the email.
        so far i have tried the following:

        1.) At the beginning of the code above i wrote :
        part_of_sheet_to_be_sent_in_email = Sheets.(“Sheet1”).Range(“A1:A7”).Select
        then i typed in that variable in that variable in the TextBody part, but the email only had “-1” in it

        Any ideas?? Please help. i need to do this for my job. No other websites seem to be of any help

        Reply
  13. Ollie

    Hi Dinesh,

    Great video, I have configured the email to send perfectly apart from I would like my logo to be sent with the email so that it is under my name when the recipient receives the email. What code do I need to do this?

    Thanks,
    Ollie

    Reply
  14. jessica

    hi Dinesh! thanks for the video. everything works well but i wanted to know if it is possible to display the email on gmail and not sending it directly ? (myMail. something? 🙂 )
    Thanks a lot !

    Reply
  15. Jasper

    I used the code provided and it works great! But after I hit Ok when the message box appears my access program doesn’t allow me to do anything. It doesn’t give me an error message but doesn’t allow me to do anything within access. Any suggestions would be greatly appreciated.
    Thanks for the code!

    Reply
  16. Michael

    Dear Dinesh,

    The code helped a lot and all is working. However, I would like to to to .HTMLBODY the following code :
    Dim r As Range
    Set r = Range(“B6:J88”)
    r.Copy
    Dim p As Picture
    Set p = ActiveSheet.Pictures.Paste
    p.Cut

    My idea, is to copy and paste screenshot of the of the table in the mail that I will send.
    How it can be done ?

    Thanks

    Reply
  17. Pranav Maru

    Hello Sir,

    I am able to run the code, however am not getting the mail in my specified email id as well as in sent mail too i cannot see the mail.

    Can you please help me as i am not getting any error.

    Regards,
    Pranav

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *