Here you go, FYI this must be run under an account that has admin privs for
creation of the OA object
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
"D. Shane Fowlkes" wrote:
Yes I am. That would be GREAT!
--
"keithinsac" <ke********@discussions.microsoft.com> wrote in message
news:87**********************************@microsof t.com... Are you using MS SQL? You could schedue a job to run an SProc that uses
the
CDO mail object to send out your emails. I have an SProc that you could
call
to generate the mails if so. Reply and I will supply if interested...
"D. Shane Fowlkes" wrote:
I've decided to send weekly emails to registered users ("members") on one
of
my sites. This will be the first time I attempted anything like this.
Now
I know I could use some 3rd party list server services like MailChimp.com
or
ConstantContact.com but since this site I have is basically maintained
and
developed for free, I want to keep my overhead as low as possible.
Before I actually dive into this and build one from scratch, is there
anything out there that is free or very inexpensive and is written in
ASP/VB.NET? I've tried searching this forum and even google but the
problem
is I'm not sure what keyword or phrase to search WITH. However, even if
I
do create one from scratch, which does seems fairly achievable, then I
have
a "loop" question.
Assuming that I do build one from scratch, I'd like to loop thru all the
members and email them a customized HTML email containing data
specifically
for them. So to reduce server strain and possible issues, I'd like to
place
a ...let's say....5 second delay within the loop so it sends an email
every
5 seconds.
So how would one add a delay in a loop? The only thing I can think of is
to
have a variable actually watch the seconds on the system clock ....and
that
seems pretty wasteful or resources.
And ideas or thoughts?
Thanks!
