Sending Email from FileMaker Using Mandrill

By Daniel Wood, 21 March 2014

Introduction

Sending Email from FileMaker is a common feature these days. With the Send Mail script step this has made it even easier as you can either send via an SMTP server or your Mail Client. While good, these methods do have restrictions such as a one attachment limit and no HTML support if using via SMTP server. Also if sending via your Email client then it becomes difficult to capture sent mail in your system and not all mail clients work the same.

Developers have aimed to circumvent these issues by using FileMaker Email plugins capable of HTML and multiple attachments among other things. Plugins are a good alternative but require some setup and configuration of SMTP details, authentication, and are at the mercy of firewalls and other restrictions that may prevent Email from being sent.

mail

Mandrill - What is it and why do I care?

What is Mandrill?

Mandrill is different. It is basically the E-Mail sending side of MailChimp that has been spawned off to become its own site. Because of this, Mandrill also offers a wide range of features that you would expect from an Email campaign management site such as tracking of Email opens and clicks, full statistics on mail sent, bounce management, as well as a raft of other customisable settings.

And why do I care?

You can use Mandrill to send Email via standard SMTP, or via their API. Even if you wish to use it just via SMTP alone it makes sense. The Mandrill SMTP credentials are reliable, secure, and work anywhere (subject to firewall restrictions of course). In addition, you get all the benefits of comprehensive email tracking and settings to help customise the experience.

In this article we will be covering sending Email via Mandrill by using their API. The benefit of this method is the Emails are sent using an HTTP POST request on port 80 so unless you work for a totalitarian company which has blocked the internets then you should be guaranteed that your Emails will send every time.

There is always a catch, what is the catch?

No catch I promise! I may sound like I work for Mandrill but my enthusiasm is based solely on using Mandrill now on 4 separate projects and having great success on all of them. Best of all Mandrill is free (now I have your attention!). With a free account you are given a starting limit of 12,000 Emails per month, with an hourly limit of 250.

Mandrill works using algorithms that fairly allocate Email send capacity based on the type and quantity of Email that you send. If you are sending legitimate Email and not spam then you will begin to notice your daily and hourly limits increase over time - though for most FileMaker solutions these starting limits are more than enough.

mandrill

Setting up a Mandrill Account

How do I create an account?

Go to http://www.mandrillapp.com and sign up for a new account. Account setup is really easy - all you need is a unique Email address and password. If you intend to use it for multiple solutions then it would be good practice to create a new account per solution - 12,000 emails per day sounds like a lot but you don't want to run out. Accounts are also free so there is no reason not to.

mandrill 1

Generating an API Key

All Email you intend to send via the API must be sent with an API Key. The key identifies which account is sending the Email. Once logged into your account select SMTP and API Credentials from the settings menu. Here you can create as many API keys as you wish for various purposes. You will also find the SMTP credentials if you prefer to use those.

mandrill 2

Configuring your account for use

Mandrill is packed with configuration settings and tools for managing sent mail. These include things such as click tracking, blacklists, whitelists, custom metadata and webhooks to name a few. We won't go into depth on these here, but we will cover a couple of useful settings found in the Sending Options area.

  • Generate plain-text from HTML Emails (and vice versa). This is a useful option for mail client compatibility. Mandrill will automatically generate either HTML or Plaintext from your Email.
  • Expose the list of recipients when sending to multiple addresses. You can send an Email to multiple recipients in a single request, however you may not wish for all recipients to know who else got the Email. Use this option to show/hide the full list of recipients from others.
  • Send a copy of every Email to this address. A great feature for initial deployment. If you are concerned about Emails not being sent, then you can be CC'd in on everything sent using this option, or use it as a secondary log of everything sent out.
  • Forward bounce notifications to this address. If knowing whether an Email reached its destination is important, you can have Mandrill forward a special bounce response to a nominated Email address. We have used this in solutions where we use a plugin to receive these Emails back into FileMaker and process the bounce accordingly

 

mailchimp

Integration with FileMaker

What do I need to add to FileMaker

From this point on we are going to refer to the Mandrill example file that is included with this article.

Download the example file

There are 3 key pieces of Mandrill information that you need to put into your FileMaker solution:

  • The API Key, you can get this from your account.
  • The URL to which you will send the API request. Mandrill uses a REST API where you send to various URL's your request depending upon what you are trying to do. For sending Email, the URL is https://mandrillapp.com/api/1.0/messages/send.json
  • The request code into which you will place your email information. Best practice is to define the template and substitute placeholders for actual data.

