Creating a Letter Style e-mail in HTML format using sp_send_dbmail

Question

Creating a Letter Style e-mail in HTML format using sp_send_dbmail

jfc-582176

Old Hand

Points: 340
More actions
February 23, 2009 at 7:15 am

#128537

Hi All,
I've been using sp_send_dbmail succesfully within an update trigger to send simple text based e-mails to users to acknowledge the closure of a support call etc.
The text includes values from the call record in the database such as their Username and the Call Description / Solution text. While these e-mails contain all the pertinent information the user requires, they don't exactly look great so I have been experimenting with the HTML capabilities of the stored procedure with mixed success.
Despite fairly extensive searching, what I've found is that for all the numerous examples available e.g.
http://technet.microsoft.com/en-us/library/ms190307.aspx
they all suggest that you have to include dynamic database fields in the body of the e-mail as tabular results sets using 'CAST AS((...FOR XML PATH))NVARCHAR(MAX)'.
All I'm really looking to return within the HTML body of the e-mail is something like the following :
--------------------------------------------------------------------------
Dear @Username,
The following log has now been closed :
Call Description : @CallDescriptionText
Call Solution : @CallSolutionText
--------------------------------------------------------------------------
Has anyone discovered a way to do this?
Indeed, is it actually possible to make the data dynamic in this way within the HTML body?
Any help would be very much appreciated.
Kind Regards,
John Cameron - MCTS

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

w.durkin@online.de Hall of Fame Points: 3943 More actions · Answer 1

Hi Jfc,

have you tried creating the template you want with all the html tags in it and then using REPLACE to remove your placeholders?

Example:

declare @htmltext varchar(max)

declare @username varchar(255)

declare @calldescription varchar(255)

declare @callsolution varchar(255)

set @username = 'Test User'

set @calldescription = 'Test Call'

set @callsolution = 'This fixes the problem'

set @htmltext='

'

set @htmltext=REPLACE(@htmltext,'#USERNAME#',@Username)

set @htmltext=REPLACE(@htmltext,'#DESCRIPTION#',@calldescription)

set @htmltext=REPLACE(@htmltext,'#SOLUTION#',@callsolution)

select @htmltext

This should point you in the general direction of what I mean. It is by no means perfect, but we used something like this a few years ago to inform customers when their parcels had been shipped out, and it worked quite well.

You can play around with the html, maybe putting it into a table using it as a template store, just in case you want to send other emails using a similar REPLACE type of system.

I would also suggest that you change the trigger you mentioned to write the email into a table which is then processed by an email job in its own right. This makes the sending of emails asynchronous, reducing the possibility that your trigger slows down the rest of the system (the trigger fires into the table and doesn't depend on sp_send_dbmail being up and running).

The likelyhood is maybe small, but you should definitely plan for the worst! 🙂 It is better that the emails are all stored in a table and not being processed because the mail system is broken, than not being able to close calls. Just my opinion.

HTH

GermanDBA

Regards,

WilliamD

jfc-582176 Old Hand Points: 340 More actions · Answer 2

Thanks GermanDBA - that's very helpful.

Kind Regards,

John

w.durkin@online.de Hall of Fame Points: 3943 More actions · Answer 3

Hi Jfc,

I just noticed that my sample code didn't have the html code in it - the forum seems to have thrown it away (doesn't like html tags to be inserted i guess). It should look like this:

Dear #USERNAME#,
The following log has now been closed:
Call Description: #DESCRIPTION#
Call Solution: #SOLUTION#

You can obviously use all html tags and design stuff you like, but you get the idea I hope.

Regards

GermanDBA

Regards,

WilliamD

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I used to also recommend putting any email type activity into a table and having a separate job process it, but because database mail uses Service Broker it is asynchronous and is pretty safe to put in triggers.

Yeah the fact that you can't put html or xml tags in the forums is a bit frustrating. I believe that they are working on correcting that issue. I know it is on the wish list of several regular contributors.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

w.durkin@online.de Hall of Fame Points: 3943 More actions · Answer 5

Hi Jack,

I agree with the safety of db_mail, just thought it prudent to point out that doing things like that inside a trigger can be a little risky. Trigger implementation like this can bite you in the a$$ at a later date, and cause headaches when troubleshooting performance problems.

I know you know this, you don't have SLJ as your avatar for no reason! 😛 I wouldn't dream of trying to dictate to a council member! :w00t:

GermanDBA

Regards,

WilliamD

Steve E SSC Journeyman Points: 89 More actions · Answer 6

Hi there,

I've also been using sp_send_dbmail recently to do email shots from a job that gets invoked once a week. The format of the email is in HTML and includes certain styles, fonts etc.

Prefixing the "CAST (( SELECT" statement i placed the header values as follows:-

N' ' +
N' Click ' +
N' ' +
N' to login and view your orders.' +
N'
' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
CAST ( ( SELECT td = .........

I have also used XML FOR to product Excel reports which took a lot of doing but works very well using the same principle above. You can even specify alignment, decimals or numerics, font, style etc.

If you want a copy of this let me know!

Apologies if i've broken any rules - this is my 1st reply!

Steve

p.s. i cannot get the code to paste into this msg as it keeps stripping out the XML?! Anyone?

rmanjunath_cse Valued Member Points: 71 More actions · Answer 7

Hi Guys..

i hav one similar prob

am using cellspacing in my table

something like this

declare @tableHTMl varchar(max)

Select @tableHTMl ='<TABLE cellpadding=3 cellspacing=3>'

Select @tableHTMl =@tableHTMl+""

............................

Select @tableHTMl =@tableHTMl+"</table>"

but in the output mail am not able to see the cellspcing effect but i can see the cellpadding effect

my objective is to get border around the cells without using border property

can someone pls advice me

thxz in advance