Send Emails thru MS SQL Stored Procedure

Category:

ASP, HTML and XML

Type:

Snippets

Difficulty:

Beginning

Author:

Anonymous

Version Compatibility:

More information:

Send Emails thru MS SQL Stored Procedure using CDONTS

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
None

Code:

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