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
Jason Tallman 18/12/2014 5:47am (10 years ago)
I think I found the solution to sending other file types. The scripted insert file command in the Add an Attachment script is set to compress the file. Once I disabled that everything sent correctly.
Chris Van Buren 25/11/2014 11:34pm (10 years ago)
Some useful things for use of this in real life:
-I got into trouble with all sorts of disallowed characters like - (dash). Basically the message would be truncated when these were used. I fixed this with the URLEncode function (http://www.briandunning.com/cf/165). This function does percent encoding.
-I also got into trouble with curly quotes. These are high ASCII which is not allowed and not fixed by percent encoding (don't know why - something to do with URLs). Anyway. This was again easily fixed with the custom function RemoveSmartQuotes (http://www.briandunning.com/cf/1115)
I think these are going to be common problems so should perhaps be incorporated into the demo file.
I am using the FileMaker insert function.
Stephen Dolenski 17/11/2014 8:21am (10 years ago)
I am curious if any one has a good instructions to integrate this to target an existing Mandrill Template, so I don't have to send over the HTML template and inline CSS as a whole but just template variables that mandril provides like *|MYVAR|*. then I send over a formatted html table. For the MYVAR variable?
Andrew Robinson 03/11/2014 11:06pm (10 years ago)
Just a word of warning. My Mandrill account has been suspended for the last week causing my whole filemaker solution to be dead in the water.
Mandrill stopped my account because they say my emails did not contain an unsubscribe link and a lot of people receiving my emails did not open or read them.
We have been using mandrill within the company to send other emails outside filemaker too.
I tried to contact Mandrill about it but they are very unhelpful. They do not reply to emails and there is no phone number to call. I am having to re-write my solution today to use a different method of sending as I cannot trust them, and I don't know when my account will if ever be resurrected.
If you are going to use Mandrill, don't use your account to send other emails for your business keep it to your system emails.
Daniel Wood 28/08/2014 11:04am (10 years ago)
Hi Andrew, I have one possible way to do this that I've actually used recently myself. So no insert file you are right. My issue was inserting a CSV back into the database. So what I did was use the BaseElements function BE_ReadTextFromFile. This function can work server-side, and can basically read the contents of a textual file (like a snapshot link) into a variable or field in the script. You can then combine this with the BE_Base64_Encode function to end up with a base64 encoded version of the file.
Note you can't use the FM base64 encoding function as this expects a container/binary file as its parameter, but the Base Elements one works with regular text.
This should allow you to read in your snapshot link and send it through using mandrill. Note you'll need the BaseElements plugin installed on server. Also note that the path that BaseElements uses for the parameter is slightly different to FileMakers path, in that the Base Elements one does not expect the volume name as part of the path, eg instead of /Macintosh HD/Users/Daniel/Desktop, it should just use /Users/Daniel/Desktop, to get around this I just substitute out the result of Get(SystemDrive) and stick a / at the start of the path.
So in summary:
1. Export your snapshot link to disk on server into a known path
2. BE_Base64_Encode ( BE_ReadTextFromFile ( Path ) )
3. Send the result of step 2 using Mandrill, defining whatever the filename of your snapshot file should be...
4. Note Path in step 2 needs to exclude system drive.
hope this helps !
Andrew Robinson 27/08/2014 2:20am (10 years ago)
Thanks Dan, that worked. Another quick question if I may.
Do you know a way to get an attachment into the container field Server Side?
I have a task database that emails a person every morning with their tasks and attaches a Filemaker Snapshot link file to the email. The insert file script step is not available on server side so not sure how to get the attachment into the system. I currently just use the Filemaker send mail for that bit but would be good to use this new mandrill function.
Daniel Wood 21/08/2014 11:35pm (10 years ago)
oops, the comment encoded it ! you replace ampersand with the percent symbol followed by number 26, eg "% 26" but without the space :)
Daniel Wood 21/08/2014 11:34pm (10 years ago)
Hi Andrew, if using the plugin method these should just work fine, however if using the insert from URL method you'll need to percent encode this character for it to be used. What you can do is run a substitute over your message contents replacing & with "&" which is the encoding for an ampersand. You could put this substitute in the custom function that creates the request. cheers !
Andrew Robinson 21/08/2014 11:03pm (10 years ago)
Hi. Does anyone know how an email could have the "&" character in it? One of my scripts creates an email with a link in it which includes the & symbol. The mandrill script and CF treats that as the end of the message and only sends the part up to that point.
Wouter Keja 14/08/2014 11:49am (10 years ago)
Hi Daniel,
Thank you for this amazingly quick response. I will look into it and if I find clues I will share them here.
Meanwhile I hope that there will be a solid solution. For my own use it is no problem at all to send a PDF or a zipped docx, but it would certainly be nice if any attachment would work.
By the way, I ironically just decided on the insertfromURL version...
Cheers,
Wouter
Daniel Wood 14/08/2014 11:09am (10 years ago)
Hi Wouter, thanks for the comment. I've replicated this myself using Word 2011 in mac. While I'm not 100% sure of the solution, the issue is possibly due to one of a few things - this article here outlines what could be happening: http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macword/unable-to-open-doc-and-docx-file/e1c366e1-8e25-4309-930c-527741825a66
I've tried replacing the mime type we use in the custom functions with a word/office specific one but this has not helped the issue unfortunately. We use octet stream mime type as it works well with *almost* all document types, but it seems here using the other specific mime types aren't helping.
I also noted that the size of the sent docx is normally smaller than the original meaning it may be information is lost in the base64 encoding process. The custom function that we use to prepare an attachment strips out any carriage returns or line feeds after the base64 encoding is done, otherwise it cannot be properly sent in the Mandrill request. I wonder whether the base64 encoding of a docx file actually includes far more CRLF (as part of the actual encoding of the file) that when stripped out basically break the encoding - I had assumed CRLF was not important, and without removing it we can't send the request to Mandrill. This may be something to look into.
My other searches around the internet haven't yielded a definite solution here either so hopefuly someone is able to help out. My only advice is for sending files like this, if you compress them to zip first and then send, it should work (tested and confirmed) , its an extra step and a bit of a pain but can be done.
If you are using the BaseElements plugin version of the file, then you could actually make use of the BE_Zip function included. As part of your procedure for adding attachments, you could check the extension of the file you added, and if it is one such as a docx, you could run it through BE_Zip first to compress it before sending.
cheers
Wouter Keja 14/08/2014 8:52am (10 years ago)
Hi Daniel,
First of all: thanks for this great solution!
But there it is, I've come acroos a problem and of course I come knocking on your door to see if you can help me with it:
I tried to send mails with various attachments. So far your pictures went great, PDF was a breeze, but docx gave the following alert:
The Office Open XML file <filename> cannot be opened because there are problems with the contents.
Details:
The file is corrupt and cannot be opened.
Do you - or anyone else her, for that matter - have any idea what causes this problem and (better yet) what I can do to fix it?
Many thanks,
Wouter
Marc Nicholas 14/07/2014 3:45am (10 years ago)
Thanks for writing this up...a lifesaver as I really wanted to use Mandrill but have also just put a lot of customer data in FileMaker!
Rakhitha Dias 27/06/2014 8:47am (10 years ago)
Hello Daniel,
I was reading the comments after posting my comment and found the answer!
Thank you for the post it was a great time saver!
Have a great weekend!
Daniel Wood 26/06/2014 1:05pm (10 years ago)
hi Rakhitha, thanks for the comment.
There was a bug in the demo file and I cannot quite remember if it had been fixed. Basically the "escapeData" custom function is not required if you use the "insert from URL" method of sending the request, so you can actually take this out of the createRequest function for the Insert from URL step. The additional issue with this was that it caused formatting to be lost, so this could be your problem.
if not, could you let me know which of the 2 sending methods you are using, which formatting you are using etc (as much as info as possible would be appreciated).
cheers!
Rakhitha Dias 26/06/2014 12:59pm (10 years ago)
Hello,
I have been using your example file but I did notice that the formatting was not being carried over.
Any ideas?
Regards
Daniel Wood 25/06/2014 9:27am (10 years ago)
Hi Chris, it is easier than you might think. Check out the Mandrill API page on sending messages here - https://mandrillapp.com/api/docs/messages.JSON.html.
The part you want to pay attention to is the "To" array which takes multiple structures of recipients. You'll see in the example code they have setup a single one for the person "Recipient Name". All you need to do is modify the request to add in extra ones for cc and bcc
The other cool thing about their documentation is you'll notice a "Try It" button at the top right corner of the code box, click this, and you can then edit the code on their page. Hit the "send request" button underneath to test it. So you can put in your own name and email, and play around with adding your cc and bcc parts here until it works.
Chris Van Buren 25/06/2014 6:59am (10 years ago)
I also will need the CC and BCC and will take the job on unless somebody else does it before me. I haven't looked at it yet but I have the feeling it will be well outside my comfort zone as well... I suppose it will be stretching!
George Perry 25/06/2014 6:55am (10 years ago)
As it applies to emailing from FileMaker:
Has anyone written the send.json API call for sending CC and BCC via Mandrill? I have seen the docs on Mandrill but a bit forbidding for me.
Daniel Wood 19/06/2014 2:20pm (11 years ago)
hi Hans, thanks for the comment. I'm not sure I follow you sorry, are you able to give me some more information about what you re attempting to do? If you have your own HTML code already built and wish to send that, then you can modify the mandrill_createRequestX functions (where X is either plugin or insert from URL function). These functions take the message as a parameter, and the functions transform the message into HTML on the assumption the passed message is rich text. You'll see a part of that function that transforms the message using the GetAsCSS function. You can basically remove this part and just leave the message parameter in there. If this does not answer your question let me know.
Hans 19/06/2014 7:34am (11 years ago)
Hi, Thanks for the great example. How can i send an HTML email directly form the example field?
Thanks
Sanusi 12/06/2014 10:08am (11 years ago)
Yes you're right.
It works now.
thanks
Daniel Wood 12/06/2014 9:46am (11 years ago)
hi Sanusi, thanks for the comment - could you let me know which weblink in particular is not clickable as an example? They are clickable and working for me. Also, could you let me know what browser & OS you are using, it may be that they are not working in your specific browser only, cheers !
Sanusi 12/06/2014 9:43am (11 years ago)
Hi Daniel,
Weblinks don't appear as a hyperlink.
Thanks
George Perry 10/06/2014 4:00am (11 years ago)
Have the single email working via Plugin. Onto multiple recipients!
A great solution now that MS Outlook 13 is not compatible with Filemaker.
From FileMaker, Inc 05/27/2014:
FileMaker Pro is a 32-bit application, and FileMaker Pro 13 is relying on Windows Mail application programming interface (MAPI) to handle Send Mail commands. The issue is 64-bit Outlook will not allow MAPI commands from 32-bit applications. No other information is available at this time.
TSGal FileMaker, Inc.
« 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