23 Comments

1. 

   Mark on 2020-04-18 at 6:52 AM

   Hi there,

   I use your script for my powerpoint, however i get a error on these:
   Dim newMail As CDO.Message
   Dim mailConfiguration As CDO.Configuration

   Datatype not defined?

   Can you help me out?

   Reply
   • 

     jiejenn on 2020-04-20 at 3:41 AM

     There should be a video included in the tutorial for some reason it is not showing. Anyway, there is a library reference you need to turn on. I have included the video + the timestamp.
     https://youtu.be/yFm1bPYk28k?t=50

     Reply
2. 

   ed on 2020-05-05 at 12:05 PM

   Hello,

   Im using an Excel spreadsheet to send emails via gmail. I would like to be able to track responses to my email campaign. How would you go about setting that up?

   Multiple choice response, Credit / Refund / Donate (if donate, identify an amt)

   How do you setup the link between the excel spreadsheet and the return email to record the response for each recipient?

   I thought perhaps generate a unique ID in the xls, include it in the email and search for it in the reply to tie the responses. Is there an easier way?

   Also, there could be multiple records for the same person/family. would need to go through the xls and retrieve all related records, then send email with information from all the records found.

   thanks for your help

   Reply
   • 

     jiejenn on 2020-05-10 at 9:19 PM

     This is pretty complicated thing you want there. Hum… to be honest, I don’t think Excel is really capable to do that.

     Reply
3. 

   Peyman on 2020-05-13 at 1:36 AM

   Hi,
   I have used your codes, but I got an error like this:
   Sign-in attempt was blocked for your linked Google Account
   Please help me to fix this error.

   Reply
   • 

     jiejenn on 2020-05-13 at 11:12 PM

     You may need to turn on allow less secure app setting first.
     https://myaccount.google.com/lesssecureapps?pli=1

     Reply
     • 

       Peyman on 2020-05-14 at 2:41 PM

       Thank you, but I have another problem with this now which says “the server respond was not available”. Can you help me with this?

       Reply
4. 

   Gihan on 2020-06-23 at 5:14 AM

   How do I copy something to the Body of the email from excel file?

   Reply
5. 

   Bobby Harris on 2020-08-12 at 6:03 AM

   Anyone know a way to do this in reverse?
   Send an email through Outlook based on Data in Google Sheets?

   Reply
6. 

   Bob Weston on 2020-08-13 at 7:41 AM

   Hi,

   I have tried this code in Microsoft Access and when I get to line 33 of the above code:

   .Item(msConfigURL & “/smtpusessl”) = True

   I get runtime error 348 “Object doesn’t support this property method”

   What am i doing wrong?
   Or is the Excel VBA different than the Access VBA?

   Reply
7. 

   Bob Weston on 2020-08-14 at 12:52 AM

   I got it to work on Access by changing the code a bit:

   Sub Send_Email_With_Gmail()

   Dim newMail As Object
   Dim mailConfiguration As Object
   Dim fields As Variant

   On Error GoTo errHandle

   Set newMail = CreateObject(“CDO.Message”)
   Set mailConfiguration = CreateObject(“CDO.Configuration”)

   mailConfiguration.Load -1

   Set fields = mailConfiguration.fields

   With fields
   .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
   .Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
   .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “”
   .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “”
   .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
   .Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
   .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
   .Update
   End With

   With newMail
   .Subject = “Hello World”
   .From = “”
   .To = “”
   .CC = “”
   .BCC = “”
   ‘ To set email body as HTML, use .HTMLBody
   ‘ To send a complete webpage, use .CreateMHTMLBody
   .TextBody = “This is a test email.”
   .AddAttachment “C:\Users\jiejenn\Desktop\Tutorial\Send Gmail Using VBA\MyLogo.png”
   End With

   newMail.Configuration = mailConfiguration
   newMail.send

   MsgBox “E-Mail has been sent”, vbInformation

   exit_line:
   ‘// Release object memory
   Set newMail = Nothing
   Set mailConfiguration = Nothing

   Exit Sub

   errHandle:

   MsgBox “Error: ” & Err.Description, vbInformation

   GoTo exit_line

   End Sub

   Reply
8. 

   aifm4232 on 2020-09-02 at 4:43 AM

   Hi!

   Thank you for this, it seems very useful and it is very clear.

   I am getting the following error: Error occured while sending the email !! — ActiveX component can’t create object

   Do you know what is happening and how can i fix it?

   Thanks!
   A

   Reply
   • 

     jiejenn on 2020-09-02 at 12:58 AM

     Have you activated Microsoft CDO for Windows 2000 library? (Tools > Library References)

     Reply
9. 

   Amir on 2021-01-06 at 4:45 AM

   hey I’m still getting a error despite turning on imap, pop and access to less secure apps

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

   Could this be because im using a corporate gmail account and its still being blocked?

   Thanks in advance, this is great

   Reply
   • 

     Hazel on 2021-03-25 at 9:01 PM

     Thank you for sharing. How does the code look like if we want to send the email to many Gmail recipients and copy the image of a range of cells to the body of email?

     Reply
   • 

     Paul Hanssen on 2021-11-06 at 6:11 PM

     Hi Amir,
     Did you find any solution for your problem? I have the same problem.

     Paul

     Reply
     • 

       Paul on 2022-04-10 at 7:18 AM

       Hi,
       Did you find any solution? I

       Reply
10. 

    David Sion on 2021-05-18 at 7:13 PM

    Hi,
    I get the following error:

    Error: At least one of the Form or Sender fields is required, and neither was found.

    Please help
    Thanks

    Reply
11. 

    John on 2021-06-17 at 4:19 PM

    hey I’m also still getting a error despite turning on imap, pop and access to less secure apps

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

    Could this be because im using a corporate gmail account and its still being blocked?

    Thanks in advance, this is great

    Reply
12. 

    Francois on 2021-12-13 at 8:38 PM

    Hi,

    I used your code and getting error : The “SendUsing” Configuration value is invalid. I made it equal to 2 like you did?

    Could you please assist?

    Reply
13. 

    Antonio on 2022-04-06 at 7:58 PM

    https://support.google.com/accounts/answer/6010255?hl=en#more-secure-apps-how&zippy=%2Cuse-more-secure-apps

    Less secure apps & your Google Account
    To help keep your account secure, starting May 30, 2022, ​​Google will no longer support the use of third-party apps or devices which ask you to sign in to your Google Account using only your username and password.

    Please note this deadline does not apply to Google Workspace or Google Cloud Identity customers. The enforcement date for these customers will be announced on the Workspace blog at a later date.

    For more information, please continue reading.

    Special Note on Apple Device Sign-Ins. Users who have not recently signed into their Google Account using only username and password will be able to only make new sign in attempts using the Google account type starting from February 28, 2022. Existing users may continue to sign into their Google Account using their username and password until May 30, 2022.
    Solution?

    Reply
    • 

      jiejenn on 2022-04-06 at 5:01 AM

      Thanks for the update, much appreciated.

      Reply
14. 

    ayaz on 2023-02-14 at 5:50 PM

    hallo,

    very nice code thx for the tut its working fein with gmail for me but its not work wich office365 smtp .. how can i done this ?? i have office365 email adress of my own domain like myname@mydomain.com

    Reply