Skip to main content

Add a Mail Merge Using a Function

Last updated 12/02/2024

A mail merge can be created using a function and inside this function you can retrieve data from a data source. For this example, we will use a SQL statement to retrieve data from a database inside the function. The function will look for employee birthdays and will be attached to an application button. When the button is clicked, the mail merge will be sent to any employees who have a birthday that matches the current date.

The following steps need to be performed:
  • Create your mail merge record
  • Add your function
  • Create an application button and attach your function

Mail Merge Properties

At a minimum you must pass in the
SMTPToName
and
SMTPToEmail
properties or the
SMTPAddresses
property into the
context
parameter of the function for the mail merge.


Any properties set in the
executeAction()
function will override any settings on the Instance record and the Mail Merge record.


Please refer to the API documentation to have a full list of Five's mail merge reserved context options.

Add a Mail Merge

info
When you add a mail merge that uses a function you do not need to provide a data source as the mail merge will use your function.

The mail merge is created first because the mail merge's action ID will be passed in as the first parameter to Five's function.

1. Click the Add Item button.

2. Type a title in the Title field.

info
The SMTP From Name and SMTP From Email fields are not required here on the Mail Merge form as they are using the values in these fields on the Instances form.

3. Type a subject line in the Subject Text field.


Add a mail merge
Figure 1 - Add a mail merge

4. Click in the Merge Text field.


Merge Text field
Figure 2 - Merge Text field

5. Use the icons in the toolbar to design your template.

info

You need to manually type your properties in your function in the mail merge template using the

property
{{five.variable.<variableName>}}
. The values passed through as the context to the function will be merged into the
variable
property on the object.


variable property
Figure 3 - variable property

6. Click the Save button in the editor app bar.


Save button
Figure 4 - Save button

7. Click the Save button in the form app bar.


Save button
Figure 5 - Save button
1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.


Code Editor menu item
Figure 6 - Code Editor menu item

Add Your Function

The example code is using several of Five's inbuilt functions, you can find the API documentation for these functions by clicking on each link below.

- is a function on the
Five
object that executes SQL statements on the Five server and returns results.


- is a function used on the
FiveError
object that returns true or false based on the outcome of the returning function call.


- is a function used on the
Five
object that returns an error with an optional message and notification parameters.


- is a function on the
FiveResult
object returned from the
executeQuery()
function returning the number of records in the result set.


- is a function used on the
Five
object that returns a
FiveError
indicating success.


- is a function used on the
Five
object and executes an action on the Five server.


1. Click the Add New Code button.


Add New Code button
Figure 7 - Add New Code button

2. Type an ID in the Function ID field.

tip
If you are using the example code below, ensure your function ID is called SendBirthdayEmails. Because the function name in the code must exactly match the function ID that you enter in the Function ID field!

3. Click the OKAY button.


OKAY button
Figure 8 - OKAY button

4. Click the Copy button on the code block below.

info
In the code below, you can see the action ID for the mail merge is passed into the
executeAction()
function with the properties
SMTPToName
and
SMTPToEmail
.

JavaScript
SendBirthdayEmails

function SendBirthdayEmails(five, context, result) {
const currentDate = new Date();

const month = String(currentDate.getMonth() + 1).padStart(2, '0');
const day = String(currentDate.getDate()).padStart(2, '0');

const filter = `%${month}-${day}`;
const sqlStatement = `
SELECT
FullName,
Email
From
Employee
WHERE
DOB LIKE ?`;

queryResults = five.executeQuery(sqlStatement, 0, filter);
if (!queryResults.isOk()) {
return five.createError(queryResults, 'Getting birthdays');
}

if (queryResults.recordCount() === 0) {
return five.success(`No birthdays today ${currentDate.toDateString()}`);
}

for (let i = 0; i < queryResults.records.length; i++) {
const fullName = queryResults.records[i].FullName;
const email = queryResults.records[i].Email;
const emailResult = five.executeAction("HappyBirthday", {"SMTPToName": fullName, "SMTPToEmail": email} );
if (!emailResult.isOk()){
return five.createError(emailResult, 'Failure to execute Email Merge (SendBirthdayEmails)');
}
}

return five.success(result);
}


5. Paste the code block over the template in Five's Code Editor.

6. Click the Save Current Tab button.


Save Current Tab button
Figure 9 - Save Current Tab button

info
If you were wanting to send your mail merge to mulitple recipients and multiple Cc and Bcc recipients, you can pass them as arrays on the mail merge context. These properties take an array of objects which contain an email address and an optional name. See the example below.

JavaScript
CopyStaffIntoSendBirthdayEmails

function CopyStaffIntoSendBirthdayEmails(five, context, result) {
const currentDate = new Date();

const month = String(currentDate.getMonth() + 1).padStart(2, '0');
const day = String(currentDate.getDate()).padStart(2, '0');

const filter = `%${month}-${day}`;
const sqlStatement = `
SELECT
FullName,
Email
From
Employee
WHERE
DOB LIKE ?`;

queryResults = five.executeQuery(sqlStatement, 0, filter);
if (!queryResults.isOk()) {
return five.createError(queryResults, 'Getting birthdays');
}

if (queryResults.recordCount() === 0) {
return five.success(`No birthdays today ${currentDate.toDateString()}`);
}

for (let i = 0; i < queryResults.records.length; i++) {
const fullName = queryResults.records[i].FullName;
const email = queryResults.records[i].Email;

const mailMergeContext = {};
mailMergeContext['SMTPAddresses'] = [five.emailAddress(email, fullName)];
mailMergeContext['SMTPCcAddresses'] = [five.emailAddress('staff@example.com'), five.emailAddress('management@example.com')];
mailMergeContext['SMTPBccAddresses'] = [five.emailAddress('hr@example.com')];
const emailResult = five.executeAction("HappyBirthday", mailMergeContext);
if (!emailResult.isOk()){
return five.createError(emailResult, 'Failure to execute Email Merge (SendBirthdayEmails)');
}
}

return five.success(result);
}


Add an Application Button

info
When working in the Cloud, to create an application button and attach a function, you need to be logged into Five as an Administrator or Control user because a Developer user does not have the permissions to add application buttons.

If you are working in Local, you can make an application button as the standalone user.

1. Sign in as the admin or control user.

2. Select Applications in the menu.


Applications menu item
Figure 10 - Applications menu item

3. Select the Application record in the list.

4. Click the Application Buttons tab.


Application Buttons tab
Figure 11 - Application Buttons tab

5. Click the Add Application Buttons button.


Add Application Buttons button
Figure 12 - Add Application Buttons button

6. Type a caption in the Caption field.

7. Click the lookup icon in the Application Area field and select Top Menu Bar.

8. Optional: Click the Edit button in the Icon field and select an image file.


Add an application button
Figure 13 - Add an application button

9. Click the Events tab.


Events tab
Figure 14 - Events tab

10. Click the lookup icon in the Do Press field and select the function ID.


Do Press field
Figure 15 - Do Press field

11. Click the Save button in the form app bar.


Save button
Figure 16 - Save button

12. Click the Save button in the form app bar above the list.


Save button
Figure 17 - Save button

How This Will Work in Your Application

1. Click the application button in the toolbar.


Send Birthdays button
Figure 18 - Send Birthdays button

info
All employees with birthdays that match the current date will have the mail merge sent to them. If there are no birthdays for a particular date, an optional message can be returned shown in the image below.

No Birthdays message
Figure 19 - No Birthdays message

tip
You can add a job to automate this mail merge so you do not need to click the button!