I find myself writing this post partially in response to a question in the Ninox forum about sending emails to Contacts. While sending basic emails is simple, the user wanted to know how to send emails to mailing lists periodically and to be able to change the content each time without changing the code. Here goes…
Before we start, to create this yourself, you'll need a Ninox Cloud account (sending emails only works in the cloud). You can sign up for one here.
Also, I have exported the database used in the article and it's available here - you just need to load it into your Ninox Cloud account.
Getting Started
I am going to be basing this post on the database used in my previous post 'Relating to Relational Databases - Part 1' so you may want to check this out before we start. In this database, we start with two tables - Companies and Contacts - it is a very simple Contact Database.
I want to create a facility for users of my database to be able to create mailing lists, add Contacts to them, and then send emails to them based upon a template which will allow for insertion of contact specific data as the messages are sent. In the process I also want to record each time a message is sent to the group, recording the template used, the date sent and the user who sent it.
To achieve this, I am going to need 4 new tables:
Email Templates - which is where the email Subject and Body are stored - the latter in HTML format.
Email Lists - this is a record for each list we want to be able to send to.
Contact Email Lists - here we link Contacts and Email Lists - it builds a Many to Many relationship between them (this is a Subtable of Contacts)
Mailouts - we create a new record in here each time we want to send a template based email to the a list (this is a Subtable of Email Lists)
We will also need a little NX Code attached to a button to actually send the messages.
Email Templates
This table is very simple - consisting of just 3 input fields, Template Name, Subject Line and Email Body:
📷
And, once fully set up the page looks like this:
📷
The interesting thing here is that the Email body is entered in HTML - which allows you to create good looking emails. If you don't know HTML, then you could create your text in one of many online HTML editors like this - https://html-online.com/editor/
Email Lists
The idea behind this table is to allow you to create multiple lists of contacts and that a Contact can be a 'member' of more than one of them. Keeping this simple, there is only one input field here - the 'List Name':
📷
You can see that there are two links though - a Subtable called Mailouts and also a reference from Contact Email Lists. The screen looks like this:
📷
Contact Email Lists
This table is where you can assign a Contact to one or more Email Lists and creates a Many to Many relationship between Contacts and Email Lists. The essential fields here are the two references - but I've added an Opt In field which allows you to indicate whether people are opted in to the emails or not (and the sending code will take this into account).
📷
Mailouts
The final table is all about recording that a template is being to a list and also includes the button with the code to send the messages. Again there are only two input fields here - links to Email Lists and Email Templates - but I have added fields for Date Time Sent and Sent By (which is the user). The latter two are filled in automatically when the process runs:
📷
And the screen looks like this:
📷
Clicking the button causes the code to run and send the emails - here's what the email in the sample template looks like:
📷
Code to Send the Messages
The magic happens behind the Send button on the Mailouts view:
"-------------------------------------------------------------------------------------------------";
"this code loops through each contact assigned to the connected email list and sends them an email";
"the body of the email uses the replace function to substitute data from the datbase for the";
"placeholders in the template";
"-------------------------------------------------------------------------------------------------";
let thisList := this.'Email Lists';
let subject := 'Email Template'.'Subject Line';
let body := 'Email Template'.'Email Body';
for thisListPerson in select 'Contact Email Lists' where 'Opt In' = 1 and 'Email List' = thisList do
let thisBody := body;
thisBody := replace(thisBody, "{Company Name}", thisListPerson.Contact.Company.'Company Name');
thisBody := replace(thisBody, "{First Name}", thisListPerson.Contact.'First Name');
thisBody := replace(thisBody, "{UserName}", userFullName());
sendEmail({
from: userEmail(),
to: thisListPerson.Contact.Email,
subject: subject,
text: " ",
html: thisBody
})
end;
'Date Time Sent' := now();
'Sent By' := user()
Points of Interest
The replace function is used to replace the placeholders in the template with fields in the database - you can see that it would be simple to add additional options here or even extend the feature to allow placeholders in the Subject Line.Note the filter 'Opt In' = 1 - this will stop the system sending any messages to anyone who has opted out of this email list.
Summary
Hopefully the functionality here is useful as a basis for building your own email facilities into your own databases. This could also be adapted to allow for sending messages to individual Contacts, saving the messages in a table linked to contacts or in many other ways.
If you'd like to download the database used in this post then please do so here. Note that I have removed the email addresses from the contact records in the database - I suggest you add addresses which you can receive to some of them before trying it out!
Comments and discussion always welcome!!