to send email autometicaly through stored procedure

[shahnawaz shaikh]

hi
friends
i want to send email thoruth stored procedure or i want to give functionality like alert which is send email automaticaly from database.

just like a news or any ad email weekly or monthly automaticaly if u'r suscribed in that site.

i like this type of functionality.

i use for this stored procedure :
set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go





Alter PROCEDURE [dbo].[usp_SMTPEMAIL]
(
@From as nvarchar(500),
@To as nvarchar(500),
@Subject as nvarchar(1000),
@Body as text
)
AS

-- Declare
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @Bcc varchar(40)

EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
EXEC @hr = sp_OACreate 'CDO.Configurat ion', @config OUT -- create the configuration object

-- Configuration Object
SET @Bcc='shahnawaz shaikh@cdnsol.c om'
EXEC @hr = sp_OASetPropert y @config, 'Fields(cdoSend UsingMethod)', 'cdoSendUsingPo rt' -- Send the message using the network
EXEC @hr = sp_OASetPropert y @config, 'Fields(cdoSMTP Server)', 'localhost' -- SMTP Server
EXEC @hr = sp_OASetPropert y @config, 'Fields(cdoSMTP ServerPort)', 25 -- Server SMTP Port
EXEC @hr = sp_OASetPropert y @config, 'Fields(cdoSMTP Authenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, 'Fields.Update'

-- Message Object
EXEC @hr = sp_OASetPropert y @message, 'Configuration' , @config -- set message.configu ration = config
EXEC @hr = sp_OASetPropert y @message, 'To', @To
EXEC @hr = sp_OASetPropert y @message, 'From', @From
EXEC @hr = sp_OASetPropert y @message, 'Bcc', @Bcc
EXEC @hr = sp_OASetPropert y @message, 'Subject', @Subject
EXEC @hr = sp_OASetPropert y @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'

-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config

-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorIn fo @message, @src OUT, @desc OUT
SELECT hr=convert(varb inary(4),@hr), Source=@src, Description=@de sc
RETURN
END



this procedure is for sending email.
and i call this procedure in another procedure. In another procedure i fetch email and subject and body etc and pass it to this procedure.

[kenobewan]

What problem or error are you receiving? Does this code work?

[shahnawaz shaikh]

What problem or error are you receiving? Does this code work?

no there is no error but through i can not recive mail.