How to send email using Gmail via Excel VBA

Share
Embed
  • Published on Aug 28, 2014
  • We can send email using Gmail, Hotmail or Yahoo directly from Excel using VBA.
    View the complete code and more details: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

Comments • 110

  • Atthapon Seema.
    Atthapon Seema. 5 months ago

    Can i use this concept with Mail becky application?

  • Panchito Montero
    Panchito Montero 6 months ago

    Very helpful. Thanks! I would very much be interested to implement something like this, but with multiple emails with an Excel file that contains columns with all sort of data. The emails are included in the Excel file, so it would be a row per recipient. Do you have a video for something like that?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  6 months ago

      This link will help: www.exceltrainingvideos.com/how-to-create-notifications-in-excel-with-vba-revisited/
      Or search www.exceltrainingvideos.com

  • AMA
    AMA 7 months ago +1

    Hello Mr Dinesh Sir, You are my Teacher like "eklavya Guriji" for All of your Tutorials Thank you very very very much for all of your tutorial, This is really work fine

  • TenSketch
    TenSketch 8 months ago

    Hi Dinesh. I'm using an automated Excel Invoice, where i want to send email by clicking a button, and it send email through gmail to the client. When i click the send email button, Each time the Macro should capture email id from a cell in the excel invoice. I have already created a Macro for saving as PDF. PDF invoice should automatically be attached to the email. Subject will be the filename of the invoice. Anyway to do this?.

  • Deepak Kumar
    Deepak Kumar 8 months ago

    Hi Sir,
    I tried as you mention the link that is very helpful but now i am facing another error "The Transport failed to connect to the server". please let me know how to run correctly my coding without getting any error.

  • Deepak Kumar
    Deepak Kumar 8 months ago

    Very Good Morning sir,
    I am very glad after watching this video but when i applied this coding i am getting an error message The "sendusing" configuration value is invalid." so please suggest me how to come out from this error.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  8 months ago

      This link will help: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
      A sample file is also included for download.
      Don't forget the CDO reference as mentioned in the details of the above link.

  • Deepak Kumar
    Deepak Kumar 8 months ago

    Hi sir,
    This video is every good but i getting error (Compile error :- Argument not option) when i go on TO : line means it is give error on email address to whom i want to send the email. So please help me resolve this error other.

    • Deepak Kumar
      Deepak Kumar 8 months ago

      Hi Sir,
      please help me as mention my probe above. It is very urgent.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  8 months ago

      @Deepak Kumar www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

    • Deepak Kumar
      Deepak Kumar 8 months ago

      also i am getting same error on attachment filepath & name

  • Mukund Joshi
    Mukund Joshi 9 months ago

    Hello Sir, thanks for the guidance, It's easy to understand and implement. I am stuck with one thing, so need your help if possible. I can't restrict my mail body content within certain cells, it might vary depending on the email. Hence earlier i used to send multiple emails using macro via Outlook in Excel itself, but now need to use gmail. In Outlook, mail body used to be my selection of cells in excel. I am unable to do it in Gmail. In short, Activesheet.selection needs to be my Email Body in Gmail. Kindly help. Thank you.

  • Med RAYAN
    Med RAYAN 11 months ago

    Dear Sir,
    I have a lisbox filled with data ( many lines and some columns ) and i wand to send all data in listox via email. would you please assist me to do that ?
    best regards

  • Ian Reeve
    Ian Reeve 11 months ago

    I would like to display the email before sending. Please indicate how this can be done? I have looked at the reply below but can't find the answer in the link you gave. Thanks

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  11 months ago

      This link will guide: www.exceltrainingvideos.com/send-numbered-message-automatically-through-outlook/

  • Sachin Shinde
    Sachin Shinde Year ago

    Hi Sir ...Excel cha code for sending mails via gmail along with attachment file.. please aap iska video banao...

  • Edward P
    Edward P Year ago

    Thank you very much; It took me 5 mins to copy your code and integrate this within my application, This is the best piece of code I have seen for a bit. You also explained it with such clarity, Greetings from London, UK Edward

  • Santosh Nikam
    Santosh Nikam Year ago +1

    Thank You Sir. It worked perfectly...

  • Эльмар Идрисов

    I have 2 questions:
    1. How to display email before sending it ? It is important as I want to make adjustments and then click "send".
    2. How to attach current open excel from which macro is written and which does not need to be saved after being emailed?

    • Ian Reeve
      Ian Reeve 11 months ago

      I too need to display before send, did you manage to do it? If so how please

    • Эльмар Идрисов
      Эльмар Идрисов Year ago

      Not sure how this playlist is going to help me. I did not find needed information.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  Year ago

      ru-clip.net/p/PLFoKoDG_7gtKiLkCe_k7RcOrX7E-4TIbe

  • Nirmal Singh
    Nirmal Singh Year ago

    Sir I have tried this programme and it is working perfectly . I want to add this code with a n excel Invoice vba which goes like this "Sheet1.Range("A2:R27").ExportAsFixedFormat xlTypePDF, Filename:= _
    "C:\Billing Data\new_sale_invoice" " so that my invoice should be converted into pdf as well as mailed via gmail. Would you help me please.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  Year ago

      www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/
      Or search www.exceltrainingvideos.com

  • Tony Nameless
    Tony Nameless Year ago

    Why myMail.Send causes excel to have grayed out print dialog ?
    Once mail is sent, printing workbook becomes a very strange process.

  • Roger Earl
    Roger Earl Year ago

    Will this work in Word 2016 for all clients email ?

    • Because011
      Because011 Year ago

      Hello did you find an answer to your question? I have same problem and I am looking for it.

  • Satish Kumar N.
    Satish Kumar N. Year ago

    Sir, Can we send mail by uninstalling outlook in system

  • Robert Heskett
    Robert Heskett Year ago

    Works great. Is there anyway to get a reference of cells to be the To and from, so if the addresses change you can a cell instead of modifying the script?

  • septiani trifosa
    septiani trifosa Year ago

    Hi sir. thx for the tutorial.. it works .. but I have some problem, I want to send excel sheet an cell as the body of email not as the attachment.. can u help me solve this problem and show me the code? I've tried some code but it only works for outlook... I need the code to send it with CDO message.. can u solve this? thx

    • septiani trifosa
      septiani trifosa Year ago

      yes ofc.. please help.. first of all thx for giving ur time.. u r very kind .. thank you very much

    • Antônio Santos
      Antônio Santos Year ago

      Well...I did now. Let me know if I can be of help. It works like a charm!

    • septiani trifosa
      septiani trifosa Year ago

      no.. Im not

    • Antônio Santos
      Antônio Santos Year ago

      Having an email sent automatically on CDO basis and with the the rows that meet a criteria in the body of the message - hopefully in HMTL format.

  • David Moore
    David Moore Year ago

    I get "Compile error" when I try to run this, the problem starts with the line ending in "smtp.gmail.com", any suggestions?

    • David Moore
      David Moore Year ago

      now I get the message "Run-time error '424': Object required"
      then when I click debug it highlights the line ending in smtp.gmail.com, how should I fix this?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  Year ago +1

      Get the code from here: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
      Don't copy paste.

  • Karmegam Mariappan

    Hello Sir. I need your support on my below doubt. In my Company they using gmail application as front end for Sending/Receiving emails but backside it is running in our company server. I just trying to create a macro for sending automatic email with my company SMPT name and port number but i am not able to send mail using this and also advise should i give my password in code because my username already having access to connect with our server . Could you please support me on this.

  • magi Bis
    magi Bis 2 years ago

    Really usefull sir

  • Paul Albert Weis
    Paul Albert Weis 2 years ago

    Hello... This is great dialogue. Could someone recommend how to embed a longer Mail.TextBody where VBA can update certain verbiage as it loops though the multiple Mail.To's . The video has a simple one liner, but I want to have many more (up to 20) Mail.TextBody lines, include closing salutation. PLEASE HELP ME

  • Tendentious Productions

    This doesn't work for 2016 Office for the Mac Right?

  • Trane Jones
    Trane Jones 2 years ago

    hi, I would like to attach the last date modified file (automatically), how can I do that? thank in advance!!

  • Nick Berg
    Nick Berg 2 years ago

    I am using VBA to send a gmail from excel. It works great, however, I need to send the gmail using one of my contact groups for the "TO:" section of the gmail. I have not been able to find the right syntax to make that work. Would someone please point me in the right direction.

    • Nick Berg
      Nick Berg 2 years ago

      I could put it into Excel, however, I did not want to recreate the work already done. Meaning that I have contact groups in gmail already created and would prefer to use the groups. It would also cut back on maintenance when having to update the e-mail lists; both in Excel and the Contact Group.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      You can use cell addresses if you have the data in an Excel worksheet.

  • abd hanyf
    abd hanyf 2 years ago

    as per the articular
    is not being maintained.
    msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

  • septiani trifosa
    septiani trifosa 2 years ago

    do u know how to run macro automatcally when data entered cell?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      www.exceltrainingvideos.com/highlight-excel-cells-while-entering-data/

  • Yopie Aprizal
    Yopie Aprizal 2 years ago

    should i activate outlook in my pc? bcs the code vwasn't working... there s no email sent in my gmail...

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      This link will help:
      www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

      Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
      If you are from India you can get this book here: amzn.to/2jzJGqU
      Or visit www.exceltrainingvideos.com to learn more for free.

  • RAVI ROSHAN
    RAVI ROSHAN 2 years ago

    hi, i am just not able to send mail using this, i tried everything but its not working, the code runs and doesnt display any error but in email i dont see any send or received mail, when i remove "on error resume next" line from code then it gives error " the transport failed to connect to the server" plz help

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      Read the details carefully at this link: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/
      Then watch the video.

  • dep icon
    dep icon 2 years ago

    The smtp port should be 465

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

  • krishnakishore peddisetti

    hi Dinesh,
    thank you for your wonderful tutorial. the code was working fantastic.but, is there a way where i can set the bounce back mail to my email id when the mail is not delivered. if that can be incorporated. the code is just great. right now i am not getting bounce back mails.
    will be waiting your reply
    thanks and regards
    kishore

  • tubagus setiawan
    tubagus setiawan 2 years ago +2

    Hello Mr Dinesh, You are my Hero :D, Thank you very much for all of your tutorial, This is really works.

  • Saša Milošević
    Saša Milošević 2 years ago

    Hello,
    I fallowed all your steps and it seem like its working but email just dont arrive to inbox, i tired with multiple accounts of gmail.
    What to do?

    • Saša Milošević
      Saša Milošević 2 years ago

      I tired everything, email just don t arrive :(
      Do You know what is the problem?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  2 years ago

      This link migh help: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

  • Jessica V
    Jessica V 2 years ago

    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 !

  • Tony Nameless
    Tony Nameless 2 years ago

    The code in the link has problems with quotes.
    Quotes from MS word are evil. They are designed to prevent people from copy/paste codes properly.

  • CE-SHP WEB
    CE-SHP WEB 2 years ago

    Morning: I got a error ; .AddAttachment "C:\Users\Ce-SHP\Desktop\email-via-gmail.txt", can you help me?

  • Boggula Siva Kumar
    Boggula Siva Kumar 2 years ago

    Hi Sir, First of all thanks for sharing your knowledge to all,
    I need to copy cell range form excel to paste in to gmail body pls help me to solve this.
    thanks in advance .

  • Uma Maheswari
    Uma Maheswari 3 years ago

    Getting Compile Error
    Argument not optional for .AddAttachment
    Kindly help to find the solution

    • Uma Maheswari
      Uma Maheswari 3 years ago

      Now I am not getting the error.Got mail sent message and no mails received in my mail box. Could you help me on this.

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  3 years ago

      Have a look at this link and the VBA code:
      www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

  • apoorva jain
    apoorva jain 3 years ago

    I exactly did the same but it's showing user defined type not defined help me out anyone

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  3 years ago

      www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/

  • Saurabh Singh
    Saurabh Singh 3 years ago

    Dr. Takyar it is not working

  • Santosh Kumar
    Santosh Kumar 3 years ago

    Hi Dinesh, Very good video thanks for it.
    But can you please help me resolving my issue. I did exactly same what you explained but it is showing me error "The transport failed to connect to the server".
    What wrong i am doing. pl check.
    Thanks in advance.
    Santosh

    • Ganesh Senkunthar
      Ganesh Senkunthar Year ago

      Santosh Kumar
      It might be an issue with your internet connection or incorrect login credentials...pls check

  • Matt Lutey
    Matt Lutey 3 years ago

    Dr. Takyar,Every day I want to send an email with an update given the current cell's value for the day. How can I modify the message body to do this?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  3 years ago +1

      Use a variable and assign the current cell's value into the variable. Finally use the variable for the body. You can also assign the current cell's value to the '.body' .

  • Bob Arnold
    Bob Arnold 3 years ago

    One of the best instructional videos I've seen. Well paced, multiple explanations of the same concept, and I particularly liked the way the instructor created problem and then worked through them. I feel watching multiple videos on the subject is not necessary. Tkx

  • bhavya bansal
    bhavya bansal 3 years ago

    Works like a charm!! Thanks so much Dr. Takyar!

  • Saverio martino
    Saverio martino 3 years ago

    Hi Mr. Dinesh, i'm italian so i apologize for my english, only a question: in my client (Outlook) there are 4 account, and the one that i would use from vba isn't the first one (thet the vba sub use by default) what can i do? otherwise i have to remove all my account and reinstall all again.
    Thank you very much.
    Saverio

  • Sayantani Kayal
    Sayantani Kayal 3 years ago

    sir, there is no error showing in my code .the msg box is showing that you mail has been sent but when i am opening the gmail account there i didnt forund any excel msg i had mail. please solve the prob n rply as soon as possible thank you sir

  • Riyad Davids
    Riyad Davids 3 years ago

    Dear Sir,
    I am a teacher and have to send progress of my students to their parents on a weekly bases. I created a workbook where i plot their weekly progress and I would like to ask if you can make a video to explain the vba code that I found on the internet.
    I am a novice when it comes to VBA but would like to learn more.
    Please help.

  • Mateo Patiño
    Mateo Patiño 3 years ago

    Hello Dinesh. Thanks a lot for your video. I cant seem to run it perfectly. I dont recieved the mails nor the notification from gmail. However, I will also like to know how can I input in the mail body, information from an active excel sheet, and how can I set the sendto adress also from a cell on the excell sheet. thanks

    • Robert McCahon
      Robert McCahon 2 years ago

      Try setting your smtpserverport to 465

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  3 years ago +1

      +Mateo Patiño Have a look at this video: www.exceltrainingvideos.com/send-email-using-gmail-with-excel-vba/ Use the range or cells property to get information from your worksheet into the macro.

  • Armin
    Armin 3 years ago

    Thanks a lot, everything is working as id should. Is there a way to create a PDF file of an area without using pdf creater, just using vba?

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  3 years ago

      +Armin Just define the range like Range("A8:E20") that you wish to convert to PDF in your VBA code.

  • MdArshad Bashir
    MdArshad Bashir 3 years ago

    Thank You immensely for doing a Great Job in VBA Excel . Your expositions are really remarakble. Especially in the way you present them in a very thorough way. Thanks and Thanks a lot indeed.. Can you also please show how to read our Gmails messages using VBA Excel or make a back up of these messages in an excel worksheet

  • synergistix india
    synergistix india 3 years ago

    THANK YOU...

  • Mark A. Treviño
    Mark A. Treviño 4 years ago

    THANKS! at last a process that REALLY Works! or sort of. The Yahoo subroutine works just as you mention. The Gmail however doesn't seem to work. This is consistent with the many others out there that claim to send email using Gmail and I have yet to find one that works. I have made all changes to ports, and really disabling security and still the messages don't go. Typically they are dated 2+ years ago, and Gmail seems to be a moving target. While gmail is preferred, I get what I need by using YAHOO, as YAHOO sub DOES work. THANKS AGAIN.

  • Riza Khan
    Riza Khan 4 years ago

    Can you add an authenticating password to this script so that you don't have to disable your security settings in your email account?

  • Danny Lau
    Danny Lau 4 years ago

    Hi Dinesh,
    I was wondering if there was a way to send the current file you are working on?
    Thanks!

    • Dinesh Kumar Takyar
      Dinesh Kumar Takyar  4 years ago +2

      @Danny Lau Try this: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/

  • Nitish Vishwakarma
    Nitish Vishwakarma 4 years ago

    Hi Dinesh Sir,
    Thanks for your valuable videos.
    Can you help me out for a problem I have written below-
    I want to automate Google spreadsheet add-on "Yet another mail merge" through Script Editor.
    Or tell me how to mail merge through Ms-Word but with personalized Subject Lines.

  • Neeraj Natu
    Neeraj Natu 4 years ago

    the mail is not going....I have no errors in VBA popping up, the msgbox says mail sent as i had typed in but still no mails in my gmail account.

  • muhib miah
    muhib miah 4 years ago

    When I run the VBA, It only runs for a second and then stops

  • muhib miah
    muhib miah 4 years ago

    Hello Dinesh
    I have followed your instructions, but the email does not send at all. I have used the following code as instructed by you, What I'm I doing wrong?
    Option Explicit
    Sub Send_Email_via_Gmail()
    Dim myMail As CDO.Message
    Set myMail = New CDO.Message
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/smtpusesssl") = True
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/sendusing") = 2
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/sendusername") = "muhib888@gmail.com"
    myMail.Configuration.Fields.Item("schemas.microsoft.com/cdo/configuration/sendpassword") = "My Password"
    myMail.Configuration.Fields.Update
    With myMail
    .Subject = "Test Email from Muhib"
    .From = "muhib888@gmail.com"
    .To = "muhib888@hotmail.com"
    .CC = "muhib.miah@specsavers.com"
    .BCC = ""
    .TextBody = "good Morning"
    .AddAttachment "C:\Users\muhib.miah\Documents\New Regional Performance Report YORK.xlsx"
    End With
    On Error Resume Next
    myMail.Send
    'MsgBox ("Mail has been sent")'
    Set myMail = Nothing
    End Sub

  • WJ JUNG
    WJ JUNG 4 years ago

    How to loading new mail in gmail via Excel VBA

  • Imran Anwar
    Imran Anwar 4 years ago

    Hi Sir,Love your Videos.Very Special Thanks to you.These help me a lot

  • nikunj gattani
    nikunj gattani 4 years ago +5

    Sir, I am not able to send email. I am not getting any error in vba code as well as not getting any mail in both To and From ids

  • Mike van Wieringen
    Mike van Wieringen 4 years ago +1

    Thanks for this well explained tutorial.

  • enionsouza
    enionsouza 4 years ago

    It's just amazing... A kind of revelation or something... I didn't even imagine one could do so much from Excel... Dinesh, you're fantastic!!

  • krn14242
    krn14242 4 years ago

    Thanks Dinesh. Strange it still worked. Check your authentication line. I think you wrote SMPT vs. SMTP. Thanks for the great tips on sending gmail via excel.