All of the URLs and request formats for the API can be found in the documentation here. We will be sending requests using JSON format, so we use the .json endpoint to the URL - however other formats are available such as XML and PHP.

You can hard-code the above directly into your send scripts, but a better way is to store them in global fields so they can be used system-wide and can be easily modified if required.

mandrill 3

Generating an Email send request

The next step is to transform your request template into an actual request that can be sent to the API. You will see in the screenshot above that our request template has some place-holder data in it at various points, eg. SENDERNAME, SUBJECT, and MESSAGE.

How you record that information in the database we will leave entirely up to you. For our example file we have an Email table onto which we will capture this information. For attachments we have a related Attachments table where each record stores an attachment in a container. It is then simply a case of substituting out our placeholders for actual data. To do this we have created a couple of helpful custom functions.

mandrill_createRequest

This custom function takes a number of parameters, each corresponding to something we will substitute into the request. We are also passing the request template as the first parameter, however if you wish it may be easier to include the template within the custom function, particularly if you only intend to use one template for the entire solution. The function is basically a glorified substitute command, but it does a couple of required things:

  • It will escape all required characters within the parameters, this is so that the request does not break as a result of prematurely terminated strings caused by a non-escaped characters.
  • It will call a helper custom function mandrill_attachmentCode to generate the required request code for attachments.
  • Because we are sending all Email as HTML, it will transform the message into HTML code using the GetAsCSS function if it detects it was not passed HTML code already.

EDIT: Thank you to Tim Dietrich, Stephen Dolenski and Michael Gaslowitz who all pointed out we also need to escape other characters in the request so as not to break it!

mandrill 4

mandrill_attachmentCode

You don't really need to concern yourself with how this function works, however it is such a cool function function it deserves some mention!

Custom functions can accept a container field as a parameter. However you cannot pass them a list of containers as the List() function is for strings only. We could define 1 parameter per attachment but that is really bad as a) we don't know how many attachments we will have, and b) if we have none or one then we have to define blank for all the other attachment parameters. So how do we process multiple container attachments with a single parameter?

The answer to this came from this Soliant Consulting article written by the very smart Wim Decorte. He noticed that a parameter to a custom function has a dual nature - you can either treat it as a normal value, OR you can treat it as a reference to a value. Basically, if we treat it as a reference then we can use it in conjunction with recursion and the GetNthRecord function to recurse through multiple related attachment containers and process each one. We are making use of the dual nature of the parameter, using it as a both a value and a reference. I think this is one of the coolest untapped features of custom functions!

Attachments are converted into Base64 and along with a file name and mime type are put into the correct request format.

I won't go into any more detail than that, feel free to dig deeper into how it works, and definitely check out Wim's great article on the topic.

mandrill 5

Enough with the stalling, send the damn Email already!

This is the easiest part. Now that we have our request generated from the custom function, all that is left to do is send it to the API to send the Email.

The Insert from URL script step may be able to be used to send the request. At the point of writing this, I am still working on determining whether this is possible, and seeking the help of fellow developers in getting this working with this script step. In FileMaker Pro 13, the step does support HTTP POST, but I'm still working on getting the syntax correct for sending. For now, we are going to use the BaseElements plugin to send the request. Thank you to Michael Gaslowitz for pointing out that Insert from URL does support HTTP POST.

The example file comes pre-packaged with the excellent and free BaseElements plugin from Goya Pty Ltd. This should auto-install on startup and prior to sending Email if not already installed. We use this plugin because not only does it have a working mac and windows version but it also has a 64-bit windows version compatible with FileMaker Server 12 and 13 for server-side sending.

Because plugins do not work on iOS you have a couple of options - one is to have FileMaker Server carry out the sending by using the Perform on Server script step. If your solution is not hosted then you can use the native FileMaker Send Mail script step and use the Mandrill SMTP credentials instead of the API. We are also working on getting the Insert from URL step working (see above).

Sending the request is now really easy and is a single function call:

BE_HTTP_POST ( $URL ; $Request )

Where $URL is the API endpoint for sending an email, and $Request is your completed request. Sending of a standard Email without attachments should take less than a second, and a little longer for attachments.

The API Response

After sending the request, the API will send you a response that contains useful information. A typical response will look something like this:

[<br />{"email":"daniel@weetbicks.com",<br /> "status":"sent","_id":<br /> "d54bb019417c491eb01fc8578c4641a5",<br /> "reject_reason":null}<br />]<br />

This is a JSON array of values of the following:

  • email. Where the e-mail was sent
  • status.This tells you the outcome. If the status is sent or queued then it succeeded. Emails without an attachment will send right away, whereas ones with an attachment will be queued - however they are typically only queued for a couple of seconds so you won't even notice. A status of scheduled means you have told Mandrill to send the message at a nominated date and time (you can define this as part of the request). If the status is rejected or invalid then something went wrong, and you can check the reason for more details
  • reject_reason. The reason the Email failed to send if the status is rejected.
  • id. This is a unique identifier assigned to the sent Email from Mandrill. This is useful to track the message via the online Mandrill dashboard, and also if you intend to process Email bounces (the bounce message contains the unique ID).

mandrill_parseResponse

We have included this custom function to help you easily obtain a specified parameter from the response. Pass this function the response, along with either the parameter keyword (or associated number) to retrieve the value. This is useful if you wish to run a test condition as to whether the email sent or failed.

mandrill 6

So in Conclusion...

We hope this article has given you a good overview of Mandrill, what it is and what its benefits are. Integrating Mandrill with FileMaker is dead simple. It avoids a lot of potential SMTP related issues such as firewall restrictions, Outlook Exchange problems, or simply increasing usage of your own SMTP server. It also contains a wealth of settings and tracking options for marketing/campaign related emails. We haven't even touched upon the comprehensive online dashboard for tracking sent email statistics, but it's all there for you to explore. Have fun!

Example File

Please find attached an example file. This file is provided to help you fully understand what is going on in this article. Note that FileMaker 13 is required to view this example file. The file also contains links to the Mandrill API documentation and website, as well as the custom functions mentioned in the article.

Download Example File

UPDATE:The example file has been updated on 23rd March with the following changes:

  • More escaping of request characters within the mandrill_createRequest function
  • added a mandrill_escapeData helper custom function to escape merge data in the request
  • I have replaced angle bracket for merge data with « and » respectively.

mandrill 7

Something to say? Post a comment...

