Send E-Mail
The "Send E-Mail" module sends an email to one or more recipients. As attributes, the module expects data such as the sender's email address, email subject, email content, etc. The module can be used to send notifications or reports. These can also contain attachments in various formats.
| Name | Meaning |
|---|---|
| Module | Send E-Mail |
| Module Class | DsModSendMail |
| Type | Java |
| Purpose | Sending emails with or without attachments for notifications, reports, and the like |
| Transformation Code | Database query for control |
| Sources | One or more source tables in a database |
| Target | One or more email recipients |
Description
When the Send E-Mail module is executed, emails are sent to one or more recipients. The attributes, the target from the graphical model, and the database query are used to build the emails and optional attachments.
In the simplest use case, all attributes can be filled with static data. For more complex scenarios, variables can also be used, which are populated with values by the database query.
Basically, the database query determines how many emails should be sent. For each result row of the query, one email is built and sent.
A simple example could look like this: In the center of the figure, a transformation for sending an email with source and target is shown. The target object is displayed at the bottom left. It is of type Email and contains a recipient address. At the bottom right, the details of the transformation "Simple Send" with its attributes and transformation code are visible:
The email parameters such as sender address, sender name (alias), subject, and content are filled with fixed data. The database query is a dummy query. It returns only one row, so only one email is sent.
Data Sources
The data sources of the module are one or more tables that must all be in the same database and are used in the database query.
Data Target
The module expects a single email address object (sqts_object_type_id = 12) with one or more email addresses separated by commas.
Attributes
The module has the following attributes:
| Name | Type |
|---|---|
| Sender Address | Sender's email address used when sending the email. |
| Sender Alias | Sender's alias name. This alias is displayed to the recipient in addition to the sender's email address. |
| Subject | Subject line of the email(s) to be sent. |
| Mail Body | Message content of the email(s) to be sent. |
| Mail Body Mime Type | MIME type of the message text. |
| Attachment Columns | Comma-separated list (without spaces) of names used as placeholders for file attachments. Each name defines and creates a file attachment. |
Variables
Variables contain data that can be used dynamically in different places. Variables can be used in the following message fields:
- Recipient lists
- Subject line (Subject)
- Message content (Mail Body)
A variable is defined by its name and used with a leading dollar sign and curly braces. All columns from the database query represent variables that can be used in the mentioned places.
Example
An example of using variables is shown in the next figure. The transformation "Send Dynamic Mail" uses a target object of type Mail with a variable "mail_recipients" as the email address (bottom left). The attributes "Subject" and "Mail Body" also use variables, namely "mail_subject" and "mail_body".
All variables are generated by the database query. The corresponding column names "mail_recipients", "mail_subject", and "mail_body" are specified in the query. The data itself comes from the control table "report_control", which looks like this:
The example thus generates three emails:
| Recipient | Subject | Message texts |
|---|---|---|
| team1@example.com | Welcome ... | Hello Team 1, welcome to the world of datasqill |
| team2@example.com | Todos | Hello Team 2, please remember the todos! |
| team3@example.com | Thank | Hello Team 3, thanks to all for a good job! |
Replacing within texts
Variables do not have to replace an entire message field. It is also possible to replace only parts, for example in
Mail Body: Hello, the report was created today, ${report_date}, at ${report_time}.
Formatting
Variables can be formatted. To do this, a format instruction must be prefixed when using the variable, such as ${date:report_date}. The following prefixes can be used:
| Prefix | Formatting | Output Example |
|---|---|---|
| ${date:var} | Formats the value of variable "var" as a date in format "DATE '%4d-%02d-%02d'" | DATE '2021-10-09' |
| ${timestamp:var} | Formats the value of variable "var" as a timestamp in format "TIMESTAMP '%4d-%02d-%02d %02d:%02d:%02d'" | TIMESTAMP '2021-10-09 22:05:12' |
| ${number:var} | Formats the value of variable "var" as a number (without quotes) | 34 |
| ${string:var} | Formats the value of variable "var" as text (with quotes) | '34' |
File Attachments
In addition to the subject and message text, attachments can be created for an email. Special variables are used for this. These variables are first defined via a comma-separated list under "Attachment Columns" with a variable name each. Each specified name serves as a base name when specifying the attachment (referred to as <base> below). For each base name, further variables are then specified via the database query:
| Name | Meaning |
|---|---|
| <base> | Variable name of the attachment, also specification of the content Depending on the type of attachment, this specifies either a file, a string (CHAR, VARCHAR, CLOB), or a binary object (BLOB) with the content of the attachment. This column must be present in the database query. |
| <base>_name | Name of the attachment in the email If no name is specified, the variable name is used for type "file". For all other types, "attachment_" and the number of the attachment are used as the name in this case. |
| <base>_type | Specification of the type of attachment
|
| <base>_mimetype | MIME type of the attachment Common MIME types such as "text/plain; charset=UTF-8" can be specified. If no MIME type is specified, the following defaults are used
|
One attachment is created for each variable name. A definition like
Attachment Columns: logo,report
creates two email attachments. The details of each attachment, i.e. logo_name, logo_type, report_name, report_type, etc., must be generated via the database query.
Examples
Email with attachment
The following screenshot shows a transformation that sends emails with Excel files as attachments (top of the image). Below left, the definition of the control table "mailing" as source and the target "sales@example.com" of type "Email" are shown. At the bottom right, the content of the transformation with attributes and database query is visible.
The variable name of the attachment is "excefile" (see "Attachment Columns"). Accordingly, the database query generates the values for "excelfile", "excelfile_type", and "excelfile_mimetype".
If the control table looks like this, two emails are generated:
Each email has one attachment, namely "revenue.xlsx" and "regions.xlsx". The corresponding files must be in the datasqill user's home directory so that the server can attach them when sending the email.
Email with multiple attachments
A more complex example is shown in the next image, where an email with two attachments is sent. Variables are used for both email fields and attachments. All are filled with corresponding values via the database query:
The variables 'mail_recipients', 'mail_subject', and 'mail_body' are used to define the corresponding email fields. Their values are generated with the help of the database query.
Furthermore, two attachments are created for each email. The first is defined by the variable name 'pdffile', the second by 'excelfile'.
For the variable name 'excelfile', files from the subdirectory "data/reports/" in the datasqill user's home directory are sent. They are of type 'file' and have the MIME type 'application/excel'.
For the variable 'pdffile', the database column 'pdffile' is queried. It provides the binary content for the PDF files to be sent. Type 'bin' is used for this (database column with binary data) and 'application/pdf' as MIME type.