Hi.
I wonder if there is a remedy in sending out an email , if the record is blank. Then the email that is being sent is also blank. Seems like it does not complete the html code when a record is NULL
DECLARE @EmployeeName nvarchar(150), @htmlBody nvarchar(max)=N'',@htmlheader nvarchar(max),@htmlFooter nvarchar(max)
set @htmlheader = '<html><body>
<div style="padding:10px;width:677px">
<p style="font-size:14px; line-height:20px"><span style="color:red;font-weight:bold">Following item/s are added/updated:</span>
<br/> Employee Code: '+ cast(@EmployeeNo as nvarchar(10))+'<br/> Employee Name:' + @EmployeeName +'
<table style="font-size:9pt;border-collapse:collapse;width:100%" cellpadding="3px">
<tr>
<td style="width:100px;background:#CCCCCC;font-weight:bold;border:1px solid #888888">Item</td>
<td style="width:200px;background:#CCCCCC;font-weight:bold;border:1px solid #888888">Description</td>
<td style="width:50px;background:#CCCCCC;font-weight:bold;border:1px solid #888888">Amount</td>
</tr>'
set @htmlFooter ='</div></body></html>'
SELECT @htmlBody=@htmlBody + '<tr>'
'<td style="border:1px solid #888888;">' +Item+'</td>'
'<td style="border:1px solid #888888;">' +ItemDesc+'</td>'
+'<td style="border:1px solid #888888;">' +Currency +' '+ CONVERT(nvarchar(20),cast(Amount as money),1) +'</td></tr>'
from (Select *,(CASE WHEN Status=2 then '(updated)' else '' end) as StatuStr from EmpPPInsurance where EmployeeNo =@EmployeeNo and Status in (1,2)) T1 order by Item
Set @htmlBody = Replace(@htmlBody, '_x0022_', '''')
Set @htmlBody = Replace(@htmlBody, '_x0023_', '#')
Set @htmlBody = Replace(@htmlBody, '_x003D_', '=')
Set @htmlBody = Replace(@htmlBody, '_x003B_', ';')
Set @htmlBody = Replace(@htmlBody, '_x0020_', ' ')
SET @htmlBody=@htmlheader + @htmlBody + '</table>' + @htmlFooter
I would really appreciate if someone could help me on this.
Thanks