Example of sending an email with attachments using the Gmail API

[hnsywnwtsly]

I use Google translation so sorry if my English is not clear.

Hello everyone

For a long time I was looking for an example of a code that sends an email with attachments via Gmail and I did not find, after much effort I was able to compose a number of codes that do the job, I share them here for the benefit of all.

Note: Some of the code may be redundant or will work better after some modifications, I do not know, much of this code I copied as is and I do not understand what it does.

Attached below is an example of an Aceess file and an example of an Excel file for sending email using Gmail Api.

credits:
Thanks to @timhall for this great library!

And thanks to @RichardWein for sharing his code (in this answer) that keeps the refresh token reusable, using this code is enough to perform authentication google only for the first time, and next times to use the refresh token.

Instructions:

1. Update the CLIENT_ID constant and the CLIENT_SECRET constant located in the OtherStuff module.
2. Use the SendEmail function in the Gmail module to send mail
3. The function accepts the following arguments:

• SenderName: The name of the sender as displayed to the recipient of the message
• SenderAddress: Sender's Address - Attention! If the address you pass to the function is incorrect, the email will be sent but the sender's name will display the email address instead of the sender's name
• Recipients: Recipients - You can move multiple recipients separated by a comma ","
• RecipientType: Recipient type - Visible recipients or hidden recipients
• Subject: Message subject, optional
• MessageText: The content of the message, optional
• Files: Attachments - Optional, you can move multiple separated files with the question mark "?"
• ReplyToAddress: Reply Address - Optional, if you want the reply address to be different from the sender's address
• ReplyToName: Name Of Reply Address - Optional, only relevant when using the ReplyToAddress argument
• bAsync: This argument was provided along with some of the code I copied from somewhere, I have no idea what it does (optional)

4. The Test function serves as an example of sending an email
5. To reset the refresh token stored on your computer, use the ClearGoogleAuthentication function

Note: The SendEmail function uses the ToBase64 function, it is designed to correctly display the sender name, subject, and mail name for reply in non-English languages, in English it is not consumed, although it also does not interfere with leaving it as is.

Send Email - Gmail Api With Access.zip
Send Email - Gmail Api With Excel.zip

[TangledinTradition]

I used the Access version and it help greatly. But I am having an issue and getting the message :
Error 400: invalid_request

You can't sign in to this app because it doesn't comply with Google's OAuth 2.0 policy for keeping apps secure.
If you’re the app developer, make sure that these request details comply with Google policies.
redirect_uri: urn:ietf:wg:oauth:2.0:oob

Any help ASAP would be great since they are shutting off the old access Monday.

[hnsywnwtsly]

Hi, I'm glad you've used this,
This line:
Private Const LOGIN_REDIRECT_URI As String = "urn:ietf:wg:oauth:2.0:oob"
Located in the "GoogleApiShared" module, it tells Google that after the user verifies his account the response will be a verification code for copying and pasting manually in your app, this option is only available for an app under review, you probably submitted your app for posting (which is great because you get a valid refresh token Forever, compared to a test app that gets a refresh token that is only valid for a week or two) and so you run into this error.
You need to use a reference to uri which basically means a reference to the URL of your private site, then after the user verifies his Google account it will be transferred to your site and the verification code will appear in the "code" parameter. (From there you copy it and paste it into your VBA app).
In order for you to be redirected to your URL you need to create a certificate for a "Web application" type and then add your URL under the heading "Authorized redirect URIs".
Then replace the line for redirection in your code, replace this line:
Private Const LOGIN_REDIRECT_URI As String = "urn:ietf:wg:oauth:2.0:oob"
And put this code in its place:
Private Const LOGIN_REDIRECT_URI As String = "https://your-site.com/your-project/authorization-code"
Additionally, you must add these two lines in the verification request: (it is in the "GetLoginUrl" function)
auth_Request.AddQuerystringParam "access_type", "offline"
auth_Request.AddQuerystringParam "prompt", "consent"
This will ensure that in response to the verification code you will also receive a refresh token and not just an access token.

[hnsywnwtsly]

