Integrating FileMaker and Gmail Part 1 – Sending HTML Emails

This is the first in a series of articles on using the Gmail API from FileMaker. We’ll provide free example files for each post and these examples will all use the native Insert From URL script step so there won’t be any plug-ins required.

In this article, we’re going to look at sending HTML emails. In future articles, I’ll examine sending large emails (35MB) with multiple attachments, resumable uploading, and managing threads. This approach provides a first-rate email experience that is not available using the native Send Mail script step. And, since the Insert From URL script step is supported natively across the whole FileMaker platform, the techniques shown here work on the Server, Go, FileMaker Cloud and WebDirect. This technique requires FileMaker 16 or higher since we’ll be using the advanced cURL options introduced in FileMaker 16.

Here’s an introduction. Step-by-step instructions and an unlocked example file follow:

Background and Choosing the Gmail API

I recently had the opportunity to build a comprehensive Gmail integration into a client’s system. The main reason the client wanted to go the Gmail route was to have seamless integration with their native Gmail experience; at first they weren’t that interested in threads or large attachments, but those features soon became important once they started emailing from within FileMaker.

Before we started, the client and I discussed which route we wanted to take, like using a plug-in or rolling our own. Fortunately, right about this time, dbservices published a great blog post and example file on integrating with the Gmail API. This article provided great techniques for authenticating to Google as well as creating an inbox in FileMaker that would import Gmails with their attachments. Basically, they had already done a lot of the work we needed. I implemented their authentication and inbox routine and then added the techniques I’m going to share here.

Example File

Please download the example file FM2Gmail for a working demo. This first version just covers the sending of HTML emails and we’ll be providing later versions of this file in future articles.

Integrating FileMaker and Gmail - Example File
FM2Gmail Example File

Getting Started / Authentication

Before we can send any emails, we’ll need to authenticate our app to Google. Our example file has adapted the authentication routine provided by dbservices where you’ll find more details on setting up a Google project for the OAuth values. Specify Web Application for your project so the redirect URI value is available. Once you’ve got your Client ID and Client Secret you can enter them into the fields on the OAuth settings page.

Integrating FileMaker with Gmail - Settings
Settings Popover
Authenticating Google Account within FileMaker
Authentication Set-Up

With your Client ID and Client Secret entered, you can click the “authenticate to gmail” link on the left. This opens a web viewer in a card window where you can log-into your Google account and authorize access to your Gmail data.

Authorize FileMaker to access Gmail
Authorize your app to access Gmail data

When you’ve successfully authenticated and authorized you’ll see your email address take the place of the “authenticate to gmail” link.

Important!

At this point, the example file has an authorization Refresh Token which we’ll use to automatically authenticate when the file opens in the future. So take some care with the file once it’s authorized as it will have access to your Gmail when opened. You can de-authorize the app access at any time by visiting “My Account” from your Google settings and then finding Apps with account access section. From there you can de-authorize the app.

Integrating FileMaker and Gmail Authorization
Remove app access from Google

Sending an HTML Email from FileMaker

Now that we’re authorized, the new email button appears, and we can compose and send something. I was going to use a card window for the compose layout, but I wanted to expose the formatting bar so went with a Floating window instead. (Exposing the formatting bar is an old FileMaker Hacks trick). Showing the formatting bar lets you easily add some styles to our text; those text styles will get transformed into styled HTML.]

Compose HTML email in FIleMaker
Composing an email with styled text

I also added a simple emoji picker for fun. These are just button bars that are passing the emoji characters into the text. Since we’re just using the unicode characters, the emojis are a little spartan on Windows but work fine for a simple demo.

Emoji Picker for FileMaker
An Emoji Picker

We can then send our Email and when we look at it in the recipient’s inbox we see that the styling has come across as an HTML email.

 

Under The Hood

To send an email via the Gmail API we first need to create the email form following the RFC822 standard. This multipart form may be familiar to many of you already and looks like the following:

MIME-Version: 1.0
From: jason@seedcode.com
To: jason@seedcode.com
Cc:
Subject: Testing 123
In-Reply-To:
References:
Content-Type: multipart/mixed; boundary=01386EE1-2BC5-4670-8AC1-2C05A5CBA90D
--01386EE1-2BC5-4670-8AC1-2C05A5CBA90D

