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 Name	Datatype	Comments
id	BIGINT NOT NULL AUTO_INCREMENT	Primary Key
status	ENUM('UNSENT','SENT','ERROR') NOT NULL DEFAULT 'UNSENT'	UNSENT, SENT, or ERROR
subject	VARCHAR(255) NOT NULL	Email subject.
body	MEDIUMTEXT NOT NULL	Email body as plain text or HTML.
html_format	BOOLEAN NOT NULL DEFAULT FALSE	Set to TRUE if body is in HTML format.
to_address	VARCHAR(255) NOT NULL	Comma or semicolon separated list of addresses.
cc_address	VARCHAR(255)	Comma or semicolon separated list of addresses.
bcc_address	VARCHAR(255)	Comma or semicolon separated list of addresses.
replyto_address	VARCHAR(255)	Single address only.
from_address	VARCHAR(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_on	DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()	Time email was added to table (server time zone).
delay_until	DATETIME	Send immediately if null (server time zone).
sent_on	DATETIME	Time email was actually sent (UTC).
user_id	VARCHAR(64) NOT NULL DEFAULT USER()	User ID of sender.
attachment_content	MEDIUMTEXT	DEPRECATED. Content for simple TEXT attachment (not used by SendGrid).
attachment_filename	VARCHAR(128)	DEPRECATED. Filename for simple TEXT attachment (not used by SendGrid).
unsubscribe_groups	VARCHAR(128)	SendGrid only. Semicolon separated list of SendGrid Unsubscribe Group IDs.
enable_tracking	BOOLEAN NOT NULL DEFAULT FALSE	SendGrid only. Enable SendGrid click tracking and open tracking.
substitutions	VARCHAR(512)	SendGrid only. Email substitutions as key=value;key=value; string.
transport	ENUM('Google','SendGrid')	Automatically managed by AppSynergy; do not change this value.
error_message	VARCHAR(512)	Error message text if send error. Automatically managed by AppSynergy; do not change this value.

The parasql_email_out_attachment Table

Column Name	Datatype	Comments
id	BIGINT NOT NULL AUTO_INCREMENT	Primary Key
email_out_id	BIGINT NOT NULL REFERENCES parasql_email_out (id) ON DELETE CASCADE	References parasql_email_out.id
attachment	VARCHAR(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 Name	Datatype	Comments
id	BIGINT NOT NULL AUTO_INCREMENT	Primary Key
email_out_id	BIGINT NOT NULL FOREIGN KEY (email_out_id) REFERENCES parasql_email_out (id) ON DELETE CASCADE	References parasql_email_out.id
to_address	VARCHAR(255) NOT NULL	Comma or semicolon separated list of addresses, but typically only one in a personalization
cc_address	VARCHAR(255)	Comma or semicolon separated list of addresses, but typically none in a personalization
bcc_address	VARCHAR(255)	Comma or semicolon separated list of addresses, but typically none in a personalization
subject	VARCHAR(255)	Personalized subject; can also customize email subject with substitutions
substitutions	VARCHAR(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.