I opened a personal website in wix for the authorization code (it's free), Google requires you to prove ownership of the site, and in wix there is a built-in tool to prove ownership, it went really easily.
You can also make a code on your site that will extract the "code" parameter from the address bar and put it in a text box so that your users will know what to do, I also added a "copy code" button.
It requires very minimal knowledge in JavaScript, wix has their own code words and it is really easy to find the codes in their forum.

[TangledinTradition]

Thank you for this information. I am going to see if I can make this happen although I'm not 100% sure I understand. One other question, It is working in test mode, is there an issue with leaving it in test mode? Chris Davis Tangled in Tradition https://tangledintradition.com Like us on Facebook Facebook <https://www.facebook.com/tangledintradition> Follow us on Instagram Instagram <https://www.instagram.com/tangledintradition/>

…

On Sun, May 29, 2022 at 5:20 AM hnsywnwtsly ***@***.***> wrote: Hi, I'm glad you've used this, This line: Private Const LOGIN_REDIRECT_URI As String = "urn:ietf:wg:oauth:2.0:oob" Located in the "GoogleApiShared" module, it tells Google that after the user verifies his account the response will be a verification code for copying and pasting manually in your app, this option is only available for an app under review, you probably submitted your app for posting (which is great because you get a valid refresh token Forever, compared to a test app that gets a refresh token that is only valid for a week or two) and so you run into this error. You need to use a reference to uri which basically means a reference to the URL of your private site, then after the user verifies his Google account it will be transferred to your site and the verification code will appear in the "code" parameter. (From there you copy it and paste it into your VBA app). In order for you to be redirected to your URL you need to create a certificate for a "Web application" type and then add your URL under the heading "Authorized redirect URIs". Then replace the line for redirection in your code, replace this line: Private Const LOGIN_REDIRECT_URI As String = "urn:ietf:wg:oauth:2.0:oob" And put this code in its place: Private Const LOGIN_REDIRECT_URI As String = " https://your-site.com/your-project/authorization-code" Additionally, you must add these two lines in the verification request: (it is in the "GetLoginUrl" function) auth_Request.AddQuerystringParam "access_type", "offline" auth_Request.AddQuerystringParam "prompt", "consent" This will ensure that in response to the verification code you will also receive a refresh token and not just an access token. — Reply to this email directly, view it on GitHub <#472 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AXA43XLJE6M33BZEDTTMFJ3VMMZELANCNFSM5SCZ2SZA> . You are receiving this because you commented.Message ID: ***@***.***>

[hnsywnwtsly]

For me this solution works great

But you can leave it in test mode, it has one significant drawback: the refresh token you receive is valid for one or two weeks only, then once a week or two your user will be required to verify his account again.

To get a refresh token that is valid forever you need to use the app that was submitted for publication, but you should know! You do not have to complete the advertising process, it is enough that you submit it for advertising and you do not proceed with the following steps and it already works with a refresh token that is valid forever. The only thing is that your user will see a warning that the app has not been verified.

[RichardWein]

Thanks for this information, @hnsywnwtsly. I didn't realise that Google's option to copy and paste the authorisation code was only available in test mode. I've always left my applications in test mode so far. My users don't seem to mind having to re-authorise every week out two.

[hnsywnwtsly]

Indeed, I sweated a lot until I realized it,
According to Google documentation (here) this option is already disabled

[shajvarg]

this was really good information. I am also now trying to get a refresh token that lasts forever..
i however had a few stumbling blocks... hope you can help..
the wix website made my website like shajvarg.wixsite.com/mysite. so I although I can verify shajvarg.wixsite.com/mysite, I cannot verifiy shajvarg.wixsite.com in google using the SEO tools.. how did you manage to do this?

can we use any youtube video showing gmail api stuff? will they object later? I feel making a video showing all scope usage is a bit too much ..

shaju

[hnsywnwtsly]

I tried to enter your site, it returned a 404 error
It looks like you have not published your site yet, which is why Google is unable to find it

[shajvarg]

hi,
sorry i was using an example
the actual site I made is https://shajvarg.wixsite.com/accura

so I have verified https://shajvarg.wixsite.com/accura in google search console
but I cannot verifiy https://shajvarg.wixsite.com the domain which is a requirement during publishing..
this is my issue

[shajvarg]

which method did you use for verification? HTML tag or some other method?

[hnsywnwtsly]

HTML tag.

I just verified the home page address, that's enough, no need to verify the domain.
It should work for you now

[shajvarg]

shajvarg.wixsite.com/accura
Authorised domain 2 *
Invalid domain: must be a top private domain

this is the error I get during publishing

and shajvarg.wixsite.com only doesn't work

[hnsywnwtsly]

What do you have registered in "Authorized Domain 1" ?

[shajvarg]

i tried another domain also. which I could verify.
but even if I remove domain 1, the error is still the same..

[hnsywnwtsly]

I do not know,
I entered the address of the home page in the "App Home": https://00****88.wixsite.com/my-site

And in "Authorized Domain 1" I wrote:
00****88.wixsite.com

[shajvarg]

thanks, i managed to get authorization code with wixsite

now I am not getting refreshtoken

it says invalid client

    ' Make an "authorization code" request (using authorization code to get a new refresh token)
    Set auth_Request = New WebRequest
    auth_Request.Method = WebMethod.HttpPost
    auth_Request.RequestFormat = WebFormat.FormUrlEncoded
    auth_Request.ResponseFormat = WebFormat.JSON
    Set auth_Body = New Dictionary
    auth_Body.Add "client_id", CLIENT_ID
    auth_Body.Add "client_secret", CLIENT_SECRET
    auth_Body.Add "grant_type", "authorization_code"
    auth_Body.Add "code", authorizationCode
    auth_Body.Add "code_verifier", codeVerifier
    auth_Body.Add "redirect_uri", LOGIN_REDIRECT_URI
    Set auth_Request.Body = auth_Body
    Set auth_Response = ExecuteRequest2(auth_Request, auth_TokenClient)

anything different needs to be done?
LOGIN_REDIRECT_URI is the same wixsite one?

shaju

[hnsywnwtsly]

I compared this piece of code with mine, it's exactly the same.
There can be several problems:
Is the client ID you created for a "network application" app?
Did you add your URL in the "Redirect Authorized URI"?
If you were able to get an authorization code then I guess these are not the issues, but there could be another thing:
Did you add the following lines in the verification request? (Found in "GetLoginUrl" procedure)
auth_Request.AddQuerystringParam "access_type", "offline"
auth_Request.AddQuerystringParam "prompt", "consent"

[shajvarg]

client ID is for web application with all URLs pointing to wix site.

getloginurl also is as you said

Private Function GetLoginUrl(ByVal codeChallenge As String, ByVal challengeMethod As String) As String
' Get the login url, forming the string from various elements
' Create a temporary Request, so that we can use Url helpers

Dim auth_Request As New WebRequest
auth_Request.resource = LOGIN_ENDPOINT
auth_Request.AddQuerystringParam "client_id", CLIENT_ID
auth_Request.AddQuerystringParam "redirect_uri", LOGIN_REDIRECT_URI
auth_Request.AddQuerystringParam "response_type", "code"
auth_Request.AddQuerystringParam "code_challenge", codeChallenge
auth_Request.AddQuerystringParam "code_challenge_method", challengeMethod

auth_Request.AddQuerystringParam "scope", OAUTH_SCOPES
auth_Request.AddQuerystringParam "access_type", "offline"
auth_Request.AddQuerystringParam "prompt", "consent"
GetLoginUrl = auth_Request.FormattedResource

End Function

[shajvarg]

hi,
it appears I have to wait for approval / verification to use gmail.send and gmail.readonly scopes when I publish the app?
i cannot use it immediately like in testing mode

[shajvarg]

this is what i found....

Apps with a publishing status of 'In production' must complete verification for all requested sensitive and restricted scopes. An app requesting unverified sensitive or restricted scopes will result in the display of unverified app warnings, which may prevent user authorisation. See OAuth user quotas for more information about user authorisation limitations while your app's use of these scopes is unverified.

[hnsywnwtsly]

No, you do not have to wait for the app to be approved because these perimeters are considered sensitive and not restricted perimeters, I started using immediately after submitting the publication request.
I read all their material, as long as your app has not been verified, you can use it and it is limited to 100 users, in addition you will be shown a warning that the app is not verified.
I suggest you upload your code here, maybe we'll see what the problem is

[shajvarg]

thanks for all the help..
I tried the entire procedure with another of my gmail accounts. And it worked..
So all good :-)

