none
VBA & Gmail API RRS feed

  • Question

  • Thank you for taking the time to read my question.

    I'm wondering if anyone knows if its possible to use Google's Gmail API with VBA. I've used CDO in the past for sending emails internally from our SMTP server but CDO only seems to work IF I decrease the security on my Gmail account.

    Google has good examples but they are in Python (which I don't know).

    Any pointers would be great.

    Thanks!

    Thursday, November 14, 2019 10:04 PM

All replies

  • I found a somewhat promising lead for you: https://stackoverflow.com/questions/27941012/reading-gmail-email-messages-via-vba-without-outlook


    -Tom. Microsoft Access MVP

    Friday, November 15, 2019 1:41 AM
  • Hi Tom,

    Thank you for your reply.

    I continued to search and found this: https://stackoverflow.com/questions/13825223/fetching-gmail-inbox-mail-messages-via-cdo-in-vba-excel/24578767#24578767

    Use the link to GitHub. I have downloaded this but not tried it yet. I'm wondering what you think of this? It seems the MS Access part of it is not fully developed yet as compared to Excel.

    Friday, November 15, 2019 12:28 PM
  • I have set up outbound email from an Access application via a gmail  account.  Your post mentions having to 'decrease the security on the gmail account'....   there is a reality from Google in manually accepting an application fed source for the account, as part of the account properties, in lieu of the normal 2 level security check which one simply can't avoid...Google issues you a token that you insert into your code.  …. I don't necessarily view this as a decrease in the security but it could be viewed as such....

    Friday, November 15, 2019 10:32 PM
  • Hi,

    Thanks for your reply. That sounds like a good idea. Would you be willing to share the steps you took to get the token and how you applied it?

    Friday, November 15, 2019 10:35 PM
  • this is the code that works:

        Dim iMsg As Object

        Dim iConf As Object

        Dim Flds As Variant

     

        Set iMsg = CreateObject("CDO.Message")

        Set iConf = CreateObject("CDO.Configuration")

     

        iConf.Load -1

        Set Flds = iConf.Fields

       

         With Flds

            .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") = "yourEmail@yourDomain.com"        'note: using gmail but have our company domain

            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Bl4321!"           ' "ltaelnixwcivple"   'UNIQUE PW GENERATED AT GOOGLE FOR 2 STEP VERIFICATION

            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 'smtp mail server

            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 'stmp server

            .Update

        End With

        With iMsg

            Set .Configuration = iConf

            .To = "destinationEmail@destinationDomain.com"

            .From = "your@email.com"   

            .Subject = "Hello World"

            .TextBody = "Hi Dude"

            '.AddAttachment "C:\path\file.pdf"

            .Send

        End With

        Set iMsg = Nothing

        Set iConf = Nothing

    NOTE - I believe that if the account does NOT HAVE 2 STEP VERIFICATION then the sendpassword code line & token was not needed...

    this is about ~3 years old....

    To create app password: https://support.google.com/accounts/answer/185833?hl=en

     

    . I had to enable access for less secure apps for my account in question by:

    1. Logging into the address you want to use for sending email 
    2. Visit the page https://www.google.com/settings/security/lesssecureapps
    3. Click Enable Less Secure Apps.

    Click Done.

    but

    you'll have to search google on their current mode to generate a Token in lieu of the 2 step verification - - that's totally up to them - and the above could be out of date

    Also, Gmail has an alternate server port number: 587.  Outlook uses port number 25.

    Some additional info here:

    http://kbase.icbconsulting.com/vba/send-an-email-from-access-database-using-gmail


    • Proposed as answer by Gustav BrockMVP Saturday, November 16, 2019 10:44 AM
    Friday, November 15, 2019 10:53 PM
  • Hi 

    My CDO EMail Tester example app includes code solutions to manage the GMail security issue.

    Similar to the above but you have a working sample you can test with your own account details


    • Proposed as answer by Gustav BrockMVP Saturday, November 16, 2019 10:44 AM
    Friday, November 15, 2019 11:25 PM