Create Colored Text in an SQL generated Email

Question

Post reply

Create Colored Text in an SQL generated Email

AVB

SSChampion

Points: 11418
More actions
October 20, 2004 at 10:59 am

#60333

We have a letter that is sent to customers from our website that is generated by and SQL 2000 SP. How can format the text to be colored? What else can I do to make the content more appealing?? Any pointers in any direction remotely closer then I am would be great! Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

If the email you are sending can contain html tags, you can certainly generate those as part of the query.

Just a random thought

/Wayne

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 2

I do this for several online reports. ASP calls a sproc that has a select query as output.

Here are couple of hints:

If a field could be null, replace it with a nonbreaking space like this:

select isnull(fieldFoo, '&nbsp') ...

I change the font color to red if the value exceeds a certain threshold - you might get some ideas for your sproc from this:

select

(case when abs(myNumericField)< @threshold

then cast(cast(myNumericField * 100 as numeric(9,2))as varchar(10))+'%'

else '<font color = "red">'+ cast(cast(myNumericField * 100 as numeric(9,2))as varchar(10))+'%</font>'

end)

myOutputField...

[font="Courier New"]ZenDada[/font]

AVB SSChampion Points: 11418 More actions · Answer 3

Thanks for the replies. I'll play around with it a bit. I figured you can do pretty much anything with SP's just figured I'd ask.

Greg Milner SSCrazy Points: 2425 More actions · Answer 4

If you want to see an example of a script that generates some SERIOUS HTML (to make a nicely formatted data dictionary) check out http://www.sqlservercentral.com/scripts/contributions/1005.asp

G. Milner

philcart SSC-Forever Points: 47795 More actions · Answer 5

You can also use SQL's sp_makewebtask in conjunction with a template file to generate a HTML page.

I do this for most of my little maintenance reports that are emailed using xp_smtp_sendmail which has an option to use a HTML file as the body of the email.

--------------------
Colt 45 - the original point and click interface