1. Home
  2. Data
  3. Messaging Services
  4. trellispark Automated Messaging (Email) Functionality

trellispark Automated Messaging (Email) Functionality

This article outlines how to use the trellispark automated messaging functionality. It provides a guide through creating an email campaign, creating a distribution list, and queuing the emails, with an additional section devoted to building your own TSQL stored procedure

User Experience

Communications Manager Application

At the application level, there are two options: Email Managers and Topics. Topics are a list that are global across the workspace, allowing users to opt in or out of relevant messages. Email Managers allow a user with access to generate a series of emails to be distributed to users.

Email Manager

Graphical user interface, text, application, email  Description automatically generated

In the Email Manager, you can specify a list of email templates and their method of generating a distribution list. Enter a descriptive name for the email manager, and an email address to be provided as the reply address for all emails. Select the message queue that the emails will be sent through, and the method for selecting a distribution list.

There are six options for generating a distribution list:

1st: You can send the emails to all active users in the workspace. This does not add any fields to the form.

Graphical user interface, text, application, email  Description automatically generated 2nd: You can generate a distribution list manually by adding emails as children of the Email Manager.

3rd: You can select from a list of active users in the workspace.

Graphical user interface, application  Description automatically generated

4th: You can specify a stored procedure to automatically select the distribution list.

Graphical user interface, text, application  Description automatically generated

5th: You can select one or more topics to apply to the Email Manager. When you queue emails in this Email Manager, all users in the workspace with the selected topics will receive the email.

Graphical user interface, application, Teams  Description automatically generated

6th: You can specify Send on Join to indicate that this email should not be queued normally. Instead,  navigate to the Invite Users application and use the controls there to specify the initial emails in a sequence to send when a user uses that join link.

Graphical user interface, application  Description automatically generated

Email Templates

Graphical user interface, text, application, email  Description automatically generated

An Email Template allows you to create emails to be sent as part of a sequence or at a scheduled time. The required fields is a name, the type of email (Scheduled or Sequenced), the content type of the email (HTML or XSLT), the subject of the email, and the content to be sent. If the Content Type is HTML, enter the Content as HTML. If the Content Type is XSLT, enter the Content as an XSLT formatted template.

There are two types of email that can be selected as Email Type: Scheduled and Sequenced

Scheduled emails allow you to specify the date and time that the email will be sent.

Sequenced emails allow you to specify what email to send the current email after and the interval between it and the previous email.

The options for Interval Units are hours, days, weeks, months, and years.

Workflow

Email Manager

Email Managers contain the Queue All Sequences command which will queue all published emails that are part of prepared sequences. It will search for each email that qualifies as an initial email, then load the distribution list and queue each email in the process list. They also contain the Update Queues command that will apply any chances to the Email Templates or the distribution list and update the ProcessList table with the updates.

Email Templates

Email templates are created as drafts and can be published when ready to use with the Publish command. Once in the Publish state, the user can queue a Scheduled email and all emails that follow it using the Schedule Mail Sequence command. Emails in the Queue can be removed using the Remove From Queue command.

Building your own Distribution List TSQL

This section will go over some basic parameters required when building a TSQL stored procedure to populate a distribution list.

Required Parameters

The following parameters must be accepted by the stored procedure in the following order: SessionGUID, UserGUID, InstanceGUID.

In the stored procedure, they should be included in the parameters as the following:

@SessionGUID AS UNIQUEIDENTIFIER,

@UserGUID AS UNIQUEIDENTIFIER,

@InstanceGUID AS UNIQUEIDENTIFIER

Required Output

Only two columns in the output will be used: EmailAddress and UserGUID. This output can be achieved by using the following statement as a final select.

SELECT [Column 1, uniqueidentifier] as UserGUID, [Column 2, varchar] EmailAddress FROM [Table] WHERE [Condition]

Setting up your SQL Scheduled Job

If you’re doing your own deployment, you will have copied a version of our SQL database structure. Unfortunately, this does not include a copy of the SQL Scheduled Job that runs to move emails from the ProcessList table into the Messages table. Without it, nothing will be sent. The way to add the SQL Scheduled Job is to open the Microsoft SQL Server Agent at the bottom of the server object browser in SWL Server Management Studio, right click on “Jobs”, then click create Job. The following screen will appear. The information on this screen isn’t important, but you should give the Job a name that will let you find it later. The owner of the job will be your account.

Next, you will need to add a step. Click the “Steps” option on the left tab in the job screen, then click “New”. Give it any name, set the type as Transact SQL script, and set the database to the Database that you are using. In the Command box, enter “execute [dbo].[GI_AddToMessageQueueFromProcessList], then click OK.

Finally, on the main Job creation window, click the “Schedule” tab on the left and click “New”. Call the schedule “Hourly Schedule”, set the Schedule type as “Recurring”. In Frequency, Occurs should be “Daily” and it should recur every day. In Daily frequency, it should recur every hour for the full day. The start date should be specified as the current date with “No End Date” selected. Click “OK”, then click “OK” on the main Job Creator window.

Updated on November 1, 2022

Was this article helpful?

Need Support?
Can’t find the answer you’re looking for? Don’t worry we’re here to help!
Contact Support

Leave a Comment