I recently responded to a question on a forum about building up messages from standard paragraphs in a database and, as a result, decided to create this blog post on the subject - extending it to actually sending emails.
The Objective
Let's assume we have a database in which we record Contacts and that we want to be able to send them emails which are a combination of both manually entered text and also 'standard paragraphs'. In addition, it would be useful to be able to automatically insert Names or other text from the contact's record.
Let's Start with the Database
I've already stated that we have a database with a Contacts table. For this example (should you choose to follow it) you will need to create a Contacts table (in Knack/Airtable) with at least the following fields:
First Name
Last Name
Email Address
Here's my example database in Airtable:
We then need a table to hold our Standard Paragraphs - and we'll need the following fields:
Name
Text (the text we want to insert)
Search for (the placeholder text we type into our email to be replaced - a formula such as "{" & Name & "}")
Finally, we need a table to hold the message we want to send which (in this example) needs the following fields:
Message Entry (the text of this particular message)
Send To (Linked to the Contacts table)
Send (a Checkbox indicating that the message is ready to send) (not really required for the Knack version)
Sent Date/Time
Email Text Sent (the full text of the sent email)
Send Updated (the date/time the Send field was last updated) (again not really required for Knack)
Send Updated uses the new Last Modified Time field type linked to the Send field:
So, having set up the tables, we're able to create Standard Paragraphs such as:
and enter specific messages to contacts:
Setting Up Integromat to Create The Message
As can be seen from the above message, we are going to need to find a way to take the user's typed text, replace the 'standard paragraphs' with their full text and replace one of more fields with values from the Contact record. In essence this is the process we will need to build:
The process is triggered when new message is ready to send
Search for each Standard Paragraph in the text and replace it's placeholder name with the full text from the Standard Paragraphs table
Once this is done, replace the {First Name} and {Last Name} placeholders with employee values from their Contact record
Send the message
Update the database to store the date and time sent and the actual text sent
Trigger the Process
Depending on which database you are using, the process can be triggered either when a new 'Email to Contact' record is created (this would be the Knack approach because the record is only created when the user has completely input it) or when the Send tick box is set to yes. Here's the latter option:
This is all quite straightforward - but note that in Airtable I'm using the new 'Last Modified Time' field type to trigger this.
Having triggered the process, I then had to think a bit in order to work out how to repeatedly update the user's text with the paragraphs and hold the resulting paragraph for the following substitution. I found that normal Integromat Set Variable steps weren't the way forward and I opted for using the Storage App to hold the value. So the next step required is to take the user's basic text and store it.
When you create the data store you also need to set it to hold one field in addition to the 'key' of the record - I called it Email Text:
Having a place to store the data, you can then complete the first Data Store app in the Scenario:
Looping Though the Standard Paragraphs and Replacing Placeholders
In this example, I decided that the easiest approach to replacing any Standard Paragraph placeholders in the text (for example {Welcome}) was to loop through all of the Standard Paragraph records and see if their Placeholder (I called the field Search For) was found in the message the user had constructed.
Looping in Integromat is quite simple to set up using an Aggregator - but first we need a step to retrieve the Standard Paragraph records:
Points to note here are to set the Max Records value to a high enough value to retrieve all the possible entries in your Standard Paragraphs table.
The way Integromat works, all steps following this will be processed for every record found in the previous step - which is great because we want to go through each, check to see if the Placeholder is present and replace it.
The next step though is to retrieve the currently stored value of Email Text from our Data Store:
This is necessary because we are going to be looping through and updating this value - in fact the next step is to update it:
In this one Data Store step, we are replacing the existing value by replacing the Placeholder (Search For) with the text from the Standard Paragraph table.
Now, at this point, we need Integromat to go to the next record in Standard Paragraphs and repeat the process. To do this we can use an Aggregator step (it doesn't matter which type because we aren't actually going to use the resulting aggregation - we just need to loop). In this case I used a Table Aggregator:
You can see here the effect of adding the aggregator - the section of the Scenario to be looped is shaded in grey once you select the Source Module.
So, at the end of this looping process, our Data Store record will hold a version of the email text with any Standard Paragraph placeholders replaced with the associated text.
So now, we want to take this text (from the data store) and replace any Contact field placeholders with data from the Contact record. The next step is therefore to retrieve the lates value from the Data Store - this is exactly the same as the example above and so I won't repeat the setup.
We also need to obtain the associate Contact record (to get the email address, first name, last name etc):
Now we have all the information we need to proceed.
Replacing First Name and Last Name
This is quite a simple process consisting of just two Set Variable steps:
So now we can send the email:
Note that I have used the Office 365 email App - but you could link to others.
This results in an email like this:
Final Tidying Up
In this particular example, there is no need for the Data Store records to be kept - so I would suggest deleting them. I would also suggest updating the database to record when the message was sent and what the final set was. If you want to do this then the steps are as follows:
and
Summary
So, we've built a process which allows you to send semi standardised emails from your database and store the date sent and final text back to the record - pretty cool - and all without any code as such! The overall Integromat Scenario looks like this:
You can find out more about Integromat here
and Knack here
and Airtable here
My next post will cover how to achieve this in Ninox - spoiler alert - no external tools will be required.
I hope you've found this useful - please feef free to join in and comment!