Emailing in HTML

Question

Emailing in HTML

ghostiv

SSC Rookie

Points: 39
More actions
July 23, 2008 at 7:19 am

#194314

Hi All,
I am trying to format an auto-generated email from SQL into HTML, i've sussed a bit of it but cant seem to wrap my head around the table. here is the code im currently using. Dont know if anyone can help!!
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
CAST(( SELECT td = Department, '',
td = SLA, '',
td = Abandoned, '',
td = IntervalSLA
FROM (SELECT ReportDate,
Department,
CAST(ROUND(SLA, 2) * 100 AS nvarchar) + N'%' AS SLA,
CAST(ROUND(Abandoned, 2) * 100 AS nvarchar) + N'%' AS Abandoned,
CAST(ROUND(IntervalSLA, 2) * 100 AS nvarchar) + N'%' AS IntervalSLA,
DepartmentOrder,
OrderBy
FROM CallCentreDashBoard.dbo.v_DailySLA) AS d
ORDER BY DepartmentOrder, OrderBy
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) + N' ' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'HewitG@cpwplc.com',
@subject = 'Daily SLA',
@body = @tableHTML,
@body_format = 'HTML' ;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

planetmatt SSCrazy Points: 2207 More actions · Answer 1

At present, that code will just output HTML table rows and cells.

You need to concatenate the opening and closing table tags along with any column headings inside TH tags. Saying that, the code as is will probably display in Outlook as it isn't too strict about well formed HTML.

You could also wrap an ISNULL around that existing code block and output a single row and cell with a "no data found" message. Otherwise if there are no rows returned from the query, the whole of @tableHTML will be NULL so even if you concatenate table headings, the concatenation with the NULL will give you an empty email.

I also add a CSS class to each row using the ROW_NUMBER() function so that I can do alternating row colors. I put this before the first "td ="

case when ROW_NUMBER() OVER(order by [field list defining unique row])%2 = 0 then 'even' else 'odd' end AS '@class',

and then add this to the CSS rules in the HEAD part of the HTML:

tr.even{background-color:#fff;}

tr.odd{background-color:#eee;}

nts 4650 Grasshopper Points: 13 More actions · Answer 2

For set a SQL to return HTML CODE with mailto you can exec this sql:

Dim Sql As String = "Select Coalesce(NombreCliente,'') +'

Teléfono:'+ Coalesce(TelefonoParticular,'') + '

Movil:' + Coalesce(TelefonoMovil,'') + "

Sql = Sql & "'

Mail: <a href=' + char(34) + 'mailto:' + Coalesce(mailcli,'') + char(34) + '>' + Coalesce(MailCli,'') + '</a>'"

Sql = Sql & " From Cliente Where Cliente=" & Cliente

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

nts 4650 (6/7/2013)
For set a SQL to return HTML CODE with mailto you can exec this sql:
Dim Sql As String = "Select Coalesce(NombreCliente,'') +'
Teléfono:'+ Coalesce(TelefonoParticular,'') + '
Movil:' + Coalesce(TelefonoMovil,'') + "
Sql = Sql & "'
Mail: <a href=' + char(34) + 'mailto:' + Coalesce(mailcli,'') + char(34) + '>' + Coalesce(MailCli,'') + '</a>'"
Sql = Sql & " From Cliente Where Cliente=" & Cliente

NOTE: 5 year old thread and completely irrelevant to the OP.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]