WOrks order section
Dim strSpecNameDrwgMF As String
Dim strManvDrwg As String
Dim strDistDrwgMF As String
Dim strSubjectDrwgMF As String
Dim strMsgDrwgMF As String
'******write log file*********
Open "C:/nightlyreports.txt" For Append As #1 ' Open file to read,
Print #1, Now(); "changecont open"
Close #1
strSpecNameDrwgPO = "purvdrwg import specification"
strSpecNameExcepPO = "pur-except import specification"
strPath = "\\Kevingriggs\user\AccessTemp\"
strPurvDrwg = "purvdrwg"
strPurExcept = "purexcept"
strExtn = ".txt"
strExcel = "excel"
strDistDrwgPO = "k.griggs@aim-henshalls.co.uk"
strDistExceptPO = "k.griggs@aim-henshalls.co.uk"
strSubjectDrwgPO = "Parts on Order at incorrect Issue"
strSubjectExceptPO = "Expedite Purchase Order Lines"
StrMsgDrwgPO = "The parts detailed in the attached Excel file are showing a conflict between the part issue and the part issue on the PO. Please check and amend as required"
strMsgExceptPO = "The required dates for the parts detailed in the attached Excel files are sooner than the PO date."
'strDay = Format(Now(), "ddd")
' Works Order Section
strSpecNameDrwgMF = "manvdrwg import specification"
strManvDrwg = "manvdrwg"
strDistDrwgMF = "k.griggs@aim-henshalls.co.uk"
strSubjectDrwgMF = "Works Orders at incorrect issue."
strMsgDrwgMF = "The parts detailed in the attached Excel file are showing a conflict between the part issue and the part issue on the Works Order. Please check and amend as required"
strDistcheck = "k.griggs@aim-henshalls.co.uk"
strsubjectcheck = "ERROR"
strmsgnopurvdrwg = "ChangeCont-2000 was unable to continue due to: File \\Kevingriggs\user\AccessTemp\purvdrwg.txt Not found"
strmsgnopurexcept = "ChangeCont-2000 was unable to continue due to: File \\Kevingriggs\user\AccessTemp\purexcept.txt Not found"
strmsgnopurexcept = "ChangeCont-2000 was unable to continue due to: File \\Kevingriggs\user\AccessTemp\ManvDrwg.txt Not found"
If Dir("\\Kevingriggs\user\AccessTemp\purvdrwg.txt") = "" Then
DoCmd.SendObject acSendNoObject, , , strDistcheck, , , strsubjectcheck, strmsgnopurvdrwg, False
ElseIf Dir("\\kevingrggs\user\accesstemp\purexcept.txt") = "" Then
DoCmd.SendObject acSendNoObject, , , strDistcheck, , , strsubjectcheck, strmsgnopurexcept, False
ElseIf Dir("\\kevingrggs\user\accesstemp\manvdrwg.txt") = "" Then
DoCmd.SendObject acSendNoObject, , , strDistcheck, , , strsubjectcheck, strmsgnoManvDrwg, False
Else
'If strDay = "thu" Then
DoCmd.DeleteObject acTable, strPurvDrwg
DoCmd.TransferText acImportDelim, strSpecNameDrwgPO, strPurvDrwg, strPath & strPurvDrwg & strExtn, True
DoCmd.SendObject acOutputQuery, strPurvDrwg & strExcel, acFormatXLS, strDistDrwgPO, , , strSubjectDrwgPO, StrMsgDrwgPO, False
'******write log file*********
Open "C:/nightlyreports.txt" For Append As #1 ' Open file to read,
Print #1, Now(); "changecont closing"
Close #1
DoCmd.DeleteObject acTable, strPurExcept
DoCmd.TransferText acImportDelim, strSpecNameExcepPO, strPurExcept, strPath & strPurExcept & strExtn, True
DoCmd.SendObject acOutputQuery, strPurExcept & strExcel, acFormatXLS, strDistExceptPO, , , strSubjectExceptPO, strMsgExceptPO, False
'DoCmd.DeleteObject acTable, "manvdrwg_importerrors"
DoCmd.Quit
DoCmd.DeleteObject acTable, strManvDrwg
DoCmd.TransferText acImportDelim, strSpecNameDrwgMF, strManvDrwg, strPath & strManvDrwg & strExtn, True
DoCmd.SendObject acOutputQuery, strManvDrwg & strExcel, acFormatXLS, strDistDrwgMF, , , strSubjectDrwgMF, strMsgDrwgMF, False
DoCmd.Quit
End If
DoCmd.Quit
End Sub