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

    Unanswered: Send e-mail on Error for ShellWait

    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
    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