Hi,
I have the below code that picks up a file based on the contents of a Excel range. However, there will be occasions where a file won't be present. On these occasions I want the code to ping an email to me to let me know. As it stands, the below is sending me 3 emails rather than 1, can anyone help?
I have the below code that picks up a file based on the contents of a Excel range. However, there will be occasions where a file won't be present. On these occasions I want the code to ping an email to me to let me know. As it stands, the below is sending me 3 emails rather than 1, can anyone help?
Code:
Function ImportTLogAuto()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM TLog"
Dim objDialog As Object
Dim varFile As Variant
Dim strsql As String
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
With xlApp
Set xlWB = .Workbooks.Open("C:\Program Files\Morning Imports.xlsm", , False)
Set xlSheet = xlWB.Worksheets("PickUp") 'Name of tab you want to select from
End With
varFile = xlSheet.Range("D9")
On Error GoTo NoTLog
DoCmd.TransferText acImportDelim, "TLog Import", "TLog", varFile
strsql = "UPDATE TLog SET TLog.Filename = '" & varFile & "' " _
& "WHERE (((TLog.Filename) Is Null));"
CurrentDb.Execute strsql
DoCmd.OpenQuery "Qry_Append_RecordLines"
DoCmd.OpenQuery "Qry_Append_Record61"
NoTLog:
Call Error_Email("404 - TLog Not Found")
Resume Next
End Function