Posts Tagged ‘SQL Server – Database Mail – Easiest way to send emails in HTML format’

I have been using Database email for a long time. This is very nice features introduced in SQL Server 2005. But when it comes to HTML formatting, you need to know a little bit HTML to format it and it takes a quite a while to format it. In this article, I will share a tip that can make your life easy and you can format even a complex HTML formatted email in few minutes.

Lets process it step by step.

Step 1 :
Mostly we receive the email format in word documents. First of all, you need to open the word document in MS word.
Sample attached Test Document 1.1.
SQL email1.1

Step 2 :
Then Go to File menu>>Save as and save it as html document.
SQL email1.2

Step 3 :
Open the saved html document, right click on it and go to view source.
SQL email1.3

Step 4 :
It will open a new window. Now you need to search for the tags namely Body and /Body and whatever text lies between both tags, just copy and paste it in the SQL query window.
SQL email1.4

Step 5 :
Now, you need to replace one single quote (‘) to two single quotes (”) in SQL query window and add single quote (‘) in the beginning and end of the text. Rest of the format will remain same.

Use msdb
EXEC sp_send_dbmail @profile_name='My profile',
@subject='Test Message with Formatted HTML',
@body_format = 'HTML' ,
<div class="WordSection1"><s>This is a TEST email</s>

<i><span style="text-decoration: underline;">This
is a TEST email</span></i>

This is a TEST email

This is a TEST
<table class="MsoTableLightListAccent1" border="1" cellspacing="0" cellpadding="0">
<td valign="top" width="97"><span class="SpellE"><b>S.No</b></span><b></b></td>
<td valign="top" width="375"><b>SQL Server Versions</b></td>
<td valign="top" width="97"><b>1</b></td>
<td valign="top" width="375">SQL Server 2005</td>
<td valign="top" width="97"><b>2</b></td>
<td valign="top" width="375">SQL Server 2008</td>
<td valign="top" width="97"><b>3</b></td>
<td valign="top" width="375">SQL Server 2012</td>

Step 6 :
Now, just execute it and it will send a nice formatted HTML email.

Read Full Post »