Solved

Using sp_send_dbmail to generate HTML-formatted emails

Posted on 2014-09-26
4
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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