annyvz Posted September 9, 2014 Share Posted September 9, 2014 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 Link to comment Share on other sites More sharing options...
annyvz Posted September 9, 2014 Author Share Posted September 9, 2014 I have found that certain mail browsers does not support CSS, here is a comprehensive list that shows what is supported where: https://www.campaignmonitor.com/css/ If anyone knows a way around this, please let me know, Thanks Link to comment Share on other sites More sharing options...
justsomeguy Posted September 9, 2014 Share Posted September 9, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now