Hi Paandeyism,
Thanks for your support. I found a solution instead of sending the results as attachment, I'm sending them as html email. Below is my code.
CREATE TABLE #Temp
(
[EmpId] [int],
[Name] [varchar](200),
[Department] [varchar](200),
[DaysCompleted] [varchar](128)
)
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10),
@count int
INSERT INTO #Temp
select EMPID,NAME,deptname, DATEDIFF(D,JOINDATE,GETDATE()) as dayscompleted from Employee where DATEDIFF(D,JOINDATE,GETDATE())=85
if @@ROWCOUNT!=0
Begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [EmpId] AS 'td','',[Name] AS 'td','', [department] AS 'td','', DaysCompleted AS 'td'
FROM #Temp
ORDER BY DaysCompleted desc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body>Dear HR Department,</br></br>The Below Employees are nearing prohibition period.</br></br>
<table border = 1>
<tr>
<th> Emp Id </th> <th> Name </th> <th> Department </th><th>Days Completed </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC sp_send_email
'sender@email.com',
'',
'receiver@emial.com',
'Prohibition period completing soon',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
end
DROP TABLE #Temp
