Introduction
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 http://wp.me/p1Oidq-9A 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
CREATE TABLEHR_EMP(
[EMP_ID] int,
[EMP_Name] Nvarchar(50),
[VISA_Start_Date]Nvarchar(50),
Visa_END_DateNvarchar(50))
INSERT INTO HR_EMP
SELECT 1,‘Mostafa Elmasry’,’18/05/2010′,’18/05/2012′
UNION ALL
SELECT 2,‘Moahmed Osman’,’01/01/2011′,’01/01/2013′
UNION ALL
SELECT 3,‘mahmoude Darwish’,’27/09/2010′,’27/09/2012′
DECLARE @xmlNVARCHAR(MAX)
DECLARE @bodyNVARCHAR(MAX)
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>
<tr>
<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 ,
@body_format=‘HTML’,
@recipients =‘Mostafa@Safeerp.com’,— replace with your email address
@subject =‘Employee Visa information’;
DB Cloud TECH 