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.
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.
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.
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.
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
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.
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.
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_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.
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.
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.
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.
Something to say? Post a comment...
Comments
Erez Ater 18/06/2015 2:48am (9 years ago)
Hi
thanks, how can i transfer to you a file that we have a problem with?
thanks
joost kingma 18/06/2015 2:47am (9 years ago)
Hi Erez,
I send a test mail through Mandrill with a tab attachment without a problem.
Regards,
Joost Kingma
Erez Ater 18/06/2015 2:12am (9 years ago)
Hello,
let's start great work, that working nice.
i do have some problems and maybe you can assist, i am adding a TAB file as attachment, but this is getting corrupted when sent to the user.
what can be the problem?
Jordan McC 13/05/2015 3:49am (10 years ago)
Should have invested more time reading the comments!
updated the custom function "mandrill_createRequestInsert"
------- GetAsCSS ( message & "¶¶" )
It's working now -
Thanks again Daniel!
Jordan McC 13/05/2015 3:04am (10 years ago)
Wow what a fantastic article!
However, I think I'm missing something.
When i add style to a message using the example file and select "Send e-mail" button while on the "Insert from URL" tab the email that is sent does not have the style applied when received. When i send the same message on the "Plug-in" tab the message sent does contain the style - Is there a trick to get the style to send when on the "Insert from URL" tab?
Thanks and wow again!
Daniel Wood 08/04/2015 9:34am (10 years ago)
Hi Joost, thanks for the comment! Yes if you could e-mail it to me daniel at teamdf dot com then I can put it up on the article. Actually there has been quite a lot of discussion that has continued in the comment section after this article with some very good ideas so I'm considering doing a followup article where some of these are covered, and possibly doing an updated demo file with things everyone has discovered.
joost kingma 08/04/2015 12:42am (10 years ago)
Hi Daniel,
I wrote a custom function that can be used to send an email to multiple recipients (using to, cc,bcc), using the be plugin.
I have added this to your demo file and I would like to share this, but do not see an option to upload a file here. Therefore, I do not know if this is appropiate, I will mention here that anyone interested can send me an email: support at dataclip dot nl and I will send them a modified demo file.
Joost Kingma
Chris Van Buren 28/03/2015 10:41pm (10 years ago)
Thanks Joost! I'll have a go at that.
Joost Kingma 28/03/2015 3:27am (10 years ago)
Chris, I noticed your comment on inline images. I do not know if you can use this but I have modified the createrequest custom function for my customer to include a header and footer in the emails, see below. The header and footer are properly formatted URL's that point to the location of the image (i.e. <img /> )
mandrill_createRequestPluginImage (request; apikey; sendername; senderemail; recipientname; recipientemail; subject; header; message; footer; attachments)
/*
Purpose: Generate Mandrill request Code
Parameters: request - The Mandrill Template request
Parameters: apikey - Mandrill API Key for sending
Parameters: attachments - A list of one or more container fields to send as attachments
Parameters: rest - The rest of the parameters are pretty self explanatory.
Assumption: User is passing all valid parameters.
Assumption: If the user is passing a message beginning with <HTML> we assume they are sending html content, otherwise we mark it up
Returns: A completed mandrill request that can be used to send an e-mail via the API.
History: Created by Daniel Wood 20 March 2014
History: Modified by Stephen Dolenski 22 March 2014 to escape Carriage returns
History: Modified by Joost Kingma 12 February 2015 added header and footer to include inline image
*/
Let ([
request = Substitute ( request ; [ "\\\\" ; "" ] ; [ Char ( 12 ) ; "" ] ; [ Char ( 13 ) ; "" ] ; [ Char ( 10 ) ; "" ] ; [ Char ( 9 ) ; "" ] ) ;
HTML = If ( Left ( message ; 6 ) = "*HTML*" ; 1 ; "" )
];
Substitute (
request ;
[ "«APIKEY»" ; mandrill_escapeData ( apikey ) ] ;
[ "«SENDERNAME»" ; mandrill_escapeData ( sendername ) ] ;
[ "«SENDEREMAIL»" ; mandrill_escapeData ( senderemail ) ] ;
[ "«RECIPIENTNAME»" ; mandrill_escapeData ( recipientname ) ] ;
[ "«RECIPIENTEMAIL»" ; mandrill_escapeData ( recipientemail ) ] ;
[ "«SUBJECT»" ; mandrill_escapeData ( subject ) ] ;
[ "«MESSAGE»" ; If ( HTML = 1 ; header & "<BR><BR>" & mandrill_escapeData ( message ) & "<BR><BR>" & footer; header & "<BR><BR>" & mandrill_escapeData ( GetAsCSS ( message & "¶¶" ) ) & "<BR><BR>" & footer ) ] ;
[ "«ATTACHMENTS»" ; mandrill_attachmentCodePlugin ( attachments ; 1 ) ]
)
)
Joan Subirós 15/03/2015 12:42am (10 years ago)
Can anybody help me to send message with TEMPLATE ????
Thanks.
Daniel Wood 10/03/2015 9:16am (10 years ago)
Hi Francis, Bounces are generally something that you cannot know about immediately after sending the e-mail so processing bounces would be a separate process run periodically, either automatic by the FileMaker Server, or manually via the database.
There's possibly a couple of ways to go about this. First might be webhooks. Now I basically know nothing about them but from what I gather code is inserted into the e-mail that Mandrill can detect and then post information to a web server somewhere (I'm sure that is probably really incorrect) but it can inform you about hard-bounces.
The other more FM way we have handled this in the past is to use a setting in the Mandrill config. You can configure Mandrill to e-mail a specific e-mail address when an e-mail bounces. That bounce e-mail is in a fairly standard mandrill format which you can easily parse to obtain information. We use a FileMaker e-mail retrieval plugin which we built ourselves to retrieve e-mails sent to this specific inbox (guaranteed in the fact that every email is a bounce email). We parse out the raw message source and from that extract the original mandrill ID. Because we keep a log of every e-mail sent from the database with Mandrill ID, we can match the bounce mandrill ID to the original sent message, and thus know which e-mails bounced.
That's it in a nutshell but key things to take away are that you can send bounces to a nominated e-mail address via your mandrill config, and from that point on it's a case of making use of those e-mails, either retrieving them into FM or whatever you choose to do with them.
francis 10/03/2015 5:50am (10 years ago)
Did anybody look into the handling of bounces? What whould be a satisfactory approach? My use case is, to send receipts to clients, so of course I would like to be informed about bounces.
Gianandrea Gattinoni 20/02/2015 1:11pm (10 years ago)
That's great!!!
I made a simple change using the new function in the BaseElements plugin.
Doing so, in my first test, I do not need any custom function.
This is how I composed the json string in your demo file without attachments:
"{ " &
BE_JSON_Encode ( "key" ; Settings::Mandrill API Key) & ", " &
BE_JSON_Encode ("message" ) & " { " &
BE_JSON_Encode ("html" ; RicavaComeCSS(Emails::Message)) & ", " &
BE_JSON_Encode ("subject" ; Emails::Subject) & ", " &
BE_JSON_Encode ("from_email" ; Emails::Sender Email Address) & ", " &
BE_JSON_Encode ("from_name" ; Emails::Sender Name) & ", " &
BE_JSON_Encode ("to") & " [ { " &
BE_JSON_Encode ("email" ; Emails::Recipient Email Address) & ", " &
BE_JSON_Encode ("name" ; Emails::Recipient Name) & ", " &
BE_JSON_Encode ("type" ; "to" ) & "} ] ," &
BE_JSON_Encode ( "async"; True ) & ", " &
BE_JSON_Encode ("attachments") & "[]}}"
Daniel Wood 11/02/2015 9:09am (10 years ago)
Hi Chris, that's awesome news, and a very cool subject to talk about. CC & BCC should be no problem, there is a parameter for the mandrill request that can take multiple of either. HTML e-mail you are right about inline images, they can just be referenced via URL providing they are online somewhere. It would be more challenging to figure out how to do an HTML e-mail with the images as attachments. Regarding risks, Mandrill works on a policy whereby your hourly send limit is determined by the type of messages you send. I'm referring to just the non paying accounts here, not sure what happens once you start paying. It has methods to detect if you are a spammer sending out the same message over and over, and if that is the case it will reduce your hourly quota. If it detects the e-mails you send are legitimate then it will increase your hourly quota. It is based on every account having a reputation whcih Mandrill calculates. You can learn more about the quota system here - http://help.mandrill.com/forums/22011078-Quota-and-Reputation-System
The mandrill documentation is really good and you should be able to find out all the answers to your questions there.
Chris Van Buren 10/02/2015 11:24pm (10 years ago)
FYI - I have signed up to present on this at the .dotfmp conference in Berlin in June. I have an hour which isn't that long but should be enough. Please do let me have any hints anyone has and, of course, I will be giving a BIG mention of how Daniel Wood has contributed to this and got me started on it and will also direct people to this blog post and the demo file. Thank you Daniel!
Things I don't know at the present time and will try to implement before then:
-CC and BCC on emails
-Practicalities of sending an HTML email instead of using GetAsCSS. For example, it would be interesting to inline images... I suppose that is just a URL to where the image can be found on the web but I haven't tried this and so don't really know.
-Risks. What are the actual policies of Mandrill. Can you get kicked off? This is a touchy issue for me because I am slowly building a mini business that is an "email game". Emails in this game need to be sent according to a schedule which means that if Mandrill suspended me or was down, it would be a big problem for me (not a problem right now as still getting started but could be a big problem down the road). One problem here is that I am sending short emails to game players so I don't really want to clutter my emails with unsubscribe links yet it seems that Mandrill may be looking for these. So that is something I will try to figure out.
Daniel Wood 05/02/2015 8:45am (10 years ago)
Hi Yossi, this can be achieved through good ol' fashioned scripting and FileMaker development :) If your database has a table of users with email addresses, then when you send an e-mail via Mandrill simply capture who the sender was (or match a recipient to a record in the database). In terms of actually retrieving e-mails into the database sent from other sources, you'd need to look at an e-mail plugin to achieve this, or investigate whether Mandrill offers any API calls to retrieve already sent messages
Yossi 04/02/2015 9:24pm (10 years ago)
Can this integration store within the profile of the Filemaker member a log of all the communications including emails sent to the member, emails received from the member, dates sent/received (attachments sent/received)? Deepest appreciation
Chris Van Buren 02/02/2015 9:44pm (10 years ago)
I don't know how to do that with the Mandrill API. Also, you could just use MailChimp to do this which would be an easy solution. Sorry I can't help more than that.
Joan Subirós 02/02/2015 1:52am (10 years ago)
Thanks a lot Mr. Chris.
Do you know how can I send to all customer's list, the same email , using this API mandrill ??
Chris Van Buren 01/02/2015 10:53pm (10 years ago)
Some points on speed:
-I assume the emails are merged (so all different). There is some other way to send if they are the same (like MailChimp really)
-Speed will depend greatly on whether you have attachments and the actual size of the email.
-I am sending simply messages but many have substantial pdf attachments. I can send about 30 mails/minute. However, this is server side from a server in a data centre which I am sure makes it much faster.
-So, lots of variables. I think you will have to just test to see.
Hope that helps.
Joan Subirós 01/02/2015 8:08am (10 years ago)
Does anybody know any data about the speed of sending emails limit ???
How many time I need to send 80.000 mails ??? ( i pay after 12.000, i know)
Thanks a lot.
David 08/01/2015 3:02pm (10 years ago)
Daniel,
Will this work with FM12 Pro? Do you have an example file for FM12, and if you do could you send it to me?
Thanks!
Matt Larson 30/12/2014 4:16pm (10 years ago)
In regards to my post from Christmas, I ended up figuring out how to tack on attachments from specific container fields to the URL. So I'm good with that.
I think I found a small issue in the example file that seems to be incorrect. In both mandrill_createRequest custom functions…
<pre>HTML = If ( Left ( message ; 6 ) = "*HTML*" ; 1 ; "" )</pre>
should be:
<pre>HTML = If ( Left ( message ; 6 ) = "<HTML>" ; 1 ; "" )</pre>
Even this article has a screenshot of the code with chevrons and not asterisks.
Wouter Keja 25/12/2014 11:05pm (10 years ago)
@Jason: Thank you very much for sharing this! Confirms the rule: "If you don't see what goes wrong, it's always something simple you overlook. Thanks again.
Matt Larson 25/12/2014 6:09pm (10 years ago)
Wonderful solution and writeup! I'm implementing the <i>Insert from URL</i> version.
Question regarding attachments…
The <b>mandrill_createRequestInsert</b>custom function appears to assume related container fields. It also seems to work great if I specify a single container field from the parent table.
But, I have a situation where I have three specific container fields in the main table that I need to attach, and it would be difficult / impractical to relate them to my main parent table just for the purposes of this script.
What would the most elegant way of grabbing container fields here and there? Wondering if it's just a syntax thing, or if I have to tack on attachment data to the URL in my script.
« previous 1 2 3 4 5 next »
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments