That was helpful, but I'm still having an issue depending on the email system the is being used (see attachment).
When viewing in Outlook the report displays correctly, but when viewed in Yahoo Mail or on the Blackberry some borders and color tags are being stripped out.
Here is the code I'm using, any suggestions?
DECLARE @bodyMsg NVARCHAR(MAX)
DECLARE @subject NVARCHAR(MAX)
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @EmailProfile NVARCHAR(MAX)
DECLARE @EmailTitle NVARCHAR(MAX)
DECLARE @EmailDateRange NVARCHAR(MAX)
DECLARE @ReportMonth NVARCHAR(MAX)
DECLARE @MtdTitle NVARCHAR(MAX)
DECLARE @DailyTitle NVARCHAR(MAX)
DECLARE @recipientsMain NVARCHAR(MAX)
DECLARE @recipientsMain1 NVARCHAR(255)
DECLARE @recipientsMain2 NVARCHAR(255)
DECLARE @recipientsCC NVARCHAR(MAX)
DECLARE @recipientsCC1 NVARCHAR(255)
DECLARE @recipientsCC2 NVARCHAR(255)
DECLARE @recipientsBCC NVARCHAR(MAX)
DECLARE @recipientsBCC1 NVARCHAR(255)
SET @EmailProfile = (SELECT TOP 1 EMAIL_PROFILE_NAME
FROM TMP_ONTIME_SUMMARY);
SET @EmailDateRange = (SELECT TOP 1 REPORT_DATE_RANGE
FROM TMP_ONTIME_SUMMARY);
SET @recipientsMain = ''
SET @recipientsMain1 = (SELECT TOP 1 EMAIL_RECIPIENT_MAIN_1
FROM TMP_ONTIME_SUMMARY);
SET @recipientsMain2 = (SELECT TOP 1 EMAIL_RECIPIENT_MAIN_2
FROM TMP_ONTIME_SUMMARY);
IF LEN(@recipientsMain1) > 0
SET @recipientsMain = RTRIM(@recipientsMain1)
IF LEN(@recipientsMain2) > 0
SET @recipientsMain = @recipientsMain + ';' + RTRIM(@recipientsMain2)
SET @recipientsCC = ''
SET @recipientsCC1 = (SELECT TOP 1 EMAIL_RECIPIENT_CC_1
FROM TMP_ONTIME_SUMMARY);
SET @recipientsCC2 = (SELECT TOP 1 EMAIL_RECIPIENT_CC_2
FROM TMP_ONTIME_SUMMARY);
IF LEN(@recipientsCC1) > 0
SET @recipientsCC = RTRIM(@recipientsCC1)
IF LEN(@recipientsCC2) > 0
SET @recipientsCC = @recipientsCC + ';' + RTRIM(@recipientsCC2)
SET @recipientsBCC = ''
SET @recipientsBCC1 = (SELECT TOP 1 EMAIL_RECIPIENT_BCC
FROM TMP_ONTIME_SUMMARY);
IF LEN(@recipientsBCC1) > 0
SET @recipientsBCC = RTRIM(@recipientsBCC1)
SET @ReportMonth = (SELECT TOP 1 REPORT_MONTH
FROM TMP_ONTIME_SUMMARY);
SET @EmailTitle = (SELECT TOP 1 EMAIL_SUBJECT
FROM TMP_ONTIME_SUMMARY);
SET @MtdTitle = 'MTD: ' + RTRIM(@ReportMonth)
SET @DailyTitle = 'Daily: ' + RTRIM(@EmailDateRange)
SET @tableHTML =
N'<style type="text/css">
#outsideborder-table
{
align: left;
border-collapse: collapse;
border-top: 7px solid #000000;
border-bottom: 7px solid #000000;
border-left: 7px solid #000000;
border-right: 7px solid #000000;
}
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #000000;
}
</style>'+
--Outside Border and style
N'<table id="outsideborder-table" >'+
--Head Line style format
N'<style type="text/css">
table
{
border-collapse:collapse;
font-family:Arial;
font-size:small;
}
h2
{
color: #fe0202;
font-family:Arial
}
h3
{
color:#000000;
font-family:Arial
}
</style>'+
--Head Line On-Time Service % (Static)
N'<H2 align="center" >On-Time Service %</H2>' +
--Head Line Report Period (Static)
N'<H2 align="center" >Report Period</H2>' +
N'<table id="box-table" >
<tr>
<style type="text/css">
table th
{
font-size: 13px;
font-weight: normal;
background: #fe0202;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #fff;
}
</style>
<th></th>
<style type="text/css">
table th
{
font-size: 13px;
font-weight: normal;
background: #00f;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #fff;
}
</style>
<th colspan = "4">' + @DailyTitle + '</th>
<style type="text/css">
table th
{
font-size: 13px;
font-weight: normal;
background: #000000;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #fff;
}
</style>
<th colspan = "4">' + @MtdTitle + '</th>
</tr>
<tr>'+
N'<style type="text/css">
table th
{
font-size: 13px;
font-weight: normal;
background: #fe0202;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #fff;
}
</style>
<th>SDC Markets</th>
<style type="text/css">
table th
{
font-size: 13px;
font-weight: normal;
background: #00f;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #fff;
}
</style>
<th>All Up</th>
<th>SDC</th>
<th>PUD</th>
<th>Except Cnt</th>
<style type="text/css">
table th
{
font-size: 13px;
font-weight: normal;
background: #000000;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #fff;
}
</style>
<th>All Up</th>
<th>SDC</th>
<th>PUD</th>
<th>Except Cnt</th>
</tr>' +
CAST( (SELECT
"td/@align" = 'left', td=SDCLocation ,'',
"td/@align" = 'right', td=DAllUp ,'',
"td/@align" = 'right', td=DSDC ,'',
"td/@align" = 'right', td=DPUD ,'',
"td/@align" = 'center', td=DExcptCnt ,'',
"td/@align" = 'right', td=MAllUp ,'',
"td/@align" = 'right', td=MSDC ,'',
"td/@align" = 'right', td=MPUD ,'',
"td/@align" = 'center', td=MExcptCnt ,''
FROM (SELECT Top(100)MARKET_NM AS SDCLocation,
DAILY_ALL_PCT AS DAllUp,
DAILY_SDC_PCT AS DSDC,
DAILY_PUD_PCT AS DPUD,
DAILY_EXCEPTION_DAY_CNT AS DExcptCnt,
MONTH_ALL_PCT AS MAllUp,
MONTH_SDC_PCT AS MSDC,
MONTH_PUD_PCT AS MPUD,
MONTH_EXCEPTION_DAY_CNT AS MExcptCnt
FROM TMP_ONTIME_SUMMARY
ORDER BY SEQUENCE_NUM
) A FOR XML PATH('tr'), TYPE )
AS NVARCHAR(MAX)
) +
N'</box-table >'+
N'</outsideborder-table>'
--Email data
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @EmailProfile,
@recipients = @recipientsMain,
@copy_recipients = @recipientsCC,
@blind_copy_recipients = @recipientsBCC,
@subject = @EmailTitle,
@body = @tableHTML,
@body_format = 'HTML' ;