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