Content-Type: multipart/alternative; boundary=5C6478EC-EDEA-43CF-AAC4-4B5AE4BC0649

--5C6478EC-EDEA-43CF-AAC4-4B5AE4BC0649

Content-type: text/plain; charset=UTF-8

Hello World

--5C6478EC-EDEA-43CF-AAC4-4B5AE4BC0649

Content-type: text/html; charset=UTF-8
Hello World
--5C6478EC-EDEA-43CF-AAC4-4B5AE4BC0649-- 
--01386EE1-2BC5-4670-8AC1-2C05A5CBA90D--

In our sample file, the Script “Create RFC822 Form” is used to build the above. The RFC822 standard does call out using CRLF (carriage return + line feed) for the line breaks, but I had better luck just using the line feed or Char(10) in FileMaker terms. This allowed for leaving header values like CC blank and a more straightforward template.

Notice that there are two parts to this form that contains the message. The first is the text/plain section where we pass the plain text from our field. Since this is plain text, all of the formattings we applied is gone. Below, in the text/html section, we pass in the HTML representation of our message with any appropriate styling. Even though most modern email clients and browsers support reading HTML messages, it’s considered best practice to pass the plaintext message as well.

To generate the HTML in our sample file we’re just using FileMaker’s GetAsCSS() function. This actually works very well for a basic example and translates all of the formatting options except for any fonts applied. If you need a more robust HTML editor then something like FMEasyHTML from Tim Dietrich or Editor by Geist Interactive are worth checking out.

The Script “Send Email – Simple”

Once we have our form built, then we can POST it to the Gmail API using the Insert From URL script step. The example file does this in the script “Send Email – Simple”. Here’s what that script does:

Our target URL is:

https://www.googleapis.com/upload/gmail/v1/users/me/messages/send?uploadType=media

This is the endpoint for our simple upload example and it supports an email up to 5MB. In the next article, we’ll be looking at a different URL that allows for up to 35MB with multiple attachments.

Now we just need to set a few cURL settings for our headers. (The exaple file writes the access token to a global variable when authentication is complete).

-H "Authorization: Bearer <<$accessToken>>"
-H "Content-Type: message/rfc822"

And then specify our request body which is the rfc822 form we created above.

-d "<<$requestBody>>"

And also capture the result headers to a variable in case we need to examine them for errors.

-D "$resultHeaders"

That’s it!. This is all done in the script Send Email – Simpleso you can step through there to see how it’s put together.

If there’s an error in the POST, an error code and message will be returned in the response body and headers. Otherwise, you’ll get a simple JSON response like this indicating the email has been sent.

{
  "id": "167180923a6f9ac3",
  "threadId": "167180923a6f9ac3",
  "labelIds": [
    "UNREAD",
    "SENT",
    "INBOX"
  ]
}

Our script grabs the id and the thread id and writes those to the email record in FileMaker as we’ll be looking at using those in a future example.

Conclusions

Even though there is some extra effort involved in accessing the Gmail API, like authentication, understanding the syntax, and cURL requirements, the benefits provided by the API are well worth it. Having techniques that work across all aspects of the FileMaker platform to extend your app’s email functionality can provide massive value to your customers.

Additionally, as FileMaker developers, our understanding this kind of integration is going to be more and more expected as FileMaker continues to focus on integrating with other platforms. From my experience, every integration I do with a new API makes the next one easier. Even if you don’t have an immediate need for Gmail integration, running through this process and understanding how this API works with something familiar, like email, can only make you a better FileMaker developer.

Here’s the next article in this series:

Integrating FileMaker and Gmail Part 2 – Sending Large Attachments

Featured Posts

Follow Along

Stay up to date with the latest news & examples from SeedCode

