Using SQL Server Agent to schedule a job to e-mail a file

[jsiebert]

I am attempting to use SQL Server Agent to schedule a job to automatically run a batch file that e-mail's an output file. The application runs fine on the server from the command line, but when I attempt to run it as a job via SQL Server, it hangs at MAPISes.SignOn and generates the following error: “Error Number 32026 Not Supported”. I’m attaching the entire VB function. Any ideas?

Public Function SendMAPIMail(MsgTo As String, _
Optional CC As String = "", _
Optional Subject As String = "", _
Optional Body As String = "", _
Optional Att As String = "&quot
As Boolean

Dim MAPISession As MAPISession
Dim MAPIMessages As MAPIMessages
Dim strErrorMessage As String

On Error GoTo errorhandler

Screen.MousePointer = 11

Set MAPISession = frmMail.MAPISession1
Set MAPIMessages = frmMail.MAPIMessages1

MAPISession.DownLoadMail = False

MAPISession.LogonUI = True

MAPISession.SignOn 'based on tests, this is where it hangs

DoEvents

' check to see if login was successful

If MAPISession.SessionID = 0 Then
'write errormessage to log file

SendMAPIMail = False
strErrorMessage = "Error On Login to MAPI"
Open "C:\Temp\testlog.txt" For Append As #1
Write #1, strErrorMessage
Close #1
Screen.MousePointer = 0
Exit Function
End If

MAPIMessages.SessionID = MAPISession.SessionID

'set the MsgIndex to -1
'this needs to be done for the compose event to work

MAPIMessages.MsgIndex = -1

MAPIMessages.Compose

'do not show the resolve address interface

MAPIMessages.AddressResolveUI = False

'set the receipiant

MAPIMessages.RecipIndex = 0
MAPIMessages.RecipType = mapToList
MAPIMessages.RecipAddress = MsgTo

'resolve the recipiants' e-mail address

MAPIMessages.ResolveName

'set the CC receipiant

' MAPIMessages.RecipIndex = 1
' MAPIMessages.RecipType = mapCcList
' MAPIMessages.RecipAddress = CC

'resolve the recipiants' e-mail address

'MAPIMessages.ResolveName

'set the subject

MAPIMessages.MsgSubject = Subject

'set the Message/Body/NoteText

MAPIMessages.MsgNoteText = Body

If Att <> &quot;&quot; Then
'set an attachment

MAPIMessages.AttachmentPathName = Att

End If

MAPIMessages.Send

'close the current session

MAPISession.SignOff

'clear the objects

Set MAPIMessages = Nothing
Set MAPISession = Nothing

SendMAPIMail = True

Screen.MousePointer = 0

Exit Function

errorhandler:

Set MAPIMessages = Nothing
Set MAPISession = Nothing

Screen.MousePointer = 0

strErrorMessage = &quot;An error occurred in MAPIMail: Error Number &quot; & Err.Number & &quot; &quot; & Err.Description
Open &quot;C:\Temp\testlog.txt&quot; For Append As #1
Write #1, strErrorMessage
Close #1

On Error Resume Next
frmMail.MAPISession1.SignOff
SendMAPIMail = False

End Function

 

[PFR]

dont know the MAPI... objects, but due to the script running from command prompt expect that either
the NT-rights for the SQLServer agent are not sufficient
or
&quot;MAPISession.LogonUI = True&quot; leads to the object using the desktop that is not present for the ssagent.

for such tasks i used the cdo objects &quot;CDONTS.newMail&quot; - that did work and is easy to use
another way is to keep running Your script from cmd and use ssagent only for starting it.