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 proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret As Long
' Initialize the STARTUPINFO structure:
.cb = Len(start)
If Not IsMissing(windowStyle) Then
.dwFlags = STARTF_USESHOWWINDOW
.wShowWindow = windowStyle
' 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)
Set outapp = CreateObject("Outlook.Application")
Set OutMail = outapp.CreateItem(0)
.To = "firstname.lastname@example.org"
.Subject = "Error Occured - Error Number " & err.Number
.Body = err.Description
'.Display '~~> Change this to .Send for sending the email
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