devxlogo

Sending Email Through a Stored Procedure in SQL Server 2000

Sending Email Through a Stored Procedure in SQL Server 2000

Use this stored procedure to send an email:

DECLARE @SenderAddress varchar(100)DECLARE @RecipientAddress varchar(100)DECLARE @Subject varchar(200)DECLARE @Body varchar(8000)DECLARE @oMail int --Object referenceDECLARE @resultcode intSET @SenderAddress = '[email protected]'SET @RecipientAddress= '[email protected]'SELECT @Subject = 'subject of email for today ' + CAST(getdate() ASvarchar(12))SET  @Body = 'This is the body of my email'EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUTIF @resultcode = 0BEGIN   EXEC @resultcode = sp_OASetProperty @oMail, 'BodyFormat', 0   EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 0   EXEC @resultcode = sp_OASetProperty @oMail, 'Importance', 1   EXEC @resultcode = sp_OASetProperty @oMail, 'From',@SenderAddress   EXEC @resultcode = sp_OASetProperty @oMail, 'To',@RecipientAddress   EXEC @resultcode = sp_OASetProperty @oMail, 'Subject',@Subject   EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body   EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL   EXEC sp_OADestroy @oMailEND

devx-admin

Share the Post: