Send an Email from Stored Procedure 2

[mihaildamjan]

Hello all,

Got this code that is supposed to send an email directly from the stored procedure.
It would be really neat to be able to do that ,but allthough it doesn't error out, it does not send the email at all.
My understanding is that if you have the Outlook set up on the PC that you are running the procedure from, you also have cdonts.dll required for the procedure to work.

Please run the code below and if you get it to work , please let me know how you did it.


***************************
DECLARE @result INT
DECLARE @object INT
DECLARE @propertyvalue VARCHAR(255)

PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

IF @result <> 0
PRINT 'sp_OACreate Failed'
ELSE BEGIN
PRINT 'Get the From property'
EXEC @result = sp_OAGetProperty @object, 'Version', @propertyvalue OUTPUT
IF @result <> 0
PRINT 'sp_OAGetProperty Failed'
ELSE BEGIN
PRINT 'CDONTS Version = ' + @propertyvalue
PRINT 'Set the From property'
EXEC @result = sp_OASetProperty @object, 'From', 'mihail.damjan@SomeEmailAddress.com'
IF @result <> 0
PRINT 'sp_OASetProperty Failed'
ELSE BEGIN
PRINT 'Sending the message using the Send method'
EXEC @result = sp_OAMethod @object, 'Send', NULL, 'mihail.damjan@SomeEmailAddress.com', 'mihail.damjan@SomeEmailAddress.com',
'My test message', 'Hello world! Look at my body!'
IF @result <> 0
PRINT 'sp_OAMethod Failed'
ELSE BEGIN
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
PRINT 'sp_OADestroy Failed'
END
END
END
END

*******************************************

and here is the result of running the procedure

Creating the CDONTS.NewMail object
Get the From property
CDONTS Version = 1.2
Set the From property
Sending the message using the Send method
Destroying the CDONTS.NewMail object

***************************

 

[mihaildamjan]

I should have emphasized which email address is recipient and which one is a sender.

Here is the procedure again

***************************
DECLARE @result INT
DECLARE @object INT
DECLARE @propertyvalue VARCHAR(255)

PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

IF @result <> 0
PRINT 'sp_OACreate Failed'
ELSE BEGIN
PRINT 'Get the From property'
EXEC @result = sp_OAGetProperty @object, 'Version', @propertyvalue OUTPUT
IF @result <> 0
PRINT 'sp_OAGetProperty Failed'
ELSE BEGIN
PRINT 'CDONTS Version = ' + @propertyvalue
PRINT 'Set the From property'
EXEC @result = sp_OASetProperty @object, 'From', 'mihail.damjan@FromEmailAddress.com'
IF @result <> 0
PRINT 'sp_OASetProperty Failed'
ELSE BEGIN
PRINT 'Sending the message using the Send method'
EXEC @result = sp_OAMethod @object, 'Send', NULL, 'mihail.damjan@FromEmailAddress.com', 'mihail.damjan@ToEmailAddress.com',
'My test message', 'Hello world! Look at my body!'
IF @result <> 0
PRINT 'sp_OAMethod Failed'
ELSE BEGIN
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
PRINT 'sp_OADestroy Failed'
END
END
END
END

*******************************************


Thanks,

Mihail

 

[Patrick2002]

Why make it so hard on yourself to send a mail from a SP ? just use master..xpsendmail with the correct parameters...it's a breeze. But setting things up so that it works can be a pain. Read all about it on the following page, and in BOL of course :

http://support.microsoft.com/default.aspx?scid=kb;en-us;311231

Good luck with solving your problem.

 

[mihaildamjan]

Patrick2002,

Thank you very much.
I never used xpsendmail procedure. I'll look it up and if I get it to work I'll post the resolution here.

Meanwhile, if someone knows why is CDONTS object not working and how to get it working, post the resolution and it will be greatly appreciated.

Tx,

Mihail

 

[mihaildamjan]

Patrik,

xp_sendmail works fine, and it's so easy.
Thank you very much.

Mihail