Any DBA or developer use Database mail Future need to send Email with Special Formate like if you need to send Query from your Database to the Customer but you want to put the result in Table What Will you Do leat’s learn how to send Email with tabel format but in the first you must configure database mail server in youe SQL Server so Read this Article about how to Configure Database mail server  also you can Check this link now i will do :

1- Create HR_EMP Table

2- Insert Data into HR_EMP Table

3- Define 2 variable @XML to send the Query with XML , @body to bulid the body of tabel with HTML


[EMP_ID]  int,

[EMP_Name]  Nvarchar(50),




SELECT 1,‘Mostafa Elmasry’,’18/05/2010′,’18/05/2012′


SELECT 2,‘Moahmed Osman’,’01/01/2011′,’01/01/2013′


SELECT 3,‘mahmoude Darwish’,’27/09/2010′,’27/09/2012′



SET @xml=CAST((SELECT[emp_id]AS‘td’,,[EMP_Name]AS‘td’,,[VISA_Start_Date]AS‘td’,,Visa_END_DateAS‘td’ FROM HR_EMPORDERBYVisa_END_DateDesc FOR XMLPATH(‘tr’),ELEMENTS)ASNVARCHAR(MAX))

SET @body=‘<html><body><H3>Visa Start Date and End Date to All Employees</H3>

<table border = 1>


<th> Employee Code </th> <th> Employee name </th> <th> Visa Start Date </th> <th> Visa End Date </th></tr>’

SET @body=@body+@xml+‘</table></body></html>’

—–Let’s Send Email Now

EXEC msdb.dbo.sp_send_dbmail

@profile_name =‘DBA’,— replace with your SQL Database Mail Profile

@body =@body ,


@recipients =‘’,— replace with your email address

@subject =‘Employee Visa information’;

Send mail with table format


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.