[hnsywnwtsly]

Hi
I'm glad to hear
Successfully

[joebob99]

Hi,
Thank you for all of this, though I am struggling to get it working.
We are an accounting firm that uses a lot of VBA macro to automate work. We use Gmail to send error/status messages where there are problems with the automation. With Google now shutting down Unsecure apps for business users, I have started looking into replacing my old VBA code for sending emails that just used the username/password.

I have only used the Google APIs and Services pages infrequently, so I am not 100% confident on those pages. I created a project, enabled the Gmail API and created an OAuth credential of type Desktop. I downloaded your sample XLSM file and entered the client ID and secret into the relevant spot on the OtherStuff sheet. When I t to send a test email, it tries to authorise, but I get this error:

Error 400: invalid_request
The out-of-band (OOB) flow has been blocked in order to keep users secure. Follow the Out-of-Band (OOB) flow migration guide linked in the developer docs below to migrate your app to an alternative method.
Request details: redirect_uri=urn:ietf:wg:oauth:2.0:oob

I am at a loss as to where I am going wrong. Would you know?

[RichardWein]

Hi Joe. I'm not up-to-date with Google's latest security changes, but perhaps I can help a bit.

This is all about how the Google authorisation server can send an authorisation code to your app (your macro) in a secure way. The VBA code that you're using for authorisation sets the redirect_url parameter to use the OOB option. In other words, it tells Google to display the authorisation code in the user's browser, from where they can copy-paste it over to your macro. This option used to be available to apps in 'Testing' mode only. Much of the discussion in this thread has been about how to do the authorisation another way (not using OOB) because the posters didn't want to use Testing mode.

It seems likely that Google is no longer allowing the OOB option, even in Testing mode. However, to make sure that's the case, I suggest you check that your app is using Testing mode. In your Google APIs and Services page, look under 'OAuth consent screen' > 'Publishing status'. If that says Testing, then the OOB option is no longer available, and you will have to use an alternative. If it doesn't say Testing, try using Testing mode, as that will be the easiest option.

The alternative discussed above is to create your own website, tell Google to send the authorisation code to that website, and then make the website display the authorisation code in the user's browser. The posters above created their websites using Wix. I haven't tried doing this myself. Obviously it's going to be more effort than the OOB method.

The option recommended by Google (in general, not specifically for VBA) is to have your app listen for the authorisation code on a web socket. This would avoid having to set up a website, and avoid the user having to manually copy-paste the code. I've just been googling for information on how to do this in VBA. It looks like it might be possible, but it won't be easy to get it working. At some point I might try it, as I might need it in the future.

[joebob99]

Hi Joe. I'm not up-to-date with Google's latest security changes, but perhaps I can help a bit.

This is all about how the Google authorisation server can send an authorisation code to your app (your macro) in a secure way. The VBA code that you're using for authorisation sets the redirect_url parameter to use the OOB option. In other words, it tells Google to display the authorisation code in the user's browser, from where they can copy-paste it over to your macro. This option used to be available to apps in 'Testing' mode only. Much of the discussion in this thread has been about how to do the authorisation another way (not using OOB) because the posters didn't want to use Testing mode.

It seems likely that Google is no longer allowing the OOB option, even in Testing mode. However, to make sure that's the case, I suggest you check that your app is using Testing mode. In your Google APIs and Services page, look under 'OAuth consent screen' > 'Publishing status'. If that says Testing, then the OOB option is no longer available, and you will have to use an alternative. If it doesn't say Testing, try using Testing mode, as that will be the easiest option.

The alternative discussed above is to create your own website, tell Google to send the authorisation code to that website, and then make the website display the authorisation code in the user's browser. The posters above created their websites using Wix. I haven't tried doing this myself. Obviously it's going to be more effort than the OOB method.

The option recommended by Google (in general, not specifically for VBA) is to have your app listen for the authorisation code on a web socket. This would avoid having to set up a website, and avoid the user having to manually copy-paste the code. I've just been googling for information on how to do this in VBA. It looks like it might be possible, but it won't be easy to get it working. At some point I might try it, as I might need it in the future.

Thank you for your quick response and assistance.

I had my consent screen set to internal, so I have changed it to external and left it in testing mode. That now allows me to login to Google without the OOB flow error and retrieve my authorisation code to paste into VBA.

But now I am getting an error in VBA from Google API saying "401, Unauthorized, invalid_client". This is right after entering the authorisation code, so it is still at the access token stage.

Sorry to be a pain, but any further ideas that might be causing this error? Or should I try and go down the website option?

[RichardWein]

It's good news that you were able to get past the OOB problem. That seems to mean that OOB still works in Testing mode. If you're OK with continuing in Testing mode, there's no need to use a website.

At the moment the only thing I can suggest for the new error is to double check that the Client ID in your VBA code matches the Client ID in your Google APIs and Services console (under 'Credentials').

[hnsywnwtsly]

@joebob99
I just saw your message, it goes to an email address that I rarely open.

