Custom Format for DB Mail

There can be several requirements that can come through regarding HTML email. One of the recent requirements from one of our user required use of different font style, borders, slogan, images etc…In this article we will solve the problem of our user.

We will be using our AdventureWorks Database for showing data from tables.

What we want to achieve is something

 

DECLARE @yourHTMLEmail NVARCHAR(MAX) ;

DECLARE @slogan NVARCHAR(90) ;

DECLARE @DonationAmt float ;

set @slogan ='Make a donation';

set @DonationAmt=90;

 

SET @yourHTMLEmail =

 

N'<table style="border:solid 2px black">

<tr><td align="left"><img src="http://www.devtechie.com/image.axd?picture=2011%2f6%2fDT.png" alt="" /></td>

<td align="center" style="font-color:red;">'+@slogan+'</td></tr>

<tr><td colspan="2">Hi ['+(select top(1)isnull(LastName+','+FirstName+' '+ MiddleName,'No-Name')

as Name from SalesLT.Customer)+'] :</td></tr>

<tr><td colspan="2"><span style="border-bottom:solid 2px

black;">Thank you for your support</span></td></tr>

<tr><td colspan="2" align="center" style="font-size:Larger">Todays donation amount is:

</td></tr>

<tr><td colspan="2">Your Donation today will help us find a cure

</td></tr>

<tr><td colspan="2">Your recurring 6 month donation is:'+cast(@DonationAmt as nvarchar(20))+'

</td></tr>

</table>';

 

 

EXEC msdb.dbo.sp_send_dbmail

 

@profile_name='Profile1',

 

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

 

@subject = 'Make a donation',

 

@body = @yourHTMLEmail,

 

@body_format = 'HTML'

 

Result will be displayed as below

 

 

 

 

 

Tags: , , ,

SQL Server