Email Formatting

There are three ways to format the message content (body) of an email in the Boomerang framework. Plain text, HTML and SSRS (SQL Server Reporting Server). The following examples show how to define each format.

Plain Text

Plain text formatting is a quick and easy way to specify an email message. Choosing plain text formatting is best when you want to make sure that the email message is easily readable regardless of email client or device used by the recipient. Plain text is the default email message formatting option and the content of the email message (body) specified in OUT_EMAIL.Body

Example:

-- Create email job/output
INSERT INTO Boomerang..OUT_EMAIL (gKey, jKey, [From], [Subject], [Body] )
SELECT
t1.gKey,
t1.jKey,
@From_Address,
t1.[Subject] + t1.My_Dynamic_Subject_Line,
'This is the start of the body:' + char(13) + char (10) + -- Return and line feed
 t1.Customer_Name + ' | ' +
 t1.Part_Number + ' | ' +
 t1.Part_Description + ' | ' +
'Qty: ' + t1.Qty_Requested + ' | ' +
'Ship Date: ' + t1.Ship_Date
 
FROM #__Supplier_Reminder t1

Result:

gKey                   jKey                    From_Add            Subject                               Body
--------------------   ----------------------- ------------------- ------------------------------------- --------------------------------------
09BF9A31//675D5141C3   70B3532F//8AAEB9D69FFB  Boomerang@fuel9.com Delivery Reminder for Order: 15515400 This is the start of the body:
                                                                                                         Facton GMBH | IA-645-39 | O-ring Rev C | Qty: 1500 | Ship Date: 2011-14-15

HTML

When formatting an email message in HTML the same approach as plain text applies. HTML tags needs to be included in the OUT_EMAIL.Body so that text can be rendered at recipients end as HTML when it arrives. This option allows for more formatting possibilities but including the correct HTML tags without a without a WYSIWYG editor can be tedious and it is our recommendation to use this option only for the most basic formatting needs.

Example:

--- create demo data ----
create table #my_emails 
	(
	 Id int 
	,Customer_Ref varchar(25)
	,Email_Address varchar(25)
	,jKey uniqueidentifier
	,Email_Body_HTML varchar(1000)
	)
 
create table #my_email_body_rows
	(
	 Id int
	,Id_Row int
	,Line varchar(10)
	,Part_Number varchar(10)
	,Part_Desc varchar(10)
	,Qty varchar(3)
	)
 
insert #my_emails (Id, Customer_Ref, Email_Address, jKey)
values (1, 'Bob Slydell', 'info@fuel9.com', NEWID())
 
insert #my_emails (Id, Customer_Ref, Email_Address, jKey)
values (2, 'Bob Porter', 'info@fuel9.com', NEWID())
 
insert #my_email_body_rows
values (1, 100, '10', '4567', 'Bike A', '3')
 
insert #my_email_body_rows
values (1, 101, '20', '1267', 'Bike C', '1')
 
insert #my_email_body_rows
values (1, 102, '30', '5567', 'Bike D', '12')
 
insert #my_email_body_rows
values (2, 103, '10', '5567', 'Bike D', '1')
 
insert #my_email_body_rows
values (2, 104, '20', '8990', 'Bike E', '1')
 
--- declarations ---
declare 
	 @gKey uniqueidentifier
	,@Email_Body_HTML varchar(1000)
	,@Id int
	,@Customer_Ref varchar(25)
	,@Id_Row int
	,@Line varchar(10)
	,@Part_Number varchar(10)
	,@Part_Desc varchar(10)
	,@Qty varchar(3)
 
