Tuesday, 17 December 2013

Colour Code your Email

So you have set up a whole load of warnings via email,  However they are starting to look messy now.  Its hard to make out what they are saying.

How about checking them into a nice color coded HTML table.  And make it look all lovely and pretty.

Check this code out:-


DECLARE @EmailAddress varchar(1000)

select @EmailAddress = 'Mail@mail.com'


IF (SELECT COUNT(*) FROM <Table>
where [Feild1] <> '') > 0
BEGIN

DECLARE @tableHTML  NVARCHAR(MAX) ;
DECLARE @Mailsubject nvarchar(255)

SET @Mailsubject = 'DBA - Warning its all dead Dave'

SET @tableHTML =
  N'<html><head><style type="text/css">'+
  N'body'+
  N'{'+
  N'font: 12px arial'+
  N'}'+
  N'</style></head>'+
  N'<body>'+
  N'<H3>Dave Its Dead, Its Dead Dave</H3>' +
  N'<table border="1">' +
       N'<th>Feild1</th>' +
  N'<th>Feild2</th>' +
  N'<th>Feild3</th>' +
  N'</tr>' +
  ISNULL(CAST ( (



  select

    case
when [Feild1] like '%somthing%'
then
  '#F78181'
   when [feild1] like '%somthingelse%'
then
  '#FF0000'

   when [feild1] like '%Anotherthing%'
then
  '#00FFFF'



  else '#FFFFFF'
  end AS [@bgcolor],

  td = feild3, ''
, td = feild2, ''
, td = [feild1]
from Table
where [feild1] <> ''
FOR XML PATH('tr'), TYPE
  ) AS NVARCHAR(MAX) ),'') +
  N'</table></body></html>' ;

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Email_Profile',

@recipients= @EmailAddress,
  @subject = @Mailsubject,
  @body = @tableHTML,
  @body_format = 'HTML' ;

END