Send Emails From A MS-SQL Stored Procedure

Category:

Database

Type:

Snippets

Difficulty:

Advanced

Author:

Anonymous

Version Compatibility: Visual Basic 6

More information:

This snippet can be used to send emails through a MS-SQL stored procedure. It instantiates the CDONTS.NewMail object via sp_OACreate system stored procedure.

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

Declarations:
' (None)

Code:

CREATE Procedure sn_SMTPMail	
/*********************************************************************
** Lavanya Simon 
**
** 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
 ****** End of Code ******

Lavanya Simon - Simon Nadar