Solved

Using sp_send_dbmail to generate HTML-formatted emails

Posted on 2014-09-26
4
530 Views
Last Modified: 2014-10-07
Have began using sp_send_dbmail in SQL Server 2005 to send me results of a query against one of our process schedule tables as an HTML-formatted email, using the following code :

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Upcoming Process Schedule List</H1>' +
    N'<table border="1">' +
    N'<tr style="background-color: #5D7B9D; font-weight: bold; color: white;"><th>Year</th><th>Period</th>' +
    N'<th>ScheduledDate</th><th>SalesAnalysis</th>' +
    N'<th>Status</th></tr>' +
    CAST (
    (
    SELECT td = FY,       '',
                    td = PERIOD, '',
                    td = CAST(CREATED_DATE AS VARCHAR(23)), '',
                    td = SALES_COSTS, '',
                    td = STATUS
              FROM [SCHEDULEDB].dbo.tbl_schedules
              WHERE SCHEDULED_DATE >= getdate()
              ORDER BY SCHEDULED_DATE        
              FOR XML PATH('tr'), TYPE
    )
    AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='raymurphy@somewehere.com',
    @subject = 'Upcoming Process Schedule List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

This works fine, and sends me a nicely-formatted HTML email as expected. But out of curiosity, I was wondering what changes I would need to have the table rows formatted as alternate row colours or even if this would be possible using sp_send_dbmail as above ?

Thanks in advance.
0
Comment
Question by:raymurphy
  • 2
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
Comment Utility
You could cram a custom style sheet in the beginning of that block to accomplish what you're looking for, all without modifying the code you've already created for the table:

http://www.textfixer.com/tutorials/css-table-alternating-rows.php
0
 

Author Comment

by:raymurphy
Comment Utility
Sorry for the delay, ryanmccauley - thanks for that, will have a look at incorporating that approach.
0
 

Author Comment

by:raymurphy
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for raymurphy's comment #a40365465

for the following reason:

Looks like this could be useful, so will have a look at incorporating that approach.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
If my post is the direction you end up going with, can you accept my answer as the solution?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now