SQL Email / Html generation with colour

Morning all,

I'm having big problems generating an email using dbmail, i can generate an email with text and unicode characters but I can't seem able to add colour based on content.

This is the test code I'm using:

DECLARE @xml NVARCHAR(MAX)
DECLARE @Body NVARCHAR(MAX)
DECLARE @Mon NVARCHAR(5) = 'Yes'
DECLARE @Tue NVARCHAR(5) = 'No'
DECLARE @Wed NVARCHAR(5) = 'No'
DECLARE @Thu NVARCHAR(5) = 'Yes'
DECLARE @Fri NVARCHAR(5) = 'Yes'


BEGIN    
		SET @xml = CAST((	
			SELECT
			'Bob' AS 'td','',
			'Driver' AS 'td','',
			CASE 
			WHEN (@Mon = 'Yes') THEN N'✓'      --Needs to be Green
			ELSE N'✗'			--Needs to be Red
			END AS 'td','',
			CASE 
				WHEN (@Tue = 'Yes') THEN N'✓'
				ELSE N'✗'
			END AS 'td','',
			CASE 
				WHEN (@Wed = 'Yes') THEN N'✓'
				ELSE N'✗'
			END AS 'td','',
			CASE 
				WHEN (@Thu = 'Yes') THEN N'✓'
				ELSE N'✗'
			END AS 'td','',
			CASE 
				WHEN (@Fri = 'Yes') THEN N'✓'
				ELSE N'✗'
			END AS 'td','',
			'10' AS 'td','',
			'Van' AS 'td',''
		
		FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
  
			BEGIN
				SET @Body = '
				<body>

				<table><tr><td valign="top" align="left"><font size="3" face="calibri" color="black"> 
				Test email body.
				<br /><br />
				</font></td></tr>
				</table>
		
				<table border = 1><font size="3" face="calibri" color="black">
				<tr>
				<th> Name </th> <th> JobGroup </th> 
				
				<th width="40"> Mon </th> 
				<th width="40"> Tue </th> 
				<th width="40"> Wed </th> 
				<th width="40"> Thu </th> 
				<th width="40"> Fri </th> 
				
				<th> Distance/Miles </th> <th> Transport </th></tr>
				'
				SET @Body = @Body + @xml +
				'
				</table>
				<table>	
				</font></td></tr>
				<tr><td valign="top" align="left"><font size="3" face="calibri" color="black">
				<br />
				Test email footer <br />
				<br />

				</table>
				</body>'
				EXEC msdb.dbo.sp_send_dbmail 
					@profile_name='SBS Info DBMail Profile',
					@recipients= 'my@email.com',
					@subject = 'Test email',
					@body = @Body,
					@body_format = 'HTML'
				END
END

I've tried to add html directly but I assume the XML encoding didn't like it...

CASE 
		WHEN (@Mon = 'Yes') THEN N'<font color="green"> ✓ </font>'
		ELSE N'<font color="red"> ✗ </font>'
END AS 'td','',

Any idea how I might achieve this?

Many thanks

Dave

Have you looked into Ssrs subscriptions

Here is a template that you can start with:

    Set Nocount On;

Declare @body nvarchar(max)
      , @xmlResults varchar(max)
      , @tableHeader varchar(max)
      , @recipients varchar(max) = 'list of recipients'
      , @cc_recipients varchar(max) = 'list of cc recipients';

 --==== Create the table header
    Set @tableHeader = cast((Select html.hdr1 As th, ''
                                  , html.hdr2 As th, ''
                                  , html.hdr3 As th, ''
                                  , html.hdr4 As th, ''
                               From (  
                             Values ('Header Column 1', 'Header Column 2', 'Header Column 3', 'Header Column 4')
                                    )  As html(hdr1, hdr2, hdr3, hdr4)
                                For xml Path('tr'), elements) As varchar(max));

 --==== Get the results as an XML table
    Set @xmlResults = cast((Select col1 As td, ''
                                 , col2 As td, ''
                                 , col3 As td, ''
                                 , col4 As td, ''
                              From sometable
                               For xml Path('tr'), elements) As varchar(max));
    
 --==== Send Notification if we have results
     If @xmlResults Is Not Null 
  Begin

--==== Setup the CSS style of the message
    Set @body =  '<style type=''text/css''>';
    Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
    Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
    Set @body += 'th {font-size:10pt; text-align:left; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
    Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
    Set @body += '</style>'

 --==== Setup the body of the message
    Set @body += '<html><body><p>...some body text here...</p> ';

 --==== Setup the table with the list
    Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';

 --==== Close the body and html
    Set @body += '</body></html>';

 --==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
        @profile_name = 'SBS Info DBMail Profile'
      , @from_address = 'SomeFromAddress@SomeDomain.com'
      , @reply_to = 'ValidReplyToAddress@SomeDomain.com'
      , @recipients = @recipients
      , @copy_recipients = @cc_recipients
      , @subject = 'Subject Here'
      , @body_format = 'HTML'
      , @body = @body;

    End 
     Go

The key will be how you define the table elements - and how that will be defined in the style sheet.

Let XML Path do the hard work for you:

Case @Mon When 'Yes' Then 'green' Else 'red' End As [td/font/@color],
Case @Mon When 'Yes' Then N'✓' Else 'N✗' End As [td], '',