The Aftermath of Microsoft Ending AppleScript Support in a Mission-Critical FileMaker Application
Our client runs a mission-critical FileMaker application that manages all their data and generates emails to the outside world based on that data. It builds a PDF and then attaches it to an email with message text and recipients drawn from database data.
Our original design used FileMaker’s SendMail function to open the Outlook client and build the populated draft messages. We later tweaked that to use AppleScript to enable more control over message fonts, access to shared mailboxes, and other features wanted by the client for these messages that serve as their face to the public.
All was well for several years until suddenly, in late 2022, our AppleScript calls stopped working. Microsoft had ended support for several of the essential features we had built into our message builder. Happily, an alternative was at hand by then, and we could turn to the Microsoft API to build and send the messages. However, we still needed a way to present the draft messages to the requesting user so they could review the contents and make any edits needed to the specific message before sending it.
We needed to build a replacement user interface for those messages and use that to show the user, and then compile the contents into the needed API calls to get those messages sent out. We called this Outlook-replacement user experience ORUX for short.
What We Built
We kept the code in a separate ORUX file to maintain our multi-file architecture for this solution. Each message-building script creates a record in the ORUX file for each email, populating the record with database values for the message as it always had.
We created a list-detail layout to display these ORUX records so that a single window could show all the messages in a batch in the list sidebar and each detailed content in the detail content pane. That content pane had features for all the message elements: Recipients, CC, BCC, Subject, Body, Attachments.
Some features needed extra tools to support ease of use and get as close to the Outlook draft experience as possible.
Recipients in the TO and CC fields were populated by default but available to users for adding more and deleting unwanted names. If default recipients got deleted by accident, there’s a tool to regenerate them. We use virtual lists to manage these edits of email recipients. The email addresses are stored as a list in the TO, CC, and BCC fields visible in the UI display, as users familiar with Outlook expect them to appear. However, during active editing of recipients, we show them as a portal in a card window so the list values in a field can be programmatically managed.
Users can send each message individually, using buttons on the detail pane (Send), or they can review the messages and then send them out as a batch using buttons on the list-sidebar (Send All). When a user clicks send, we use Outlook API to create the message in the user’s Outlook account (or relevant shared account) and then send it.
There’s also a Save As Draft option, which saves the ORUX record and lets the user out of the ORUX window. They can come back to their saved drafts later, inside their FileMaker application, and send them out when ready, mirroring the way they used to interact with Outlook draft emails in their Outlook client.
Attachments
Attachments are particularly important. In addition to the system-created attachments on the message by default, we included a drag-and-drop interface for adding additional file attachments. We support removing any attachment, and therefore, in turn, we needed an on-demand way to regenerate those system-created attachments in case any got deleted by mistake.
This system stores its file attachments in a managed container file, cleverly named “FILE,” one attachment to a record. For our system-created attachments, we could just put the IDs of the FILE records into our ORUX-record. Users could interact with the “attachments,” but we didn’t have to move the attachment files back and forth. Only when the message is built in the Outlook API do we need to upload the actual file, and that work can be done without making the user wait.
We used a Carafe bundle to provide a drag-and-drop interface for files added to the ORUX-record ad hoc by the user. These files are uploaded into FileMaker in real time and stored in temporary container records. Learn more about Carafe at https://carafe.fm/.
Both types of attachments are presented in the ORUX interface using a virtual list in a portal so that users may interact with all the attachments in the same way and the same place, regardless of their source.
Demo File
Download a Sample File for this virtual list technique.
As it happened, users liked the drag-and-drop feature so much that they wanted the same sort of thing for adding other system-generated attachments (in addition to those added by the default logic). We used some drag-and-drop text tricks to support this.
In the location where these system-generated files are stored inside FileMaker, we support dragging, and in the ORUX-record we provide a separate dropzone for these files. When one of these files is dragged, we grab the ID of its FILE record, and then when it is dropped, we add that ID to the Attachments field (list of IDs) via triggered scripts.
Follow Up After Sending Messages
The sending of messages is tracked inside this FileMaker database so that users know what has been done or has not been done. Because we now have granular control over the entire message lifecycle, we know when the sending has happened and can proceed with the follow-up tracking actions. These post-send processes are possible using metadata stored in the ORUX record on creation so that after the message is successfully passed on to Outlook via API, we can use that metadata in scripts to record the action of sending the message.
We can also isolate the follow-up actions so that users may choose to do only the follow-up and skip sending the actual message. (This is a real-world use case lovingly referred to as “Fake Send” by this client, as you can see in the screenshot).
How It’s Going
This alternative email builder has been working well for this client for the past two years. Adoption went smoothly, given the effort to closely mirror the experience of Outlook draft messages. There are occasional send-glitches traceable to outages at Microsoft itself, but these have not presented insurmountable difficulty.
The ORUX message records are retained in case of error, so if an outage happens, users find out quickly and still have their draft ORUX record to use for re-trying. (Keep an eye on outages online at sites like this: https://downdetector.com/status/microsoft-365/map/.)
And what’s next? Now that we have this full-featured tool, we’re finding more places to use it. The next project will leverage this email-building mechanism to support some much-desired automation in this FileMaker solution.
Launch This in Your Own FileMaker Application
As a consultative team of FileMaker experts, we harness our collective brainpower to gain a deep understanding of our clients’ businesses and deliver innovative solutions crafted to their specific needs. Strategic problem-solving is typical within our client engagements, like the challenge we’ve shared today. We empower organizations to transform their operations, boost productivity, and catalyze growth through custom solutions on FileMaker.
If you’re facing a similar challenge or a challenge where you’re not sure how to start, we can help. Contact us to talk with a consultant today.