I can't tell what exactly the problem is, but the first thing that jumped out at me is that you wrote that you created a Desktop OAuth certificate, my solution talks about a "Web application" type certificate, even though the application is indeed a desktop application, I create the certificate of the "Web application" type .
It's been a long time so I don't remember exactly what the differences between the types of apps are, I can only say that I work with it on a daily basis and it works great with no problems.

Another thing, for an app in test mode, it has a significant disadvantage in that you will have to re-verify every week, whereas if you submit the application for publication (and stop at this stage, you do not need to progress beyond changing it to a status of submitted for publication) you do not need to verify more than once One, it has been working for me for almost two years.
For example, I work with an application that sends and reads emails not only as a response to customer interaction, in this case if its refresh token is not valid the application is stuck, so it is very important to change the application to publishing mode.
I'd be happy to help if you have any more questions.

[joebob99]

Thanks @hnsywnwtsly appreciate your response. I will explain what I have done so far:

Saved your XLSM file to my PC.

Created a new project for "VBA Gmail" in Google Cloud/APIs and Services.
I have added the Gmail API. I have confirmed that it is enabled.
Created an OAuth consent screen, with User type = External, currently in Testing mode. I have added my gmail address as a test user. NB: My business domain is managed by Gmail, so I have entered it, not an @gmail.com address.
Created credentials of type "Desktop"

I have entered the Client ID and Secret from Google into the public const on the OtherStuff module.
I then try and run the Test sub in the Gmail module. I update this to use my email address as the Sender Address and a To Address of my personal email address.
When the process gets to Set auth_Response = ExecuteRequest2(auth_Request, auth_TokenClient), auth_Response.StatusCode returns as 401. The full error message is HTTP code: 401, Unauthorized error: invalid_client, error_description: Unauthorized

If I try and set the credential type to be "Web application", however I need to enter an Authorized redirect URI. I thought that might be urn:ietf:wg:oauth:2.0:oob which is mentioned in the VBA code, but it would not accept that.

Unfortunately I am not a developer, but I am usually useful enough to be able to adapt other peoples efforts and make them work. I read the earlier comments about Wixsite, but that seems beyond my capabilities.

If you cannot provide any further help or assistance, no worries and thank you for your time.

[hnsywnwtsly]

@joebob99
You will have to enter your site's URI and create a "Web application" certificate, you cannot skip this step.

The whole idea of wix (and of other hosting sites, you don't just need wix) is that you don't need to be a developer to set up a site with them, you'll be surprised how easy it is to set up a site with them, and it's also free.
In fact, I think it's easier than manipulating VBA code.

I honestly don't mind giving you my website address, which you enter under "Authorized redirect URI", but if I were you I wouldn't accept the offer, I wouldn't want to depend on someone's website that I don't know and it can stop working without warning.

[zgrose]

Just as a sidebar comment, OAuth in general is kind of a pain to do in VBA (if you restrict yourself to VBA and widely available MS DLLs) because of the lack of a decent HttpClient class. For my app, I found using the Device Code flow is usually the most suited to VBA apps. This is the kind of flow that you go through when authenticating your TV apps on your phone.

Once you have your JWT, everything is smooth. It's just that first part where VBA is showing its age.

Good luck!

[joebob99]

@hnsywnwtsly thank you, you were right, the Wix site was easy to use.

1. Created a dummy site and ensure it is published.
2. In browser, go to Googler Search Console and add a new property (URL prefix). being the wix website URL https://XXXX.wixsite.com/YYYY
3. Select the other method of "HTML tag" to generate the code.
4. Back on the Wix site, under Sites Pages and Menu, select Home. Settings, Advanced SEO, Additional tags, Add new tag and paste in the code from Google.
5. Back in Google Search Console, click to Verify.
6. In the VBA code, update the Private Const LOGIN_REDIRECT_URI As String to be the wix website URL
7. On Google Cloud/APIs, create new credentials of type "Web server". Enter your wix website URL as the Authorized redirect URIs

So I was able to successfully send a test email after doing this. I didnt bother with having the authorisation code appear on the page. Seeing it in the URL was sufficient for what I need this for.

Thanks again to you and others for your responses that helped me get here. I will have a look at the publishing part in the near future, to see if I can get that part working to avoid the need to re-authorise every week.

[hnsywnwtsly]

@zgrose
I didn't understand your meaning "using the Device Code"

[zgrose]

There are several ways to authenticate in OAuth called flows. One is called Device Code where a code is displayed in your app and the user enters that code on web page.

I'm not recommending you rewrite your app, I was just mentioning it for future readers to consider. It may not even be available for your Google mail scenario.