Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Send e-mail on Error for ShellWait

    I posted this In the Access forum also but it should probably be here.....

    Iím using the following code to open batch files located on a network drive. I would like to add code to an error handler that would send me an e-mail if something goes wrong.
    I canít seem to get this to work and Iíve been googling forever. I suspect itís not possible or Iím approaching it completely wrong. Any insight would be greatly appreciated. Iím using Microsoft access 2010.

    Public Sub ShellWaitNew(Pathname As String, Optional windowStyle As Long)
    On Error GoTo Senderr

    Dim start As STARTUPINFO
    Dim ret As Long
    ' Initialize the STARTUPINFO structure:
    With start
    .cb = Len(start)
    If Not IsMissing(windowStyle) Then
    .wShowWindow = windowStyle
    End If
    End With
    ' Start the shelled application:
    ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
    NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    ret& = CloseHandle(proc.hProcess)

    Exit Sub

    Set outapp = CreateObject("Outlook.Application")
    Set OutMail = outapp.CreateItem(0)

    With OutMail
    .To = ""
    .Subject = "Error Occured - Error Number " & err.Number
    .Body = err.Description

    '.Display '~~> Change this to .Send for sending the email
    End With

    Set outapp = Nothing: Set OutMail = Nothing

    End Sub

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    o how do you 'know' the process has teminated succesfully or otherwise.
    I woudl have thought you need to do this as part of a batch file. Offhand I con't think of a way within Access to identoify if an exteranl process has failed or succeeded aside form
    say as part of that batch process you write a log file or set a return code to a file and then read the file afteer the process has completed

    I woudl expect in say a vbscript or other batch file to have far better error handling...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Well I found some code “RunShell” and modified it with a Global Variable called BadCode.
    I added the batch files to a different VBA function (myTest5) with the first line in the function setting the BadCode global variable equal to the name of that function.
    If the function bombs the name of the function is passed to the error handler in the RunShell code which then includes it in an e-mail notification.
    Not the most elegant but it seems to work.

    Public Function mytest5()
    BadCode = "mytest5"
    On Error GoTo err_mytest5

    RunShell "C:\TQCodes.bat"
    'RunShell ExecuteTrustQueriesnew
    RunShell "C:\TQAssetTypeszzz.bat"
    BadCode = vbNullString
    'Exit Function

    End Function

    Public Function RunShell(CmdLine As String)

    On Error GoTo Senderr
    Dim hProcess As Long
    Dim ProcessId As Long
    Dim exitCode As Long

    Dim mybadcode As String

    Dim SAFEMAIL As Variant
    Dim myOlApp, MyItem, myRecipient, myBody, myfolder, mynamespace, myAttachments, Utils
    'mybadcode = vbNullString
    mybadcode = BadCode
    ProcessId = Shell(CmdLine, 1)
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, ProcessId)


    Call GetExitCodeProcess(hProcess, exitCode)

    Loop While exitCode = STATUS_PENDING

    Call CloseHandle(hProcess)

    'MsgBox "The shelled process " & CmdLine & " has ended."

    Exit Function

    Set myOlApp = CreateObject("Outlook.Application")
    Set MyItem = myOlApp.CreateItem(0)

    With MyItem

    .To = ""
    .Subject = "Error Occured - Error Number " & err.Number & mybadcode
    .Body = err.Description

    '.Display '~~> Change this to .Send for sending the email
    End With

    Set myOlApp = Nothing: Set MyItem = Nothing
    mybadcode = vbNullString

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts