By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,209 Members | 1,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,209 IT Pros & Developers. It's quick & easy.

to send email autometicaly through stored procedure

P: 9
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_IDENTIFIER 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.Configuration', @config OUT -- create the configuration object

-- Configuration Object
SET @Bcc='shahnawazshaikh@cdnsol.com'
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'localhost' -- SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, 'Fields.Update'

-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Bcc', @Bcc
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @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_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
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.
May 26 '07 #1
Share this Question
Share on Google+
2 Replies


kenobewan
Expert 2.5K+
P: 4,871
What problem or error are you receiving? Does this code work?
May 26 '07 #2

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

no there is no error but through i can not recive mail.
May 28 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.