HTML Format in Sp_Send_Dbmail

HTML Format in sp_send_dbmail

You have to specify @body_format = 'HTML' too. The default is TEXT.

See sp_send_dbmail on MSDN: there are HTML examples too

sp_send_dbmail sending HTML instead of HTML formatted email

In your example, the @body argument is already HTML-encoded, so that's why you're getting HTML in the e-mail. Your use of FOR XML PATH is the culprit, as it is doing this encoding you don't need. Find a better approach to building @ExecSQL - I think you're overcomplicating it.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'GrillExceptions',
@recipients = '*******@******.com',
@subject = 'NOTIFICATION: You have exceeded the alloted meal amount',
@body = '<HTML> <HEAD> <STYLE> TD {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} </STYLE> </HEAD> <BODY> <H3>James Shinevar,</H3> <br/> <p> On the following dates you spent the indicated amounts:</p> <br/> <table cellpadding=0 cellspacing=0 border=0> <TR> <TD>Date</TD> <TD></TD> <TD>Amount</TD> </TR><TR><TD>2016-11-22</TD><TD>&#x20;</TD><TD>8.50</TD></TR></table> <p>You will see $0.50 deducted from your paycheck. </p> </BODY> </HTML>',
@body_format = 'HTML';

Format msdb.dbo.sp_send_dbmail

You need to specify the Body format as HTML while calling the sp_send_dbmail

EXEC dbo.sp_send_dbmail @profile_name = 'MyMailProfile',
@recipients = 'receiver@gmail.com',
@copy_recipients = 'cc@gmail.com',
@body = 'HTML Body Content',
@body_format = 'HTML',
@subject = 'My Mail Subject';

T-SQL; How to add sentences before and after a table in an html format using sp_send_dbmail

Use this instead...

DECLARE @header NVARCHAR(MAX)
DECLARE @footer NVARCHAR(MAX)
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @header = 'Hi, </br> Here is a list. </br> The details are below </br>'
SET @footer = '</br> Thank you for looking. </br> Kind regards </br> From us'

SET @tableHTML = @header +
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' + @footer

EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;

This is my output

Sample Image

How to embed image in html and send html as email by msdb.dbo.sp_send_dbmail?

I think I got the answer:

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'myemail@someemail.com',
@subject = 'test',
@file_attachments = 'C:\MyFolder\Test\Google.gif;C:\MyFolder\Test\Yahoo.gif',
@body=N'<p>Image Test</p><img src="Google.gif" /><p>See image there?</p>
<img src="Yaoo.gif" /><p>Yahoo!</p>',
@body_format = 'HTML';

basically, add the image as attachment, and the src attribute contains just the image file name, no any path is needed. If more than one image files are needed, just use ";" to separate them.

I send email to my outlook email and it works. Try it to my yahoo email....

sp_send_dbmail - formatting row in table as red for an alert

You can not do it directly.A little "Handcrafted" HTML is required. Here is an approach that can be used.

Select good and bad records in separate CTE and append the "td" tags. for bad ones append the Style information as-well.

Then append "tr" tags and combine (UNION) data rows and concatenate them using for xmlpath.

I have removed order by columns for simplicity but you can select them in the CTE and order the results later.

Note: I have tested the output HTML and it works but I am not HTML guy so don't mind if there is any mistake in HTML tags.feel free to correct it.

DECLARE @tableHTML  NVARCHAR(MAX) 
,@Data NVARCHAR (MAX)=''
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>'

;WITH CTE_Good AS
(
SELECT HTMLRowData= N'<td>'+STR(wo.WorkOrderID)+N'</td>'
+N'<td>'+STR(p.ProductID)+N'</td>'
+N'<td>'+p.Name+N'</td>'
+N'<td>'+STR(wo.OrderQty)+N'</td>'
+N'<td>'+CONVERT(VARCHAR(10),wo.DueDate,101)+N'</td>'
+N'<td>'+STR((p.ListPrice - p.StandardCost) * wo.OrderQty)+N'</td>'
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
--AND Rating = 'Good'
)
,CTE_Bad AS
(
SELECT HTMLRowData= N'<td><p style="color:red">'+STR(wo.WorkOrderID)+N'</p></td>'
+N'<td><p style="color:red">'+STR(p.ProductID)+N'</p></td>'
+N'<td><p style="color:red">'+p.Name+N'</p></td>'
+N'<td><p style="color:red">'+STR(wo.OrderQty)+N'</p></td>'
+N'<td><p style="color:red">'+CONVERT(VARCHAR(10),wo.DueDate,101)+N'</p></td>'
+N'<td><p style="color:red">'+STR((p.ListPrice - p.StandardCost) * wo.OrderQty)+N'</p></td>'
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
--AND Rating = 'Bad'

)

SELECT @Data=(SELECT HTMLRows
FROM (
SELECT N'<tr>'+HTMLRowData+N'</tr>' AS HTMLRows FROM CTE_Good
UNION SELECT N'<tr>'+HTMLRowData+N'</tr>' AS HTMLRows FROM CTE_Bad
) mi
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)')

SET @tableHTML=@tableHTML+@Data+N'</table>'

--SELECT @tableHTML

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;

How to use HTML Format in sp_send_dbmail in asp.net

You need to put html tags in your string body, example:

string body = "<h1>Hello" + fullName + Environment.NewLine + "</h1>";
body += "<p>Your registration has been approved. Here is your user name and password " + Environment.NewLine + "</p>";
body += "<h3>User Name: " + usrname + Environment.NewLine+"</h3>";
body += "<h3>Password: " + pass + Environment.NewLine+"</h3>";
body += "<h3>Start Date: " + startDate + Environment.NewLine+"</h3>";
body += "<h3>End Date: " + EndDate + Environment.NewLine+"</h3>";

body += "<p>Thanks</p>";

then, in the parameters, you need to add:

exec_cmd.Parameters.AddWithValue("body_format", "HTML");

The default value is TEXT

sp_send_dbmail Incorrect syntax near ''

It looks like you want @tableHTML to be the body of the email, but you're passing it in as @query, which has to contain valid SQL, hence the error.

Try using @body instead:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'db_mail_account',
@recipients = 'example@example.com',
@subject = 'Daily Project Tracking Report',
@body = @tableHTML,
@body_format = 'HTML';


Related Topics



Leave a reply



Submit