Using a DTS package to send an email using CDOSYS and vbscript 1

[panini]

hi there

I've a 2003 server running MS SQL 2000. I've a customer table and an orders table. The orders have an end date. I'd like to fire an email off when an order ends thanking the customer.

So....

I've been trying to use a scheduled DTS package to do this.

I've set up a microsoft OLE DB connection to the database, followed by an Execute SQL Task, then an ActiveX script task.

I've set the output global variable's in the sql task to pull the variables out of it and it says it runs ok. when i get to the activex script task it says that there's an error which is CDO.Message.1 'interface not registered' this is the code i'm using....

Dim iMsg
Dim iConf
Dim Flds
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
Flds("

http://schemas.microsoft.com/cdo/configuration/urlproxybypass")

= "<local>"
Flds("

http://schemas.microsoft.com/cdo/configuration/urlgetlatestversion")

= True
Flds.Update
With iMsg
Set .Configuration = iConf
.CreateMHTMLBody "

http://www.yahoo.com"

.To = DTSGlobalVariables("email").Value
.From = "webmaster@xyz.com"
.Subject = "thanks for your order....."
.Send
End With


If i try to use simpler code...

Dim iMsg
Set iMsg = CreateObject("CDO.Message")

With iMsg
.To = "me@xyz.com"
.Newsgroups = "area.example.newsgroup1"
.Subject = "Agenda for staff meeting"
.TextBody = "Please plan to present your status for the following projects..."
End With

Then i just get 'function not found'....

I've tried registering the cdosys.dll using 'Regsvr32.exe c:\windows\System32\CDOSYS.dll' but no joy - is there another activex dll that needs to be registered?

Any help really appreciated - Many Thanks

 

[dbomrrsm]

you could use a DTS package that daily selects all orders where the enddate is equal to GETDATE () and puts them along with the customers email address into a table.

another part of the DTS can then run a stored procedure you can create that goes through this created table and using xpsendmail sends the email you want to all the email addresses with a max(enddate)

 

[panini]

many thanks DBomrrsm, think i'm getting there... it needed to be within a function and have the success bit below in it....

Function Main()

Dim iMsg
Dim iConf

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set iMsg.Configuration = iConf

iMsg.To = DTSGlobalVariables("email").Value
iMsg.From = "me@example.co.uk"
iMsg.Subject = DTSGlobalVariables("orderNo").Value
iMsg.CreateMHTMLBody DTSGlobalVariables("htmlorderConfString").Value&DTSGlobalVariables("orderNo").Value, cdoSuppressAll
iMsg.Send

if err.Number = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if

End Function

However the above code only works for the first order and then stops - my attempts to put a WHILE WEND loop in it below aren't working....

Function Main()

Dim iMsg
Dim iConf

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set iMsg.Configuration = iConf
set objResults = DTSGlobalVariables("orderNo").Value

while not objResults.EOF

iMsg.To = DTSGlobalVariables("email").Value
iMsg.From = "me@example.co.uk"
iMsg.Subject = DTSGlobalVariables("orderNo").Value
iMsg.CreateMHTMLBody DTSGlobalVariables("htmlorderConfString").Value&DTSGlobalVariables("orderNo").Value, cdoSuppressAll
iMsg.Send

objResults.MoveNext
wend

if err.Number = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if

End Function

Any advice gratefully received

 

[dbomrrsm]

my pc is up the wall at the moment - I will post some code I have tomorrow to show you how to loop through a table and pick up and email a list of email addresses.

DBomrrsm

 

[dbomrrsm]

Here we go:

CREATE PROC uspSendThemMail
AS BEGIN
SET NOCOUNT ON

DECLARE @em nvarchar(100), @rc int

DECLARE YourCursor CURSOR
FOR SELECT EmailAddress FROM ...

OPEN YourCursor
FETCH NEXT FROM YourCursor INTO @em

WHILE @@Fetch_Status = 0 BEGIN

EXEC @rc = master.dbo.xp_sendmail
@FROM = N'MyUsername@xxx.com',
@TO = @em,
@priority = N'HIGH',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Goodbye MAPI, goodbye Outlook',
@type = N'text/plain',
@server = N'aaaaa'

SELECT Email = @em, RC = @rc

FETCH NEXT FROM YourCursor INTO @em

END

CLOSE YourCursor
DEALLOCATE YourCursur
END

 

[panini]

great many thanks!