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

executeAction()

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

executeAction()

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.

Figure 1 - Add a mail merge

4. Click in the 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

variable

property

{{five.variable.<variableName>}}

. The values passed through as the context to the

executeAction()

function will be merged into the

variable

property on the

Five

object.

Figure 3 - variable property

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

Figure 4 - Save button

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

Figure 5 - Save button

Navigate to the Code Editor

1. Select Logic in the menu.

2. Select Code Editor in the sub-menu.

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.

executeQuery()

- is a function on the

Five

object that executes SQL statements on the Five server and returns results.

isOk()

- is a function used on the

FiveError

object that returns true or false based on the outcome of the returning function call.

createError()

- is a function used on the

Five

object that returns an error with an optional message and notification parameters.

recordCount()

- is a function on the

FiveResult

object returned from the

executeQuery()

function returning the number of records in the result set.

success()

- is a function used on the

Five

object that returns a

FiveError

indicating success.

executeAction()

- is a function used on the

Five

object and executes an action on the Five server.

1. Click the 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.

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

.

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.

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.

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.

Figure 10 - Applications menu item

3. Select the Application record in the list.

4. Click the Application Buttons tab.

Figure 11 - Application Buttons tab

5. Click the 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.

Figure 13 - Add an application button

9. Click the Events tab.

Figure 14 - Events tab

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

Figure 15 - Do Press field

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

Figure 16 - Save button

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

Figure 17 - Save button

How This Will Work in Your Application

1. Click the application button in the toolbar.

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.

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!