Comments

  • Daniel Wood 13/09/2018 2:42pm (6 years ago)

    hi Stefan, apologies for the late response to your comment.

    Mandrill will work in FileMaker 11 but in order to use it you'll need to use the plugin method because the "Insert from URL" script step was first introduced in version 12.

    To use the plugin method will require a plugin capable of sending an http post, which is exactly what you'll find in the demo file. Given the age of this article, there's also a good chance the verison of Base Elements plugin contained within the demo file will work in FileMaker 11 (later versions may not).

    If the plugin does not work for you in FileMaker 11 I'd suggest contacting Goya directly (www.goya.com.au) for help on getting a version that will work in FileMaker Pro 11.

    But certainly from Mandrill standpoint, you should be able to use Mandrill in FileMaker Pro 11.

  • Stefan Miles 17/08/2018 2:49am (6 years ago)

    I use FileMaker v11. Would Mandrill work with this version of FileMaker or do the newer versions of FileMaker have some extra scripts that are required. Also does Mandrill create a log to show if the email has been received even if the receiver has not responded? Thank you in advance for this information.

  • Daniel Wood 09/12/2016 10:23am (7 years ago)

    Hi Andres,

    The mandrill_attachmentCode function is basically responsible for generating the small part of the overall request code that will contain attachment data. The key part of this code is the attachment must be in Base64 encoding. THe easiest way to achieve this is by having the attachment within the container field and then using the Base64encode function that is in FileMaker.

    The only way I know of that would allow you to send an attachment and keep it as a file at a known location on disk, would be prior to sending from FIleMaker, to have your script insert the file as a reference into a temporary container field, and then generate the attachment code for that container field. Because you insert as a reference, the file is not physically stored in the database and still resides on disk at the given path, and this also allows you to utilise the Base64Encode function to obtain the encoded version for sending.

    This way you can use the same function in the exact same way, the only difference being you temporary insert the filepath as a reference into the container for the purposes of base64 encoding it :)

  • Andres Figueroa 08/12/2016 4:59pm (7 years ago)

    Is there a specific file path that needs to be passed to the mandrill_attachmentCode function to allow for attaching files that do not exist in a container field, but exist in a specific location on the system using the Example database? I am trying to attach a single PDF to a single email, but I haven't been able to find any documentation or way of doing this. Any help would be appreciated.

    Great example file by the way.

  • Daniel Wood 16/03/2016 7:16am (8 years ago)

    hi Jimmy. Unfortunately I haven't tried to get Sparkpost working just using the Insert URL method and I'm not hopeful that method will work because in order to authenticate with Sparkpost API the key and content type have to be passed as headers in the post request.

    I have Sparkpost working fine using the BaseElements plugin and doing a call using BE_HTTP_Post, along with setting the headers first using BE_HTTP_Set_custom_Header, I intend to post an article very soon about how you can migrate from your Mandrill setup to Sparkpost, so keep an eye out for that soon.

  • Jimmy 16/03/2016 5:24am (8 years ago)

    Hi Daniel.
    I'm trying to make the change to Sparkpost using the url method. So far not good. I'm using several variations for this:
    https://api.sparkpost.com/api/v1/transmissions?Authorization=<<API KEY>>&recipients[address]=some@mail.com&content[from]=sandbox@sparkpostbox.com&content[subject]=Test&content[html]=<strong>Hi Jimmy</strong><p>You have just sent your first email through SparkPost</p><p>Congratulations</p>

    Have you got any luck so far?
    Thanks

  • Daniel Wood 11/03/2016 8:45am (8 years ago)

    hi Daniel, thanks for the comment. Yes I noticed this also, it's a shame about Mandrill but Sparkpost looks like a very nice alternative. I'm actually in the process now of migrating a client solution to use Sparkpost so may put an article up at the end of it as to how the migration can best be done for an FM solution.

  • Daniel 11/03/2016 7:45am (8 years ago)

    If you missed it, Mandrill is now recommending SparkPost: "SparkPost has offered to take on any departing Mandrill users and to honor Mandrill’s pricing for those users"

    SparkPost has a migration guide just for Mandrill users: https://www.sparkpost.com/mandrill-migration-guide

    Their free price tier includes 100,000 emails per month! And they claim to be committed to honoring that for the lifetime of any account. https://www.sparkpost.com/blog/my-promise-to-developers-sparkpost-pricing

  • Sam Sehnert 08/03/2016 4:44pm (8 years ago)

    If you've got several low volume clients, it's possible to use a single Mandrill account to share the financial burden of the $30 p/m.

    It does make the verification step a little more tricky, in that you have to get domain owners to forward you the verification email, but in all other respects it works well.

    You can also set up 'Sub accounts' if you're concerned and want to separate mail reputation, and track sending rates between clients.

  • Daniel Wood 04/03/2016 10:18am (8 years ago)

    hi Chris, Yes it's a bit frustrating for low volume FM projects. We have a few ourselves in which our clients will now have to pay the monthly fee. THere should be little/no impact on the implementation through using the API/URL methods, it's just the payment and having to verify ownership of sending domains.

  • Chris Van Buren 03/03/2016 6:37am (8 years ago)

    Based on https://mandrill.zendesk.com/hc/en-us/articles/217467117

    It seems that you have to pay:
    $20/month to Mandrill as Mailchimp add on.
    $10/month for MailChimp

    So, a total of $30/month. You get 25k emails for this which is no big deal if sending all those mails. It is a big deal for me though in that I am using it on a project which is quiet at the moment (and sending zero emails) but it will soon be operating (and sending a few hundred emails/week when active and 0 emails/week when quiet - weeks will vary). So the $30/month is a real problem for me. Grrrr. I would be happy with any PAYG plan, even one with a very expensive per email cost but this really doesn't work.

  • Daniel 25/02/2016 4:51pm (8 years ago)

    Mandrill is undergoing major changes this March and April. It is being merged into Mail Chimp and will require a paid account there. Seems pricing is increasing as well.

    Anyone changing services?

  • Dave Hobson 17/11/2015 1:48am (8 years ago)

    Daniel - thanks for this great article. It seems that Mandrill now charge after 2,000 trial emails (in total, not per month), and the minimum plan seems to be USD9.95 per month. This is probably good value, but worth mentioning - obviously more of an issue to some people than to others.
    Dave.

  • Paul Vincent 28/10/2015 12:02am (8 years ago)

    This is a fantastic article; many thanks for this Daniel!
    I'm having one problem though, which is that I can't get any of my fields to merge into the html message. Is there a step or function I'm missing or would the merged variables have to be included in the message via the Mandrill API's global_merge_vars ?

    Thanks again,
    Paul

  • Daniel Wood 12/10/2015 10:49am (8 years ago)

    Hi Daniel, flick me your e-mail address through and I may be able to help you out with this. You can e-mail via the envelope icon in the top right of the article (with the RSS, FB, twitter icons)...

  • Daniel 11/10/2015 4:24am (8 years ago)

    Anyone have a reliable method for incoming email to fmp? Besides pricey plugins?

    I don’t mean to change the subject, there’s just a lot of great contributions here, maybe someone can pint to a solution?

  • giuseppe pupita 06/10/2015 3:45am (8 years ago)

    One request and one comment
    1. the request: have you figured out how to send customized headers using SMTP ?
    I've read the details page
    https://mandrill.zendesk.com/hc/en-us/articles/205582117-Using-SMTP-Headers-to-customize-your-messages
    but I haven't jet managed to actually send and custom header

    2. the comment
    I've successfully managed to use webhooks to manage bounces, unsubscribes and the like
    It involves sending the data to the Runscope site (subscription required)
    Then you've to connect to the site (Base Elements plugin required to pass custon authorization header), retrieve a list of entries in the site (each entry is a webhook event), and retrieve the details of each event
    Everything comes in JSON format

    Do let me know if you're interested in the fine details

    Ciao
    g.

  • Alexander 22/09/2015 2:04am (9 years ago)

    Thanks for the post, template and compact explanation - saved me quite some time - thank you!
    I noticed a bug with umlauts, GetAsCSS escapes them with an ampersand and the ampersand remains a query parameter in the POST.
    mandrill_escapeData can be extended to handle this easily:

    Let ( [
    aBackslash = "\\\\" ;
    aQuote = "\\"" ;
    aBackspace = Char ( 8 ) ;
    aFormFeed = Char ( 12 ) ;
    aCR = Char ( 13 ) ;
    aLF = Char ( 10 ) ;
    aTab = Char ( 9 ) ;
    aAmpersand = "&" ;
    theData = Trim ( theData )
    ] ;
    Substitute (
    theData ;
    [ aBackslash ; "\\\\" & aBackslash ] ;
    [ aQuote ; "\\\\" & aQuote ] ;
    [ aBackspace ; "\\\\" & aBackspace ] ;
    [ aFormFeed ; "\\\\" & aFormFeed ] ;
    [ aCR ; "\\\\" & aCR ] ;
    [ aLF ; "\\\\" & aLF ] ;
    [ aTab ; "\\\\" & aTab ] ;
    [ aAmpersand ; GetAsURLEncoded ( aAmpersand ) ]
    )
    )

  • Daniel 18/09/2015 8:52am (9 years ago)

    Forgot to add that the only character that is still trouble is an ampersand, so the percent encoding must be used as noted above. I've added it to the mandrill_escapeData cf, ["&" ; "% 26"] (without the space)

  • Daniel 18/09/2015 8:26am (9 years ago)

    I'm using this via Insert from URL...

    The Insert from URL has encoding issues...some have been mentioned here. One major issue I have is that when you convert a field via GetAsCSS, it encodes special characters (common ones like double quotes", single quotes', etc.) as HTML entities (like &quot;). The Insert from URL function chokes on this and sends cutoff, mangled, POST requests when it encounters that entity.

    I am now using the following solution: first grab the field via GetAsCSS, then run it through a custom function to convert HTML entities BACK to their actual characters. This allows Insert from URL to do it's own encoding...

  • Marty Enerson 24/07/2015 9:48am (9 years ago)

    One final thing... did someone complete sending to a CC or multiple people not via the plugin? I see you can on the Mandrill website, and I can do it via their JSON example, but not sure how to edit the Custom Function to make this work.

  • Marty Enerson 24/07/2015 4:47am (9 years ago)

    I must be missing something. I am trying to send a basic HTML email via insert URL with bold and an image (using img src).
    I can send emails without issue.
    I think I am supposed to put *html* at the beginning of the message, which removed the auto stuff... and it works when I "bold" something, but it puts the "*html*" in the message and i get a broken image icon for the img src.
    Do I need put all the html tags in the email? like html, head, title, body etc?

    I do notice that it is putting "\\" in front of my quotes around the image url.

  • Jorge 12/07/2015 1:27am (9 years ago)

    If someone has successfully implemented this solution with a Mandrill Template please share with us how it was done.

    Thanks in advance.

  • Ron 24/06/2015 8:16am (9 years ago)

    I need to send a simple mandrill template from filemaker, any help would be appreciated. I already have mandrill setup with a template.

  • joost kingma 18/06/2015 2:51am (9 years ago)

    send me an email: support at dataclip dot nl

1 2 3 4 5

RSS feed for comments on this page | RSS feed for all comments

Categories(show all)

Subscribe

Tags