Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Using CSS to control formatting in dbmail
I have written a query and currently have a job that runs and sends an email using sp_send_dbmail in a formatted table if certain conditions are met. I use style sheets to control the format and this works fine for all columns returned using the style for td but I have a need to control the format of individual columns of data only, i.e. control the text alignment, data type, color, etc. of the value column data independently of the column header. I would like to format the individual columns below to a currency if possible and right justify them. Even if I can not format to currency, if i could format to a number format with a comma separated value (XX,XXX) I can concatenate the $ sign. I have attached a snippet of the table output as well as the code.
FYI- there is another table below the summary table shown here but I did not send it. Much similar in nature, I just need guidance on getting the style sheet embedded to work.
I found during my search on the web a query written almost identical to mine with the same issue and the response was as follows:
Note: create a css classes for each td above.
> CAST
> (( SELECT
> [td/@class]='anyclassname1
> [td/@class]='anyclassname2
I have tried this and substituted [td/@class]='text-align: right', and can not get it to work.
Any help would be greatly appreciated.
> DECLARE @bodyMsg nvarchar(max)
> DECLARE @subject nvarchar(max)
> DECLARE @tableSummary nvarchar(max)
>
> SET @subject = 'Matter Alert for 189274'
>
>
>
> SET @tableSummary =
> N'<style type="text/css">
> #box-table
> {
> font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
> font-size: 12px;
> text-align: left;
> border-collapse: collapse;
> border-top: 7px solid #9baff1;
> border-bottom: 7px solid #9baff1;
> }
> #box-table th
> {
> padding: 5px;
> font-size: 13px;
> font-weight: normal;
> background: #b9c9fe;
> border-right: 2px solid #9baff1;
> border-left: 2px solid #9baff1;
> border-bottom: 2px solid #9baff1;
> color: #039;
> }
> #box-table td
> {
> padding: 5px;
> border-right: 1px solid #aabcfe;
> border-left: 1px solid #aabcfe;
> border-bottom: 1px solid #aabcfe;
> color: #669;
> }
> tr:nth-child(odd) { background-color:#eee; }
> tr:nth-child(even) { background-color:#fff; }
> </style>'+
> N'<H3><font color="Red">Matter Management Alert</H3>' +
> N'<table id="box-table" >' +
> N'<tr><font color="Green">
> <th>Base Rate Value (B)</th>
> <th>Value at A Rate</th>
> <th>Value at C Rate</th>
> <th>Value at $125/Hr</th>
> <th>Threshold</th>
> </tr>' +
> CAST ( (
>
> SELECT
> td = cast(sum(itdr.[Base Amt]) as numeric(25,2)),'',
> td = cast(sum(itdr.[A Rate Value]) as numeric(25,2)),'',
> td = cast(sum(itdr.[C Rate Value]) as numeric(25,2)),'',
> td = cast( sum(
> CASE WHEN itdr.[Employee Code] = 'TLW'
> THEN (itdr.[Base Hrs] * 125)
> ELSE (itdr.[Base Amt])
> END
> ) as numeric(25,2)),'',
> td = '5000'
> FROM Intl_Tax_Data_Run itdr
> where itdr.[Matter Code] = '189274'
> FOR XML PATH('tr'), TYPE
> ) AS NVARCHAR(MAX) ) +
>
> N'</table>'
>
> declare @nbody varchar(max)
>
>
> set @nbody = @tableSummary
>
>
>
>
> EXEC msdb.dbo.sp_send_dbmail
> @profile_name = 'ProfileName',
> @recipients='s_smith@sampl
> @from_address = 's_smith@sample.com',
> @subject = @subject,
> @body = @nbody,
> @body_format = 'HTML' ;
Table.JPG
FYI- there is another table below the summary table shown here but I did not send it. Much similar in nature, I just need guidance on getting the style sheet embedded to work.
I found during my search on the web a query written almost identical to mine with the same issue and the response was as follows:
Note: create a css classes for each td above.
> CAST
> (( SELECT
> [td/@class]='anyclassname1
> [td/@class]='anyclassname2
I have tried this and substituted [td/@class]='text-align: right', and can not get it to work.
Any help would be greatly appreciated.
> DECLARE @bodyMsg nvarchar(max)
> DECLARE @subject nvarchar(max)
> DECLARE @tableSummary nvarchar(max)
>
> SET @subject = 'Matter Alert for 189274'
>
>
>
> SET @tableSummary =
> N'<style type="text/css">
> #box-table
> {
> font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
> font-size: 12px;
> text-align: left;
> border-collapse: collapse;
> border-top: 7px solid #9baff1;
> border-bottom: 7px solid #9baff1;
> }
> #box-table th
> {
> padding: 5px;
> font-size: 13px;
> font-weight: normal;
> background: #b9c9fe;
> border-right: 2px solid #9baff1;
> border-left: 2px solid #9baff1;
> border-bottom: 2px solid #9baff1;
> color: #039;
> }
> #box-table td
> {
> padding: 5px;
> border-right: 1px solid #aabcfe;
> border-left: 1px solid #aabcfe;
> border-bottom: 1px solid #aabcfe;
> color: #669;
> }
> tr:nth-child(odd) { background-color:#eee; }
> tr:nth-child(even) { background-color:#fff; }
> </style>'+
> N'<H3><font color="Red">Matter Management Alert</H3>' +
> N'<table id="box-table" >' +
> N'<tr><font color="Green">
> <th>Base Rate Value (B)</th>
> <th>Value at A Rate</th>
> <th>Value at C Rate</th>
> <th>Value at $125/Hr</th>
> <th>Threshold</th>
> </tr>' +
> CAST ( (
>
> SELECT
> td = cast(sum(itdr.[Base Amt]) as numeric(25,2)),'',
> td = cast(sum(itdr.[A Rate Value]) as numeric(25,2)),'',
> td = cast(sum(itdr.[C Rate Value]) as numeric(25,2)),'',
> td = cast( sum(
> CASE WHEN itdr.[Employee Code] = 'TLW'
> THEN (itdr.[Base Hrs] * 125)
> ELSE (itdr.[Base Amt])
> END
> ) as numeric(25,2)),'',
> td = '5000'
> FROM Intl_Tax_Data_Run itdr
> where itdr.[Matter Code] = '189274'
> FOR XML PATH('tr'), TYPE
> ) AS NVARCHAR(MAX) ) +
>
> N'</table>'
>
> declare @nbody varchar(max)
>
>
> set @nbody = @tableSummary
>
>
>
>
> EXEC msdb.dbo.sp_send_dbmail
> @profile_name = 'ProfileName',
> @recipients='s_smith@sampl
> @from_address = 's_smith@sample.com',
> @subject = @subject,
> @body = @nbody,
> @body_format = 'HTML' ;
Table.JPG
Who is Participating?
> [td/@class]='anyclassname1
> [td/@class]='anyclassname2
the classes are: anyclassname1 & anyclassname2
My CSS knowledge is rudimentary at best, but once you nominate a class, then the class itself should contain the wanted alignment
> [td/@class]='anyclassname2
the classes are: anyclassname1 & anyclassname2
My CSS knowledge is rudimentary at best, but once you nominate a class, then the class itself should contain the wanted alignment
Thanks for your help. I am in the same boat with you on CSS knowledge as well. Could you advise as to where I would nominate the class in the query to refer back to it? I am truly at a complete loss here on how to do this.
Thanks!
Thanks!
> #box-table
box-table is a class name. Now look back at your question. You will see box-table toward the top of your code there.
So you already have some class names
In html the table tag should look something like
<table class="box-table">
Then elements auch as <td> are controlled by the box-table td already defind.
Can you capture the generated @tableSummary result and provide it?
In a code block please.
box-table is a class name. Now look back at your question. You will see box-table toward the top of your code there.
So you already have some class names
In html the table tag should look something like
<table class="box-table">
Then elements auch as <td> are controlled by the box-table td already defind.
Can you capture the generated @tableSummary result and provide it?
In a code block please.
Think I have found a blog page that will clarify what you are attempting:
Format DBMail with HTML and CSS
extract:
DECLARE @Style NVARCHAR(MAX)= '';
...
SET @Style += ...
+ N'.tg .tg-9ajh{font-weight:bold;backgrou
+ N'.tg .tg-hgcj{font-weight:bold;text-ali
+ N'</style>';
.....
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="2">Order Information</th>'
+ N'<th class="tg-hgcj" colspan="2">Summary</th>'
+ N'</tr>'
/*
Define Column Sub-Headers
*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Order Date</td>'
...
Format DBMail with HTML and CSS
extract:
DECLARE @Style NVARCHAR(MAX)= '';
...
SET @Style += ...
+ N'.tg .tg-9ajh{font-weight:bold;backgrou
+ N'.tg .tg-hgcj{font-weight:bold;text-ali
+ N'</style>';
.....
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="2">Order Information</th>'
+ N'<th class="tg-hgcj" colspan="2">Summary</th>'
+ N'</tr>'
/*
Define Column Sub-Headers
*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Order Date</td>'
...
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
In the latter part of the query which is not written above, I did have to do some tweaking to the query. It has both varchar and numeric columns in the td. I had to redefine the classes of my table. See below:
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @Style nvarchar(max)= '';
DECLARE @tableHTML nvarchar(max)= '';
SET @subject = 'Matter Alert for 101853'
SET @Style +=
+ N'<style type="text/css">' +
N'.tg
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: left;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
' + N'.tg td
{
padding: 5px;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
' + N'.tg th
{
padding: 5px;
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
' + N' tr:nth-child(odd) { background-color:#eee; }
' + N' tr:nth-child(even) { background-color:#fff; }
' + N'<tr><font color="Green">
' + N'</style>'
;
SET @tableHTML += @Style + @tableHTML +
N'<table class="tg">' --DEFINE TABLE
/*
Define Column Headers and Column Span for each Header Column
*/
+ N'<tr>'
/*
Define each th
*/
+ N'<th>Employee Name</th>'
+ N'<th>Base Rate Value</th>'
+
/*
Define data for table and cast to xml
*/
CAST ( (
SELECT
'left' as 'td/@align', td = itdr.[Employee Name],'',
'right' as 'td/@align', td = '$' + PARSENAME(CONVERT(VARCHAR,
FROM Intl_Tax_Data_Run itdr
where itdr.[Matter Code] = '189274'
group by itdr.[Employee Name]FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
declare @nbody varchar(max)
set @nbody = @tableHTML
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients='s_smith@sampl
@from_address = 's_smith@sample.com',
@subject = @subject,
@body = @nbody,
@body_format = 'HTML' ;