Outbound Mail

You can use AppSynergy's outbound mail features to send notification emails from your triggers and other PL/SQL code. In order to send email you must first create a SendGrid account and then link that account to AppSynergy.

Linking AppSynergy to SendGrid

Follow these steps:

Create a SendGrid account - You can do so here.
Link Your Domain - Using their control panel, link your domain and verify it via Settings > Sender Authentication > Domain Authentication.
Create an API Key - Using their control panel, create an API key via Settings > API Key. Be sure to grant Mail Send privileges to the API key.
Link AppSynergy to your API Key - In AppSynergy go to My Apps then click on Account Settings. There you can enter your SendGrid API key and preferred default From email address.

Example: Text Email

To send a simple text email just insert a row into the parasql_email_out table like this:

	INSERT INTO parasql_email_out (to_address, subject, body)
	VALUES ('joe@xyzdomain.com', 'My subject', 'Hello World');
	

Example: HTML Email

Similar to the example above but with html_format set to TRUE:

	INSERT INTO parasql_email_out (from_address, to_address, subject, body, html_format)
	VALUES (parasql_user_email(), 'joe@xyzdomain.com', 'My subject', '<h1>HTML email body</h1>', TRUE);
	

In this example the parasql_user_email() function returns the email address of the currently logged in user.

Example: Email with Attachment

To send an email with a document field attachment add a row to the parasql_email_out_attachment table as follows:

	BEGIN
	
	  DECLARE v_attachment VARCHAR(512);
	
	  -- get document for attachment
	  SELECT MyDocumentField 
	    INTO v_attachment 
	  FROM MyTable 
	  WHERE MyTable_ID = 1234;
	
	  -- create the email
	  INSERT INTO parasql_email_out (from_address, to_address, subject, body) 
	  VALUES (parasql_user_email(), 'joe@xyzdomain.com', 'My subject', 'My body...');
	
	  -- add the attachment
	  INSERT INTO parasql_email_out_attachment (email_out_id, attachment) 
	  VALUES (last_insert_id(), v_attachment);
	
	END
	

Outbound Mail Tables

The following tables are used for sending and managing email. You can query them for diagnostic purposes.

parasql_email_out
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; joe.blow@somedomain.com or Joe Blow <joe.blow@somedomain.com>
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	Restricted to approved senders. Typically set via the parasql_user_email() function or hard coded to something like noreply@mydomain.com
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.
user_id	VARCHAR(64) NOT NULL DEFAULT USER()	User ID of sender.
unsubscribe_groups	VARCHAR(128)	DEPRECATED. SendGrid only. Semicolon separated list of SendGrid Unsubscribe Group IDs.
enable_tracking	BOOLEAN NOT NULL DEFAULT FALSE	DEPRECATED. SendGrid only. Enable SendGrid click tracking and open tracking.
substitutions	VARCHAR(512)	DEPRECATED. 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.

parasql_email_out_attachment
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(512) NOT NULL COMMENT 'GCSFILE'	contents of a Document Field, Image Field or Signature Field