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:

-- Email Formatting HTML Sample ---------
    -- bail if no records
  if( not exists(select null from ##t__temptable ) ) return 0;
  print('Got records!');
 
  -- first create new event
  declare @gKey uniqueidentifier; set @gKey = newid()
  declare @jKey uniqueidentifier; set @jKey = newid()
  insert EVENT_MASTER (gKey,Created_By,Source,Str1) values (@gKey,'sp_Email_Formatting_Sample',2,'2140')
 
  --declare @msg varbinary(16); select @msg=textptr(Body) from OUT_EMAIL where jKey=@jKey
  declare @msg varchar(max); select @msg=''
  declare @z cursor;
 
  -- body header
  set @msg = @msg + '

Report Header

' set @msg = @msg + '' -- lines declare @src varchar(25),@trno varchar(10),@trdate datetime,@txt varchar(50),@amt money set @z = cursor fast_forward for select SRC,TRNO,TRDATE,TXT,AMT from ##t__temptable order by SEQ open @z fetch @z into @src,@trno,@trdate,@txt,@amt while(@@fetch_status=0) begin set @msg = @msg + '' fetch from @z into @src,@trno,@trdate,@txt,@amt end deallocate @z -- body footer set @msg = @msg + '

Source	Tr.No	Tr.Date	Text	Amount

‘+@src+’	‘+@trno+’	‘+convert(varchar(10),@trdate,1)+’	‘+@txt+’	‘+convert(varchar(22),@amt)+’

'
 
  -- create email output
  insert OUT_EMAIL (gKey,jKey,Subject,Body) values (@gKey,@jKey,'Manual entries to 2140, last '+convert(varchar(10),@days)+' days',@msg)
  insert OUT_EMAIL_RECIPIENT (jKey,Email) values (@jKey,'info@fuel9.com.com')
 
  -- flag event ready
  update EVENT_MASTER set Status=0 where gKey=@gKey

'
 
  -- create email output
  insert OUT_EMAIL (gKey,jKey,Subject,Body) values (@gKey,@jKey,'Manual entries to 2140, last '+convert(varchar(10),@days)+' days',@msg)
  insert OUT_EMAIL_RECIPIENT (jKey,Email) values (@jKey,'info@fuel9.com.com')
 
  -- flag event ready
  update EVENT_MASTER set Status=0 where gKey=@gKey

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:

Download Sample

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

Email Formatting

Email Formatting

Plain Text

HTML

SSRS – SQL Server Reporting Server

Download Sample

Copyright