Send Email from Connect

Connect enables you to compose and send email messages. What’s more, you can integrate these email message objects into your processes using steps and pipelines. For example, you can generate email to end users about job status.

Email message objects can also include file attachments (such as reject-file CSVs) that are generated from Connect result sets.

 

Connect Email Syntax

To create a new email message in Connect, use:

create email if not exists email_name as ( parameter_list );

Where email_name (the name of the email object) is required and if not exists is an optional clause that allows the email object to be created only if it does not currently exist in Connect. (It suppresses an xSQL error when an email name clash occurs.)

And where parameter_list includes:

• “To” = email_addresses
  (required) The comma-separated list of recipients of the email.
• “From” = email_addresses
  (optional) The sender of the email. Defaults to xactly@connect_server_name.
• “Cc” = email_addresses
  (optional) The comma-separated list of carbon copy recipients.
• “Bcc” = email_addresses
  (optional) The comma-separated list of blind carbon copy recipients.
• “Subject” = string
  (optional) The subject of the email. Try to make the Subject as specific and identifiable as you can. Remember that humans will be receiving and reading these email messages.
• “Body” = string
  (required) The body of the email. The string can be any text MIME type.
• “BodyType” = string
  (optional) The MIME type of the email body, such as ‘html’. The default type is ‘plain’. For a complete list of text MIME types, see the IANA web site.
• “ReplyTo” = email_addresses
  (optional) The email addresses to which any replies should be sent.
• “Attachment#” = string OR result_set
  (optional) Attaches a file to the email message. # is a positive integer, starting at 1, that identifies each distinct file attachment. You can attach multiple files using “Attachment1”, “Attachment2”, etc.
• “AttachmentName#” = string
  (optional) Sets the name for the file attachment. The name defaults to the referenced file name, or, if it’s an xSQL result set, to ‘Attachment#’.
• “AttachmentType#” = string
  (optional) The MIME type of the attachment. The default type is ‘plain’. For a complete list of text MIME types, see the IANA web site.

 

Send a Simple Email Message

To start, we’ll just send a simple email message to ourselves.

First, store your email address in a variable that we can use later, when creating email objects:

set v_email_to = 'you@your_domain.com';

NOTE: When learning and testing, remember to use an email account to which you have access.

 

Next, we need to create the email object before we can send it:

create email e_simple as 
("To"=:v_email_to, 
"Body"='Thank you for attending this training lab. Hurray for data!', 
"Subject"='Start Process: Email Lesson at ' || CurDateTime());

NOTE: In addition to variables, you can use xSQL functions and expressions, such as CurDateTime(), within the email definition.

 

Finally, let’s send the email:

send email e_simple;

Check your inbox. If the statement returns a success code (‘ok’), you should have new mail that looks something like this:

 

Send Email with Attachments from SELECT

One of the most useful features of Connect email is the ability to query data tables and generate email attachments on the fly. For example, your ETL pipeline might execute business rule validations and send the rejects to an error table. You can use xSQL to generate a reject.csv file attachment in the email creation expression.

In our example, we’ll create the email object e_xsql_attachment with two CSV attachments. One is a list of payee information, and the other is a result set from the xc_payment table:

create email e_xsql_attachment as 
("To"=:v_email_to, 
"Body"='Here are the csv files you requested!', 
"Subject"='End Process: Email Lesson at ' || CurDateTime(), 
"Attachment1"=(select distinct first_name, last_name, employee_id 
from xactly.xc_participant), 
"AttachmentName1"='participants.csv', 
"Attachment2"=(select participant_id, position_name, 
participant_name, sum(amount), period_name 
from xactly.xc_payment), 
"AttachmentName2"='payfile.csv');

 

Now let’s send that new email: 

send email e_xsql_attachment;

Check your inbox for an email message with two attachments:

 

Use Handlebars HTML Template for Email

Connect includes a subset of the Handlebars HTML template engine. Use these templates to compose more interesting email in HTML.

Our example involves a simple message displaying a list of payees from a Connect table, in a nicely formatted HTML email body.

 

First, we will create a variable to store the payee data from our table:

set v_payee_list = select first_name, effective_start_date, effective_end_date from xactly.xc_participant;

 

Now we’ll store our template body in another variable:

set v_template_body = '<ul>{{#v_payee_list}} <li>{{first_name}} is effective from {{effective_start_date}} to {{effective_end_date}}</li> {{/v_payee_list}}</ul>';

 

Now we create the email object:

create email e_html_template as 
("To"=:v_email_to,
"BodyType" = 'html', 
"Body"=:v_template_body, 
"Subject"='Email example using Handlebars HTML template');

 

And then we send it:

send email e_html_template;

Check your inbox for the formatted HTML email: