Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using sp_send_dbmail to generate HTML-formatted emails

Posted on 2014-09-26
4
Medium Priority
?
609 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 2000 total points
ID: 40350104
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
ID: 40365465
Sorry for the delay, ryanmccauley - thanks for that, will have a look at incorporating that approach.
0
 

Author Comment

by:raymurphy
ID: 40366061
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
ID: 40366027
If my post is the direction you end up going with, can you accept my answer as the solution?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview

972 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