Send Image in Email Body via Database Mail

Certain Business requirements may involve sending database mail that incorporates company's logo, and other formatting things like thick border, particular font etc...Below article describes how this can be done using Database Mail.

If this is your first time sending HTML formatted email, please refer to my earlier article that explains in detail about doing so and can be found here.

We will use adventureWorks Database for demonstration...

DECLARE @yourHTMLEmail NVARCHAR(MAX) ;

 

SET @yourHTMLEmail =

N'<table style="border:solid 5px black" Width="1" align="center">'+

N'<tr><td><H3 align="center" >Product/Order Detail Report</H1>' +

N'<style type="text/css">table {border-collapse:collapse;font-family:Arial;font-size:small;}'+

N' table td,th{border:solid 1px Gray;padding:10px;text-align:left;color:Gray;} '+

N' table th{background-color:Gray;color:White;font-weight:bold;} h3 {color:red; font-family:Arial}</style>'+

N'<table border="1" Width="1" align="center"><tr><td align="center"><img src="http://www.devtechie.com/image.axd?picture=2011%2f6%2fDT.png" alt="" /></td>'+

N'</tr></table>'+

N'<table border="5" align="center">' +

N'<tr>'+

N'<th>Name</th><th>Title</th><th>EmailAddress</th>'+

 

CAST( (select

td=Name ,'',

td=Title ,'',

td=EmailAddress ,''

 

from (

select top(10)isnull(LastName+','+FirstName+' '+ MiddleName,'No-Name') as Name,Title,EmailAddress

from SalesLT.Customer

) A FOR XML PATH('tr'), TYPE )

AS NVARCHAR(MAX)

) +

N'</table>' +

N'</td>'+

N'</table>';

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Profile1',

@recipients='abc@devtechie.com',--this will be email adress of your receipient

@subject = 'Product Detail Report',

@body = @yourHTMLEmail,

@body_format = 'HTML'

Below is how it will be displayed