Stored procedure to send Mails in SQL

--All leave application should be answered within 48 hours at the maximum.
--Unanswered leave applications will go to Vishal/kanika straight away after 48 hours.
--Later you can use it in SQL Job as well

CREATE Procedure 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 @str varchar(200)
DECLARE cur CURSOR FOR Select id from tablename
OPEN cur FETCH NEXT FROM cur INTO @t WHILE @@FETCH_STATUS = 0 BEGIN SET @str = 'Leave id: ' + cast(@t as varchar(20)) + ' is due more than 48 hours. Please Take Appropriate Action. ' EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT EXEC @resultcode = sp_OASetProperty @oMail, 'From', 'janki_shah@yahoo.com'
EXEC @resultcode = sp_OASetProperty @oMail, 'To', 'jankit_shah@yahoo.com'
--EXEC @resultcode = sp_OASetProperty @oMail, 'HTMLBody', 0
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', 'Reminder'
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @str
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