raymurphy
asked on
Using sp_send_dbmail to generate HTML-formatted emails
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>P eriod</th> ' +
N'<th>ScheduledDate</th><t h>SalesAna lysis</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_sched ules
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@som ewehere.co m',
@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.
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>P
N'<th>ScheduledDate</th><t
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_sched
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@som
@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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
If my post is the direction you end up going with, can you accept my answer as the solution?
ASKER