Sign In

Sending Email

Often it is necessary to send an email notification when something changes in your database. For example, when an order status changes from InProcess to Shipped you might want to notify the customer.

You can send an email from your database when something changes with just a few lines of trigger code. The email can be anything from a simple plain text email to a highly formatted HTML email with document attachments.

Sending A Plain Text Email

To send a plain text email, simply add a row to the parasql_email_out table:

INSERT INTO parasql_email_out (to_address, subject, body) VALUES ('somebody@somedomain.com', 'My subject line', 'Body of the email here');

Sending An HTML Email

To send an HTML formatted email, simply add a row to the parasql_email_out table with html_format set to TRUE:

INSERT INTO parasql_email_out (to_address, subject, body, html_format) VALUES ('somebody@somedomain.com', 'My subject line', '<h1>Body of HTML email here</h1>', TRUE);

Sending An Email with Attachments

To send an email with a document field attachment, simply add a row to the parasql_email_out_attachment table that contains a reference to the document field.

BEGIN DECLARE v_email_id BIGINT; DECLARE v_doc_attachment VARCHAR(255); -- Document Fields are declared as VARCHAR(255) -- create the email INSERT INTO parasql_email_out (to_address, subject, body) VALUES ('somebody@somedomain.com', 'My subject line', 'Body of the email here'); -- get the primary key of the email we just created SELECT last_insert_id() INTO v_email_id; -- get the document we want to attach SELECT MyDocField INTO v_doc_attachment FROM MyTable WHERE MyTable_ID = 1234; -- add the attachment to the email INSERT INTO parasql_email_out_attachment (email_out_id, attachment) VALUES (v_email_id, v_doc_attachment); END

The parasql_email_out Table

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
statusENUM('UNSENT','SENT','ERROR') NOT NULL DEFAULT 'UNSENT'UNSENT, SENT, or ERROR
subjectVARCHAR(255) NOT NULLEmail subject.
bodyMEDIUMTEXT NOT NULLEmail body as plain text or HTML.
html_formatBOOLEAN NOT NULL DEFAULT FALSESet to TRUE if body is in HTML format.
to_address VARCHAR(255) NOT NULLComma or semicolon separated list of addresses.
cc_address VARCHAR(255)Comma or semicolon separated list of addresses.
bcc_addressVARCHAR(255)Comma or semicolon separated list of addresses.
replyto_addressVARCHAR(255)Single address only.
from_addressVARCHAR(64) NOT NULL DEFAULT 'no-reply@parasql.com'Restricted to approved senders unless using SendGrid. If using SendGrid please provide support with your SendGrid API Key and your preferred default from address.
scheduled_onDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()Time email was added to table (server time zone).
delay_untilDATETIMESend immediately if null (server time zone).
sent_onDATETIMETime email was actually sent (UTC).
user_idVARCHAR(64) NOT NULL DEFAULT USER()User ID of sender.
attachment_content MEDIUMTEXTDEPRECATED. Content for simple TEXT attachment (not used by SendGrid).
attachment_filenameVARCHAR(128)DEPRECATED. Filename for simple TEXT attachment (not used by SendGrid).
unsubscribe_groupsVARCHAR(128)SendGrid only. Semicolon separated list of SendGrid Unsubscribe Group IDs.
enable_trackingBOOLEAN NOT NULL DEFAULT FALSESendGrid only. Enable SendGrid click tracking and open tracking.
substitutionsVARCHAR(512)SendGrid only. Email substitutions as key=value;key=value; string.
transportENUM('Google','SendGrid')Automatically managed by AppSynergy; do not change this value.
error_messageVARCHAR(512)Error message text if send error. Automatically managed by AppSynergy; do not change this value.

The parasql_email_out_attachment Table

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
email_out_idBIGINT NOT NULL REFERENCES parasql_email_out (id) ON DELETE CASCADEReferences parasql_email_out.id
attachmentVARCHAR(255) NOT NULL COMMENT 'GCSFILE'contents of a Document Field, Image Field or Signature Field

The parasql_email_personalizations Table (SendGrid only)

This table enables sending personalized email to a large number of recipients using a single email body (typically for email marketing). You can think of a single personalization row as a single envelope in a mass mailing (it contains the address info and substitutions needed to customize each person's name or other info in the email template).

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
email_out_idBIGINT NOT NULL FOREIGN KEY (email_out_id) REFERENCES parasql_email_out (id) ON DELETE CASCADEReferences parasql_email_out.id
to_addressVARCHAR(255) NOT NULLComma or semicolon separated list of addresses, but typically only one in a personalization
cc_addressVARCHAR(255)Comma or semicolon separated list of addresses, but typically none in a personalization
bcc_addressVARCHAR(255)Comma or semicolon separated list of addresses, but typically none in a personalization
subjectVARCHAR(255)Personalized subject; can also customize email subject with substitutions
substitutionsVARCHAR(512)Email substitutions as key=value;key=value; such as %first_name%=Bob;%account_no%=1234;

Sending Email From anybody@yourcompany.com (SendGrid only)

By default your account uses Google to send email. When sending email via Google the only valid from_address is no-reply@parasql.com or the email address of the currently signed in Google Account user. This is too restrictive for many use cases including invoicing, marketing, etc.

You can optionally send email via your linked SendGrid account (www.sendgrid.com). This will allow you to send email from anybody@yourcompany.com without restriction. After setting up your SendGrid account (be sure to visit SendGrid > Settings > Sender Authentication to authenticate your domain) you can provide a SendGrid API Key to support for sending email. All outgoing email will be signed by your domain and sent with TLS security.

Using Full Email Addresses (SendGrid only)

When sending email via SendGrid you may format any email address as simply joe.blow@somedomain.com or optionally use the complete name and address format like this: Joe Blow <joe.blow@somedomain.com>

Email Substitutions (SendGrid only)

When sending email via SendGrid you may specify custom values to be substituted into your email body or subject line. The substitution values are specified via the parasql_email_out.substitutions column or the parasql_email_personalizations.substitutions column.

Say you had an email body like this:

Hi %first_name%,
Your customer number is %customer_id%.
Thanks!

You could then specify substitutions to be:

%first_name%=Bob;%customer_id%=12345;

And the email sent would look like this:

Hi Bob,
Your customer number is 12345.
Thanks!

There are also global substitution tags that you can use in your email body for things like Unsubscribe links. For example, the <%asm_global_unsubscribe_raw_url%> tag will be replaced with the SendGrid global unsubscribe URL, and the <%asm_group_unsubscribe_raw_url%> tag will be replaced with the SendGrid group unsubscribe URL. Use the second tag with the parasql_email_out.unsubscribe_groups column to specify which unsubscribe group the email belongs to.

Diagnosing Problems

To diagnose email problems, review the error_message column of the parasql_email_out table by issuing a command like the following via Tools > SQL Console...:

SELECT * FROM parasql_email_out WHERE error_message IS NOT NULL ORDER BY id DESC LIMIT 25

The most common error is specifying an invalid from_address. If sending via Google (the default) the from_address must be either no-reply@parasql.com or the email address of the currently signed in Google Account user. If sending via SendGrid the from_address must be somebody@yourdomain.com. You may of course create a view on this hidden table to make it accessible to your applications.