email using MS Sql

email using MS Sql

CREATE Procedure sn_SMTPMail
/*********************************************************************
** Simon / Lavanya
**
** PURPOSE:
** This stored procedure is used to send email from MS SQL Database
**
** TABLE USED:
** TEMP
** RESULT:
** Email Sent.
** Command:
** exec sn_SMTPMail
**********************************************************************/
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
BEGIN
DECLARE @t int
DECLARE cur CURSOR FOR Select id from temp where checkrecord = 'Y' and termdate > getdate() - 10
OPEN cur
FETCH NEXT FROM cur INTO @t
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', 'lavanyasimon@yahoo.com'
EXEC @resultcode = sp_OASetProperty @oMail, 'To', 'simonnadar@yahoo.com'
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @t
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', 'ID Expires in next 10 days. Please Take Appropriate Action'
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
FETCH NEXT FROM cur INTO @t
END
CLOSE cur
DEALLOCATE cur
END
SET nocount off
GO