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> </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
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
Difference Between Datetime Converts in Msexcel and SQL Server
SQL Server Split Comma Separated Values into Columns
Returning the Value of Identity Column After Insertion in Oracle
Case Statement with Different Data Type
Ora-00600 When Running Alter Command
Convert Delimited String to Rows in Oracle
SQL Server Copying Tables from One Database to Another
How to Have Alphanumeric Sequence Generator in SQL
Mysql: Union of a Left Join with a Right Join
Getting Unavailable Dates for Renting a Product That Has Stocks
Why Do SQL Id Sequences Go Out of Sync (Specifically Using Postgres)
How to Create Foreign Keys Across Databases
How to Select Records That Don't Exist in SQL Server
How to Write a Query to Extract Individual Changes from Snapshots of Data
Is This Normalization Correct? (Two Many-To-Manys Connected by a Many-To-One)
What Is the Correct Syntax for Using Database.Executesqlcommand with Parameters