Thursday, September 15, 2011

Send mail with HTML Table format in subject area

declare @AccountTable Table(RowIds int Identity(1,1),AccountName varchar(50),ModifiedDate datetime,ExpiryDate datetime)
declare @cntAccounts int
declare @flgAcconts int
declare @AccountName varchar(50)
declare @ModifiedDate datetime
declare @ExpiryDate datetime
declare @BodyText varchar(max)
set @flgAcconts=1
Insert into @AccountTable select * from dbo.Passwords where  DATEDIFF(DAY,Getdate(),expiry_date) <=15
select @cntAccounts=COUNT(1) from @AccountTable
if @cntAccounts > 1
begin
DECLARE @AdminEmails NVARCHAR(1000)
SELECT @AdminEmails = 'jay@micro.com'
DECLARE @Subject NVARCHAR(500)
SELECT @Subject = 'Expiry Date of Accounts'
set @BodyText =
            N'<table border="1" cellpadding="2" cellspacing="0" >' +
            N'<tr bgcolor = "#cccccc"><th>Account Name</th><th>ModifiedDate</th><th>Expiry Date</th></tr>'
      
while (@flgAcconts <=@cntAccounts)
begin
      select @AccountName=accountName,@ModifiedDate=ModifiedDate,@ExpiryDate=ExpiryDate from @AccountTable where RowIds=@flgAcconts
      set @flgAcconts=@flgAcconts+1
      set @BodyText =@BodyText+ N'<tr bgcolor = ""><td>'+convert(varchar(50),@AccountName)+'</td>' +
            N'<td>'+convert(varchar(10),@ModifiedDate,102)+'</td>'+
            N'<td>'+convert(varchar(10),@ExpiryDate,102)+'</td></tr>'
end  
set @BodyText=@BodyText+'</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default'
,@body_format = 'HTML'
,@importance = 'High'
,@recipients = @AdminEmails
,@subject = @Subject
,@body = @BodyText
end

No comments:

Post a Comment