34 Comments

  • I have tried to follow these and DB services directions and am running into an issue. When I authenticate I don’t get a button to create and email. I am also getting a slightly different screen at the authentication. Mine only has the following –
    This will allow FM GMail Integration to:
    Read, compose, send, and permanently delete all your email from Gmail

    I don’t have Know who you are or View your email address. I am wondering if this is something to do with the scope that I set but I can’t figure out how to edit that.

    • KC Embrey

      Hi Pete. Sorry for the late reply.

      We’d be happy to help troubleshoot this issue. Yes, it’s possible that the permissions set in the Google Cloud Console aren’t meeting the full requirements. Per the DB Services article, you need to enable List Messages, Get Message, Get Attachment, and Modify Message.

      If that doesn’t work for you, we’d be happy to set up a quick screen share to look into this together. You can reach out at support@seedcode.com to set that up.

      Sincerely,

      KC

  • Hi Jason, thank you for the excellent guide. Using your v1.31 I have ran into an issue that results in an error when attempting to send out an email (Filemaker error: 5). Using the script debugger I found where the issue was happening however I wasn’t able to solve the issue myself. I was hoping you could provide some insight into this.
    Thank you.

    • Hi Colin,

      Thanks for posting, it looks like Google has changed their response headers a bit, so we’re not getting the proper URL for the PUT anymore. I’ll work on getting a new demo file up there, but in the meantime, I think this fix should work:

      On line 65 of the script Request Resumable Upload URL, change the GetValue parameter from 3 to 4, which should grab the correct URL from the response headers.

      Let me know if that fix works and I’ll update this thread when a new file is up.

      My best,
      Jason

      • Hi Jason, thank you for the quick response. Your fix to change the GetValue parameter from 3 to 4 solved the issue and its working again.

        Thank you again!

  • Najee Simmons

    Thanks for this awesome post/file. — Is it possible to automate a script which triggers the authentication process and sends an email? Is there a way to authenticate without having a “live” user choose a gmail account/enter a password in the web-viewer which pops up when pressing the “Authenticate to Gmail” button?

    • Jason Young

      Hi Najee,
      I’m glad to hear you like it. There’s no way I know to automate the log-in process as Google now requires that OAuth flow, you used to be able to do this with Basic Authentication, but Google doesn’t allow this anymore.

      I do want to point out that the authentication flow that needs to be done by a person should just be a one-time process as once you have the refresh token, you don’t need to re-authorize. For example, I’ve set this up so the server can send out emails on a timer after I stored the refresh token somewhere the server could use it to get an access token.

      Please let me know if that helps and if you have any other questions.

      My best,
      -Jason

  • Do we know if FMS 19.5.2 fixes this issue?I am getting the following error from FM19.5.1 platform (FMS and FMP).

    {
    “error”: {
    “code”: 400,
    “message”: “Recipient address required”,
    “errors”: [
    {
    “message”: “Recipient address required”,
    “domain”: “global”,
    “reason”: “invalidArgument”
    }
    ],
    “status”: “INVALID_ARGUMENT”
    }
    }

    • Jason Young

      Hi Jeff,

      19.5.2 should resolve the issue as they reverted the quote functionality to its previous state.

      Thanks,
      Jason

  • Hi there —
    Something seems to have changed in how Google is processing the email — the little app I built using this tutorial starting returning an error this morning.
    I re-downloaded the sample provided on this page to check, and it is also returning the same error:
    “400 Recipient address required”.

    Would be grateful for any insights you might have.

    Thanks!
    -Simon

    • Jason Young

      Hi Simon,

      Thanks for posting this and for contacting support. I think you’re all set from support, but let me know if otherwise.

      Yes, FM 19.5.1 has changed the behavior of the Quote function which has caused an issue with the cURL requests we’re making to the API. Our understanding is that this will be fixed in 19.5.2, which should be released soon, so folks using this technique should hold off on 19.5.1 and upgrade to 19.5.2 when it’s released.

      In the meantime, if you’ve already updated to 19.5.1. The fix is to change the script that sends the actual payload to not use the Quote function to a simple expression that basically works how the Quote function did before the change in 19.5.1. This script and line number will be different depending on which sample file from the 3 different blog posts you started with, but will have the Text “Send Email.” You’ll then locate the line that added the $requestBody variable to the $cURL variable and replace the expression with the following:

      $cURL & ” -d ” & “\”” & Substitute ( $requestBody ; Char(34) ; “\\” & Char(34) ) & “\””

      Don’t hesitate to reach out to us at support@seedcode.com if you need any further assistance with this issue.

      My best,
      -Jason

      • Gary Lowell Sprung

        OMG! I was getting that same 400 error. Downgraded to Filemaker Pro 19.4 and now your app works great. I’m on the way to restoring client’s Gmail sending . So helpful. Thanks.

  • A Very Frustrated User Who Just Wasted Like 3 Hours On This For Nothing

    This process appears to have changed. Signing up for the Gmail API no longer gives a blank for a callback URI when you’re setting OAuth up, although the back end still requires it, so the authentication process must fail and you cannot ever log in.

    Also, the DBservices article you linked to collects email addresses and then does not ever send a link to download the file as promised. I tried with 3 different email addresses, they never sent the link. Not cool.

    • Jason Young

      Hi Frustrated,

      I’m sorry to hear that you’ve been having trouble setting this up. I just created a new Project from the Google Developer Console. I did need to go through the consent process first, but once I did that I was able to create an OAuth client ID and then select Web Application as the type. I was then brought to a page for Adding Authorized redirect URIs. You do need to click the Add URI button and then a field for entering the Redirect URI is there.

      If you send me an email at support@seedcode.com, attn Jason, I’d be happy to make a quick video walking through the above process and where to enter the redirect URI for your project.

      I’m not sure what’s going on with DB Services, but I asked for a new download and got it in about five minutes, so I’d be happy to send that file to you as well if you email me. It is a zip file, so maybe it got bounced or ended up in junk as that can certainly happen with some mail servers and zip files.

      Hope to hear from you,
      -Jason

  • Renier

    Hi

    Thanks so much for this, I know this is very late from when you posted it, but I seem to be struggling to log in / authenticate I keep getting the following.

    This browser or app may not be secure.
    Try using a different browser. If you’re already using a supported browser, you can try again to sign in.

    I have ‘access to less secure apps turned on’ but still cant authenticate. any assistance would be greatly appreciated

    • Jason Young

      Hi Renier,
      I’m sorry we missed this comment. I haven’t run into this and we typically recommend making these internal projects with an https redirect URI and that shouldn’t trip any warnings. If the project is external, then you will need to get it verified/approved by Google to bypass these warnings.
      hth,
      -Jason

  • Congratulations, great job! This is my scenario: several people work on the same filemaker, they all have their email in G Suite. I generated the Google project and OAuth values. I would like that the various users can send emails from Filemaker, where each user’s email and password are registered, directly without going through authentication or anything else. Is it possible?

    • Jason Young

      Hi Luigi,

      I hope all is well. The sending email address is bound to the tokens, so the users would need to go through the OAuth process once to get their own refresh token. With Google, the refresh tokens generally don’t expire unless they’re revoked, so it should be a one time process.

      Another option would be to use SMTP rather than https, which has been supported by FileMaker’s Insert From URL since version 17. It’s a similar process as we show here, e.g. building the form, but with SMTP, you can do the authentication with the Google user name and password instead of a token, but this means you’d need to store those credentials in FileMaker, as opposed to storing the Refresh Token. There’s a good post on doing this with SMTP here:
      https://www.soliantconsulting.com/blog/html-email-filemaker/

      hth,
      Jason

      • Tom Z

        Hi,

        This is a great article on how to use an API and OAuth
        However is it the point to integrate OAuth with FileMaker so user are able to send email using Gmail? as of today, smtp is using old fashion authentication which is user name and password. This method is not acceptable this days, especially with Gmail. To use smtp and username -password for authentication users will have to turn on Less Secure Apps in their Google account. While this was available for the last 2 year, starting from 15 Feb 2021 the LSA are no more support and Gmail users will no longer be able to turn on this feature in their Google account. Therefore it might be not possible for users to send emails from FileMaker.
        I believe by integrating an API and OAuth will allow to not only have better “Gmail” like experience but actually to be able to send email and comply with latest security protocols.

      • Luigi Corte Rappis

        Great, I think that’s exactly what I need. Thank you!

  • Clem

    Hi Jason,
    For your information, I have also now determined that if a reply to an email sent from FM is sent in plain text that the email is not received in FM at all. Perhaps plain text strips out the threadid.

    Best,
    Clem

  • Frank

    Overall, excellent work. However, it seems that replies to FM with attachments are not processed correctly. The thread count is updated while the received email text is not shown nor are any attachments i.e. the incoming reply appears to be an empty email.

    • Jason Young

      Hi Frank,
      Thank you for checking this out. I didn’t spend much time on receiving emails, as the focus of these examples was on sending, and I took a pretty simple approach of just rendering them in a web viewer. For a more detailed look at receiving emails, I would look at the dbServices article that kind of kicked this off, which focuses more on receiving them:

      https://dbservices.com/articles/filemaker-gmail-integration/

      We do look at threads on the 3rd part of this series, so that could help the receiving issues too.

      https://www.seedcode.com/email-threads-filemaker-gmail/

      Let me know if that helps.
      -J

  • It seems the Web Viewer to Authenticate a Gmail account is not working in WebDirect. Is this the case or am I doing something wrong?

    On the FM2Gmail file I turned on the WebDirect Extended Privileges, but when I try to “authenticate to gmail” I get a white screen.

    • Jason Young

      Hi Joseph,
      Yes, the authentication part of the demo file won’t work in WebDirect as you can’t “scrape” the tokens using GetLayoutObjectAttribute in WD. You would need to authenticate in Pro or Go and once you have the refresh token, then I believe most things will work in WD.

      Insert From URL is fully supported in WD, so all of the send and receive steps are supported.

      hth,
      -Jason

  • If you’re looking for a way to send responsive HTML email templates or newsletters from Gmail without the help of a designer, developer or coding knowledge, you should check out ContactMonkey’s solution for sending HTML emails from Gmail.

    They have engineered a way in which you can easily create responsive HTML email templates with their easy to use drag and drop builder and then send straight from your Gmail to your mailing lists.

    Here’s a step by step guide on sending HTML from Gmail:
    https://www.contactmonkey.com/blog/send-responsive-html-email-gmail

    This is really great for internal communicators or internal marketers who want to send and track responsive employee newsletters from Gmail!

  • Joe Byrne

    Excellent. When is Part II coming? Can’t wait!

  • As always a great SeedCode video that enriches the FileMaker community with new options to explore. Thanks for sharing these techniques.

  • Jim

    Thank you for the article. It is something I’ve considering doing but was not sure where to start or how much was involved.

  • Daniel Farnan

    This is a nice implementation, Jason – thank you for providing it!

    I would note, however, that the Gmail API documentation (https://developers.google.com/gmail/api/guides/sending) specifies using the RFC2822 standard and not the RFC822 mentioned in your article. For most purposes there is a negligible difference between the two, but it’s worth paying attention to this sort of thing in case those rare errors show up.

    I also note that RFC2822 was superseded in 2008 by RFC5322 (https://en.wikipedia.org/wiki/Email#Internet_Message_Format), so the Gmail API may be upgraded at some point to make use of the new standard.

    • Jason Young

      Hey Daniel,
      Thank you very much for pointing that out. I was going by the Content-Type headers in the docs on uploading media that all still reference message/rfc822. I’ll make some edits to the original post and to my upcoming ones to make this more clear.
      Thank Again!!

Leave a Reply

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

Check out some of our other posts ...

Suggesting Appointment Slots

Show Available Slots that Match Multiple Criteria Schedulers often look for gaps in their schedules to find the open resources for each opportunity. But sometimes,

Introducing Draft Settings Mode

Following up on this idea that people stretch themselves when they feel a little safer, we’ve been very focused on the customization experience for DayBack

New Longer Timescales for DayBack

Resource Scheduling Swimlanes You can now extend the pivoted scheduling view in DayBack to show items by week instead of solely by day. This lets

FileMaker Summer Camp – Recap

Unconference Sessions If you missed Pause in October, here’s a look at the sessions that attendees hosted. All the sessions are listed in this post

COMPANY

FOLLOW ALONG

Stay up to date with the latest news & examples from SeedCode

© 2024 SeedCode, Inc.