set @gKey = NEWID()
 
 
--- email loop ---
while 1 = 1
	begin
		--- get email records ---
		select top 1 
		  @Id = Id
		, @Customer_Ref = Customer_Ref 
		from #my_emails 
		where Email_Body_HTML is null
 
		--- break loop if no more records ---
		if @@ROWCOUNT = 0 break
 
		--- set beginning of HTML string ---
		set @Email_Body_HTML = '<!DOCTYPE html><html><body>Dear ' + @Customer_Ref + ',<br>' +
                                       'We are happy to let you know that your order...<br><br>' +
                                       '<table><tr><td>Line</td><td>Part Number</td><td>Desc</td><td>Qty</td></tr>' 
 
		--- email body loop ---
		while 1 = 1
			begin
				select top 1
				  @Id_Row = Id_Row
				, @Line = Line
				, @Part_Number = Part_Number
				, @Part_Desc = Part_Desc
				, @Qty = Qty
				from #my_email_body_rows
				where Id = @Id
 
				--- break loop if no more records ---
				if @@ROWCOUNT = 0 break
 
				--- update HTML string ---
				set @Email_Body_HTML = @Email_Body_HTML + 
				                      '<tr><td>' + @Line + '</td><td>' + @Part_Number + '</td><td>' + 
						       @Part_Desc + '</td><td>' + @Qty + '</td></tr>' 
 
				delete from #my_email_body_rows where Id_Row = @Id_Row
 
			end
 
		--- set end of HTML string ---
		set @Email_Body_HTML = @Email_Body_HTML + '</table></body></html>'
 
		--- update temp table with email body ---
		update #my_emails 
		set Email_Body_HTML = @Email_Body_HTML
		where Id = @Id
 
		--- reset HTML string ---
		set @Email_Body_HTML = null
 
	end
 
 
--- email out ----
insert [dbo].[EVENT_MASTER] 
	(
	gKey
	)
values
	(
	@gKey
	)
 
insert [dbo].[OUT_EMAIL]
	(
	 gKey
	,jKey
	,[Subject]
	,Body
	,IncludeKey
	)
select
	 @gKey
	,jKey
	,'Order Id: ' + CAST(Id as varchar(3))
	,Email_Body_HTML
	,0 -- don't include tracking key
from #my_emails
 
insert [dbo].[OUT_EMAIL_RECIPIENT]
	(
	 jKey
	,Email
	)
select
	 jKey
	,Email_Address
from #my_emails
 
update [dbo].[EVENT_MASTER]
set [Status] = 0
where gKey = @gKey
 
 
drop table #my_emails
drop table #my_email_body_rows

Result:
Email form

SSRS – SQL Server Reporting Server

The third and most flexible option for formatting email messages is to use Reporting Services. You can include graphs, a logo and use the rich formatting provided by SSRS. Any RS report can be rendered in the email message (body) as HTML. Message content inserted into the ‘Body’ (OUT_EMAIL.Body) column in OUT_EMAIL will be added at the top of the email body and the Reporting Server Report will be rendered below. If OUT_EMAIL.Body is omitted only the SSRS will be rendered in the email message (body). For a Reporting Server report to be rendered in-line as the email message (body) EVENT_CONTENT.IsAttachment should be set to 0 (zero) i.e. in-line as suppose to the default 1 (one) i.e. email message attachment

Example:

Insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, IsAttachment ) values (@gKey,@jKey,@aKey,2,'Misc/Finance/CashFlowScorecard',0)

The sample below is a dedicated “email content” report that can be used when reports are delivered as attachments or for other straightforward notifications.

Example:

declare @gKey uniqueidentifier; set @gKey = newid()
declare @jKey uniqueidentifier; set @jKey = newid()
declare @aKey uniqueidentifier;
 
-- first off: create new event
insert EVENT_MASTER (gKey, Source, Created_By ) values (@gKey, null, 'Michael')
 
-- create email jog and add recipients
insert OUT_EMAIL (gKey,jKey,Subject,Body,ReplyTo,[From],IncludeKey) 
 
values (@gKey,@jKey,'Please reply to this one',null,'replywhatcanbomerangdo@fuel9.com','"What Can Boomerang Do" ', 0)
insert OUT_EMAIL_RECIPIENT (jKey, [Name], Email) values (@jKey, 'Display Name', 'email@gmail.com')
 
-- call the "email body" report
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path, IsAttachment) values (@gKey,@jKey,@aKey,2,'Misc/Email_Content', 0)
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'Greeting_Phrase','Dear')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'First_Name','John')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'Last_Name','Smith')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'Show_Body_Text_Type','2')
insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'Body_Text_Type_1','11111')
--insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'Body_Text_Type_2','22222')
--insert CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey,'Body_Text_Type_3','33333')
 
-- add a sales report as pdf attachment
set @aKey = newid()
insert EVENT_CONTENT (gKey,jKey,aKey,Src_Type,Path,[Format]) values (@gKey,@jKey,@aKey,2,'Misc/Sales_by_Region','PDF')
 
-- flag event ready
update EVENT_MASTER set Status=0 where gKey=@gKey

Result:
Email form

Download Sample

Email Formatting | Description: Including Plain text, HTML and SQL Server Reporting Server in-line formatting.