declare

@email varchar(200),

@subject varchar(200),

@htmlBody varchar(max)

declare c1 cursor for

--#########################################################################

SELECT

e.Email,

'Deliverable Due' AS Subject,

'You have a deliverable due on - ' + d.DueDate + '. It is ' + d.Deliverable AS Body

FROM dbo.tblEmployeesResponsible

INNER JOIN CommonData.dbo.tblEmployee AS e

ON dbo.tblEmployeesResponsible.EmpID = e.EmpID

INNER JOIN dbo.tblDeliverables AS d

ON dbo.tblEmployeesResponsible.DelCatID = d.DelCatID

WHERE DATEADD(dd, DATEDIFF(dd,0,d.DueDate), 0) = DATEADD(dd, DATEDIFF(dd,0,getdate() -7), 0)

--#########################################################################

open c1

fetch next from c1 into @email,@subject,@htmlBody

While @@fetch_status <> -1

begin

--now the email itself:

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Your Database Mail',

@recipients=@email,

@subject = @subject,

@body = @htmlBody,

@body_format = 'HTML';

fetch next from c1 into @email,@subject,@htmlBody

end

close c1

deallocate c1

GO