Imagination Is All You Need!
We all know that Vista can send emails for report processes, error checking and data updates, but did you know that you can create eye catching emails with images, color and different font styles? Most of the time a plain text email is just fine and all that you need, but what about when a fancier email is needed? It may be that the email is going to management, or you want to catch the attention of your audience.
Below is a sample of an email that is delivered to all new employees after they start asking them to take a series of training courses. It is all done with a stored procedure and a scheduled Enterprise Server job. Click here to view the content that I reference within this article. You’ll need your Support login information.
Within the body of the procedure you can see that simple HTML commands were used to create the email body.
For the header we set the color by using the html code and color number (color: #7F7F7F). Did you know that you can find these color codes by doing a simple google search for “HTML Color Codes?”
Our cyber security email was created using tables. That allowed us to put different information into sections. The HTML table codes were used to accomplish this (<tr><td></td></tr>. )
Bolding a word is easy by using the bold commands around the word (<b></b> or <strong></strong>). Font sizes can be changed or links can be inserted into the document. To insert a link, just use the html commands to reference the site you want to access.
<a href=”https://training.cyveillance.com/login/144/client.aspx”>Click here</a> to watch the online training</span>’
To really make your emails “Eye Catching”, try inserting a graphic or two. Use the Vista special command [CID] where you want your image to appear.
‘<tr><td style=”width:96.0pt;padding:0in 4.0pt 0in 4.0pt”>’ + CHAR(13) +'[CID]’ + CHAR(13) +'<td style=”font-size:9.0pt;width:5.0in;padding:0in 5.4pt 0in 0in”>’ + CHAR(13) +<li><img width=26 height=20 src=””>’
Whenever you insert a graphic, you will need to specify where that graphic is stored on the server. In the procedure for the cyber security email, the image location is in the select statement for the email.
— Send one confirmation email
SELECT @sSchedBlock = ‘
item_type_code=M##description=IT Training–‘ + @sRecipient + ‘##command_line=’ +'<COMMAND>’ + CHAR(13) +'<EMAIL><RECIPIENT>’ + @sRecipient + ‘</RECIPIENT>’ + CHAR(13) +@sSubject + CHAR(13) +’<EMBED>[ESDIR]\Joblets\lock.jpg</EMBED>’ + CHAR(13) +@sBody + CHAR(13) +'</EMAIL></COMMAND>’ + CHAR(13)
EXEC ES_SchedJob @nRet OUTPUT, @sRet OUTPUT, ‘PDSADMIN’, NULL, ‘A’, NULL, NULL, @sSchedBlock
Another example of an email that is a little simpler than the example above is our Birthday email. All we have done is create a procedure that sends an email to everyone on their birthday (see birthday procedure attached)
In this procedure we set the font, the color, and the text size. The [CID] command is used to show where we want the image to appear in the email.
‘<P><FONT face=Cambria color=#5F81DA size=5>‘ + CHAR(13) +’Best Wishes on your Birthday.<BR>We hope you have a very special day!’ + CHAR(13) +'</FONT></FONT></P>’ + CHAR(13) + CHAR(13) +'[CID]’ + CHAR(13) +'</DIV>’ + CHAR(13) +'</BODY>’ + CHAR(13)
Again, as in the cyber security email, we define where the image is stored in the select statement of the procedure.
SELECT @sSchedBlock =
‘item_type_code=E##description=Birthday email–‘ + @sRecipient + ‘##command_line=’ +'<COMMAND><FORMAT>HTML</FORMAT><HTMLBODY>’ + @sBody + ‘</HTMLBODY>’ + CHAR(13) + ‘<RECIPIENT>’ + @sRecipient + ‘</RECIPIENT>’ + CHAR(13) + @sSubject + CHAR(13) +’<EMBED>[ESDIR]\Joblets\birthday.jpg</EMBED>‘ + CHAR(13) +'</COMMAND>’ + CHAR(13)
EXEC ES_SchedJob @nRet OUTPUT, @sRet OUTPUT, ‘PDSADMIN’, NULL, ‘A’, NULL, NULL, @sSchedBlock
Creating attractive emails can be done using other methods as well. They don’t have to all be done using a stored procedure. As an alternative to a stored procedure, emails can be “prettied up” using xml jobs through enterprise server.
Below is an example of an email that we send out providing various counts of certain categories (see counts.xml attached). As you can see, we have changed the color of the header. For the result set we used HTML to create a list of information.
The body of the email is simple html changing the color and font. The size is also changed. Simple bold commands and breaks are inserted into the remaining text.
<h2><FONT face=Technical color=#c00000>Current Firm attorney/employee counts.</font></h2>
<FONT face=Technical> The numbers are current as of [TODAY]. Active is defined as anyone with an active or leave status.
These numbers <b>do not</b> include anyone with a temporary or contract status. People seconded to another member firm are NOT included in the numbers unless specified. Retired Partners (RI) with an Of Counsel agreement <b>are</b> included in the numbers. The full-time equivalency is found in parenthesis next to the headcount number.<br><br>
[RESULT_SET]
The result set is comprised of simple sql statements using an html break command (<br>) to create a new line.
select ‘Total Firm Count:’, count(empno), ‘(FTE:’, cast(sum(fte)as decimal (7,2)),’)’, ‘<br>’ from fjbasic where hr_status in (‘A’, ‘C’) or (class = ‘RI’ and hr_status = ‘G’)
select ‘Total Active attorneys (Patent Agents not included):’, count(empno), ‘(FTE:’, cast(sum(fte)as decimal (7,2)),’)’, ‘<br>’ from fjbasic where (hr_status in (‘A’, ‘C’) and is_atty = ‘Y’ and class not in (‘AG’)) or (class = ‘RI’)
select ‘Number of active partners:’, count(empno), ‘(FTE:’, cast(sum(fte)as decimal (7,2)),’)’, ‘<br>’ from fjbasic where class = ‘PT’ and hr_status in (‘A’, ‘C’)
So remember, all you need is a little imagination, basic knowledge of HTML (remember Google is a great source) and an idea. Happy E-Mailing!
Meribeth Carter, Co-Chair of the PDS UGA Steering Committee
Director of Human Resources
Norton Rose Fulbright
Meribeth.carter@nortonrosefulbright.com