Jump to content

SQL server Management Studio losing CSS styles when executing mail


annyvz

Recommended Posts

Hi

 

I have coded an e mailer to go out with SQL server management studio, however when I execute the file it looses all the CSS styling.

 

I have tried putting the styles inline, still no luck.

 

Is there any way to fix/bypass this? See my previews attached.

 

Here is my code:

 

--USE [issueTrackerDPS]--GO--/****** Object: StoredProcedure [dbo].[issueTracker_Issue_SendWeekly_ToBeClosed] Script Date: 09/08/2014 11:40:06 ******/--SET ANSI_NULLS ON--GO--SET QUOTED_IDENTIFIER ON--GO--ALTER PROCEDURE [dbo].[issueTracker_Issue_SendWeekly_ToBeClosed]--ASDECLARE @IssueId int, @IssueTitle varchar(100), @ProjectName varchar(100), @LastUpdated date, @FirstReminder date, @CloseDate date, @Result varchar(50), @ReportedBy varchar(100)DECLARE @TempTBL table (IssueId int, IssueTitle varchar(200), ProjectName varchar(100), LastUpdated date, FirstReminder date, CloseDate date, Result varchar(50), ReportedBy varchar(100))DECLARE @OuterCNT int, @InnerCNT intDECLARE @EmailString varchar(500)DECLARE @MailTo varchar(100)DECLARE @MailSubject varchar(50) = 'Issue "To Be Closed" Notification'DECLARE @MailBodyFinal nvarchar(max)DECLARE @MailBody1 nvarchar(max)DECLARE @MailBody2 nvarchar(max)DECLARE C1 CURSOR FOR SELECT DISTINCT b.CustomFieldValue + ';' + d.Email + ';' AS Email FROM vProjectReport a JOIN IssueTracker_ProjectCustomFieldValues b ON a.issueid = b.issueid JOIN IssueTracker_Issues c ON a.IssueId = c.IssueId JOIN IssueTracker_Users d ON c.IssueAssignedId = d.UserId WHERE a.MilestoneName LIKE 'To Be Closed' AND a.StatusName = 'Waiting on Response' AND b.CustomFieldId in (SELECT CustomFieldId FROM dbo.IssueTracker_ProjectCustomFields WHERE CustomFieldName = 'Reported By')OPEN C1FETCH NEXT FROM C1 INTO @EmailStringWHILE @@FETCH_STATUS = 0BEGINSET @OuterCNT = 0SET @InnerCNT = (SELECT LEN(@EmailString) - LEN(REPLACE(@EmailString,';',''))) WHILE @OuterCNT < @InnerCNT BEGIN SET @ReportedBy = (SELECT left((substring(@EmailString,1,(charindex(';',@EmailString)-1))),(len(@EmailString)))) SET @EmailString = (SELECT right(@EmailString,(Len(@EmailString) - (charindex(';',@EmailString))))) SET @OuterCNT = @OuterCNT + 1 IF (SELECT ReportedBy FROM @TempTBL WHERE ReportedBy = @ReportedBy) is null and @ReportedBy != '' INSERT INTO @TempTBL (ReportedBy) SELECT @ReportedBy ENDFETCH NEXT FROM C1 INTO @EmailStringENDCLOSE C1DEALLOCATE C1DECLARE C2 CURSOR FOR SELECT DISTINCT ReportedBy FROM @TempTBLOPEN C2FETCH NEXT FROM C2 INTO @ReportedByWHILE @@FETCH_STATUS = 0BEGINDELETE FROM @TempTBL WHERE ReportedBy = @ReportedByINSERT INTO @TempTBL (IssueId, IssueTitle, ProjectName, Result, ReportedBy) SELECT a.issueid, a.IssueTitle, a.ProjectName, a.Result, @ReportedBy FROM vProjectReport a JOIN IssueTracker_ProjectCustomFieldValues b ON a.issueid = b.issueid JOIN IssueTracker_Issues c ON a.IssueId = c.IssueId JOIN IssueTracker_Users d ON c.IssueAssignedId = d.UserId WHERE a.MilestoneName LIKE 'To Be Closed' AND a.StatusName = 'Waiting on Response' AND b.CustomFieldId in (SELECT CustomFieldId FROM dbo.IssueTracker_ProjectCustomFields WHERE CustomFieldName = 'Reported By') and (SELECT (b.CustomFieldValue + ';' + d.Email)) like '%' + @ReportedBy + '%'FETCH NEXT FROM C2 INTO @ReportedByENDCLOSE C2DEALLOCATE C2DECLARE C3 CURSOR FOR SELECT DISTINCT IssueId FROM @TempTBLOPEN C3FETCH NEXT FROM C3 INTO @IssueIdWHILE @@FETCH_STATUS = 0BEGINSET @LastUpdated = (SELECT MAX(DateCreated) FROM IssueTracker_IssueHistory WHERE IssueId = @IssueId)SET @FirstReminder = (SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @LastUpdated), 7))SET @CloseDate = (SELECT DATEADD(WEEK, DATEDIFF(WEEK, -14, @FirstReminder), 7))UPDATE @TempTBL SET LastUpdated = @LastUpdated, FirstReminder = @FirstReminder, CloseDate = @CloseDate WHERE IssueId = @IssueIdFETCH NEXT FROM C3 INTO @IssueIdENDCLOSE C3DEALLOCATE C3DECLARE C4 CURSOR FOR SELECT DISTINCT ReportedBy FROM @TempTBL WHERE ReportedBy like '%wy%'OPEN C4FETCH NEXT FROM C4 INTO @MailToWHILE @@FETCH_STATUS = 0BEGINSET @MailSubject = 'Issue "To Be Closed" Notification'SET @MailBody1 = ''SET @MailBody2 = ''SET @MailBody1 = '<html><head><title>DPS_IST# (CONVERT(Char(5),@IssueID)) _Full_Issue_Log</title><meta name="SKYPE_TOOLBAR" content="SKYPE_TOOLBAR_PARSER_COMPATIBLE" /><style type="text/css">.a:link {color: #CF020E}.a:visited {color: #CF020E}.a:hover {color: #CF020E}.a:active {color: #CF020E}.a.white:link {color: #ffffff; background: #none; text-decoration: none;font-weight:none;}.a.white:active {color: #ffffff; background: #none; text-decoration: none;font-weight:none;}.a.white:visited {color: #ffffff; background: #none; text-decoration: none;font-weight:none;}.a.white:hover {color: #ffffff; background: #none;text-decoration: none;font-weight:none;}.responsive-image {height:auto;width:100%;}.skype_php_container {display:none !important}.skype_php_print_container {display:inline !important}.image {position: center;}.h2 {color: #ffffff;font-family: Arial,Helvetica,sans-serif;font-size: 17px;font-weight: bold;height: 5px;left: -15px;line-height: 120%;position: relative;text-decoration: none;top: -109px;width: 100%; }.h2.pos_left {position: relative;left: -10px;}.h2.pos_right {position: relative;left: 10px;}.top {border-bottom: 2px solid #000000;border-style: solid;border-width: 2px;border-radius: 10px;padding:10px;}.tab1 {border-bottom: 2px solid #000000;border-style: solid;border-width: 2px;height: 171px;border-radius: 10px;border-color:#003a63;}.head {background-color:#0082c8;width: 70%;color: ffffff;text-align: center;line-height: 30px;border-style: solid;border-width: 2px;border-radius: 10px;border-color: #003a63;}.datagrid table { border-collapse: collapse; text-align: left; width: 100%; }.datagrid {font: normal 12px/150% Arial, Helvetica, sans-serif; background: #fff; overflow: hidden; border: 1px solid #003a63; -webkit-border-radius: 10px;-moz-border-radius: 10px; border-radius: 10px; }.datagrid table td, .datagrid table th { padding: 3px 10px; }.datagrid table thead th {background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #0082C8), color-stop(1, #009ddc) );background:-moz-linear-gradient( center top, #0082C8 5%, #009ddc 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr=''#0082C8'', endColorstr=''#009ddc'');background-color:#0082C8; color:#FFFFFF; font-size: 15px;font-weight: bold; border-left: 1px solid #0070A8;}.datagrid table thead th:first-child { border: none; }.datagrid table tbody td { color: #00496B; font-size: 12px;font-weight: normal;border-left: solid 1px #003a63;}.datagrid table tbody .alt td { background: #E1EEF4; color: #00496B; }.datagrid table tbody td:first-child { border-left: solid 1px #003a63; border-right: solid 1px #003a63; }.datagrid table tbody tr:last-child td { border-bottom: none; }.datagrid table tfoot td div { border-top: 1px solid #006699;background: #E1EEF4;}.datagrid table tfoot td { padding: 0; font-size: 12px }.datagrid table tfoot td div{ padding: 2px; }.datagrid table tfoot td ul {margin: 0; padding:0; list-style: none; text-align: right; }.datagrid table tfoot li {display: inline; }.datagrid table tfoot li a {text-decoration: none; display: inline-block; padding: 2px 8px; margin: 1px;color: #FFFFFF;border: 1px solid #006699;-webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #006699), color-stop(1, #00557F) );background:-moz-linear-gradient( center top, #006699 5%, #00557F 100% );filter:progid:DXImageTransform.Microsoft.gradient(startColorstr=''#006699'', endColorstr=''#00557F'');background-color:#006699; }.datagrid table tfoot ul.active, .datagrid table tfoot ul a:hover { text-decoration: none;border-color: #006699; color: #FFFFFF; background: none; background-color:#00557F;}div.dhtmlx_window_active, div.dhx_modal_cover_dv { position: fixed !important; }</style></head><body> <div> <img src="http://tickettracker.ctrack.co.za:8084/images/IssueTrackerDPS.png" border="0" class="responsive-image" alt="Null"> <div class="h2" align="right"> <h2> 24h Support: +27 (79) 874 0113<br>E-mail: <a href="mailto:ctrack_support@digicore.co.za" target="_blank" class="white">ctrack_support@digicore.co.za</a><br>Office: 27 (12) 450 2222 <br>Mobile: 27 (82) 410 3240 </h2> </div> </div><div style="margin-top:-53px;"><div class="datagrid"><table> <thead style="text-align:;"> <tr> <td colspan="4" style="text-align:center;background-color:#003a63; color:ffffff;font-size:18px; "> Ticket Notification </td> </tr> <tr> <td colspan="4" style="padding:10px;"> <p style="padding-left:10px;color:#0082c8;"><strong>Dear Distributor and Support Team</strong></p> <p style="text-align:justify;padding-left:10px;margin-right:18px;line-height:15px;color:#003a63;">Please note that all tickets fixed in the latest release of Ctrack Maxx & Ctrack Online will be marked as "To Be Closed", to allow all affected parties time to verify that tickets has been resolved as expected.</br><br> Should you still experience problems regarding these ticket, update IssueTracker with further detailed explanations of the persisting problems.<br><br> If no update is logged within 3 weeks, it will be assumed that the Ticket was successfully resolved and be automatically closed by the system.<br><br></p> </td> </tr> </thead> <thead> <tr> <th style="text-align:center;width:40%">Tickets - TO BE CLOSED</th> <th style="text-align:center;width:20%">Project</th> <th style="text-align:center;width:20%">Last Updated</th> <th style="text-align:center;width:20%">Auto Close</th> </tr> </thead> <tfoot> <tr> <td colspan="4"> </tr> </tfoot>'set @MailBody2 ='<tbody>'+ CAST ( ( SELECT td = [issueTitle], '', td = [ProjectName], '', td = [LastUpdated], '', td = [CloseDate], '' FROM @TempTBL WHERE IssueId in (SELECT IssueId FROM @TempTBL WHERE ReportedBy = @MailTo) AND ReportedBy = @MailTo ORDER BY IssueId FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +'</tbody></table></div></div>'SET @MailBodyFinal = @MailBody1 + @MailBody2EXEC msdb.dbo.sp_send_dbmail @profile_name = 'IssueTracker Mail', @recipients = 'Annalin@Ctrack.co.za',--@MailTo, @subject = @MailTo,--@MailSubject, @body = @MailBodyFinal, @body_format = 'HTML'FETCH NEXT FROM C4 INTO @MailToENDCLOSE C4DEALLOCATE C4

post-175273-0-85962300-1410251000_thumb.jpg

post-175273-0-85251900-1410251060_thumb.jpg

Link to comment
Share on other sites

The first thing I would do would be to validate your CSS, at least a couple things in it aren't valid. Other than that though, you can't force mail clients to do anything, they're going to do what they do regardless of what you want them to do. It's like with web browsers, you can't force old versions of IE to natively support new CSS styles either. When you send complex emails like that you should send a multi-part message that contains both a text and HTML version, because you might also be sending to a mail client that doesn't show HTML email at all. They would see your HTML code instead of a message.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...