Write efficient email sending code in VBScript?

[MrPeds]

Hi,

I would like to be able to send a simple email via an ActiveX VBScript in one of my DTS packages, e.g.

If (someCoditionIsTrue) then
'send email code here
else
'dont do anything
end if

My current email code seems very inefficient, it causes Enterprise manager to crash, as tho the codee has a memory leak. Is there any way of making my code simpler??

My code is:

Dim oPackage
Dim oMailTask 'oCustomTask1
Dim oMail 'holds send mail task object
Dim oStep

Set oPackage = DTSGlobalVariables.Parent

'Clean the task if there is already exists
For Each oMailTask In oPackage.Tasks

If oMailTask.Name = ("DTSSendMailTask_runtime") Then
oPackage.Tasks.Remove ("DTSSendMailTask_runtime")
End If
Next

'Clean the Steps if there is already exists
For Each oStep In oPackage.Steps
If oStep.Name = "AutoMailSend" Then
oPackage.Steps.Remove "AutoMailSend"
End If
Next

'Adding a new step to this package
Set oStep = oPackage.Steps.New

'invoking DTSSendMailTask method for a task
Set oMailTask = oPackage.Tasks.New("DTSSendMailTask")

'Give a namefor the task you are invoking
oMailTask.Name = ("DTSSendMailTask_runtime")

Set oMail = oMailTask.CustomTask

With oMail
.Profile = "MS Exchange Settings"
.ToLine = "myEmailAddress"
.Subject = DTSGlobalVariables("strSubject").Value
.MessageText = DTSGlobalVariables("strMsg").Value
.SaveMailInSentItemsFolder = False
End With


oStep.TaskName = oMail.Name
oStep.Name = "AutoMailSend"
oPackage.Steps.Add oStep
oPackage.Tasks.Add oMailTask

oPackage.Steps("AutoMailSend").Execute
oPackage.Tasks.Remove ("DTSSendMailTask_runtime")
oPackage.Steps.Remove ("AutoMailSend")
oPackage.UnInitialize

'clean and tidy up the code
Set oPackage = Nothing
Set oStep = Nothing
Set oMail = Nothing

Please, does anybody see how this can be made simpler? and to stop the code crashing Enterprise Manager? I only want the code really simple, just sending an email as quick as possible with a few lines of text and that is all,

Thanks in advance,

MrPeds

 

[dbomrrsm]

check out xp_sendmail in BOL

DBomrrsm

 

[cbhnp]

I'm not sure why you are adding a SendMailTask and executing it at runtime.

I usually create a global variable to hold an error flag. I use a ExecSQL task to set the value of the variable based on whatever the condition is that I'm checking for. Then I use an ActiveX task to check the value of the variable. If I find the variable is flagged, I exit the ActiveX task reporting a failure, else success. The SendMailTask is created at design time to run if the ActiveX task results in failure.

HTH,
cb