How to send mails From Stored Procedure?

Here we go...

If we want to write a code that sends Newsletters via Emails to many users, then what we can do ideally something like this
/*
for loop
{
( send mail); /* takes long time */
}

Mails are sent using SMTP server n it takes a long time then the for loop execution speed..
so what we need to do is, we should insert some kind of waiting code between each mail sending.. like,
for loop
{
( send mail); /* takes long time */
thread.sleep(5000); /* wait for 5 seconds..*/
}

but I have a great method if you are using MS SQL server as your database...

We can write a stored proc that can send emails behalf of us....

what we do here
we pass folowing parameters...
From Address,To address,subject and Message Body...

You could schedue a job to run an SProc that uses the
CDO mail object to send out your emails.

We need here two objects that is
1. Message object
2. Configuration Object
After setting properties of each of these objects u can use it to send your emails...
Just use this stored procedure and create one job that runs periodically on your server..

Here is the source code...

===============================================
CREATE PROCEDURE sp_SMTPemail
(
@From as nvarchar(50)
,@To as nvarchar(50)
,@Subject as nvarchar(255)
,@Body as text
) --WITH ENCRYPTION--
AS
-- Declare
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
SET @hr = 0

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
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)',
'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)',
'your.server.com' -- 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, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'bodyformat',0
EXEC @hr = sp_OASetProperty @message, 'MailFormat',0
EXEC @hr = sp_OASetProperty @message, 'HTMLBody', @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
-- log and/or handle the error here if there is one...
RETURN
END
GO