Avatar of Luis Diaz
Luis Diaz
Flag for Colombia asked on

VB Script: check if files exist and datemodified is equal of today if not send a notification e-mail

Hello experts,

I have three files with the following names:
m.csv, tt.csv, and sp.csv

Those files are generated on daily basis in C:\test and moved to an archive folder.

I need a VB Script which to do the following:

1-Check if the files exist in C:\test (Be careful, the loop cannot be by count>0 as We have other files in C:\test
2-Check if the date modified of the files is equal of today.
3-If one of those conditions is not met:
The script need to run the following notification code (I want a unique .vbs (loops+notification)


' Send by connecting to port 25 of the SMTP server.
Dim iMsg 
Dim iConf 
Dim Flds 
Dim strHTML

Const cdoSendUsingPort = 2

set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")

Set Flds = iConf.Fields


' Set the CDOSYS configuration fields to use port 25 on the SMTP server.

With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    'ToDo: Enter name or IP address of remote SMTP server.
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "" 
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
    .Update
End With

' Build HTML for message body.
strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<p>-----This message has been sent automatically-----<p>"
strHTML = strHTML & "Hello,</br><p>Csv files haven't been generated today,</b> please check the interface.</p>"
strHTML = strHTML & "<p>Regards,</p>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"

' Apply the settings to the message.
With iMsg
    Set .Configuration = iConf
    .To = "" 'ToDo: Enter a valid email address.
    .From = "" 'ToDo: Enter a valid email address.
    .Subject = "[WARNING] Csv files for CC  haven't been generated today"
    .HTMLBody = strHTML
    .AddAttachment "C:\test\log-files-checks.txt"
    .Send
End With

' Clean up variables.
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

WScript.Quit

Open in new window


log requirements:
1-Log name: log-files-checks.txt
2-If the folder in which the script check the files don't exist log file should contains Now & "The folder" & variable doesn't exist
3-If one of the files m.csv, tt.csv, and sp.csv" doesn't exist log file file should contains Now & “ERROR”: ” & ": One of the following files "m.csv, tt.csv, and sp.csv" hasn't been found
4-If datemodified of one of the file is not equal of today Now & “ERROR” & “the following files : m.csv, tt.csv, and sp.csv" has a datemodified equal of today
4-If the notification is sent the log file should contain Now & "ERROR: Notification has been sent."
5-If the files are in the folder with a date modified of today the log file should contains Now “SUCESS: All is good files exist in the folder and the datemodified is equal of today notification hasn’t been sent”
VB Script

Avatar of undefined
Last Comment
Luis Diaz

8/22/2022 - Mon
Bill Prew

Give this a test (after changing paths as needed), seems to be working here.  I didn't change the notification email code you provided, so didn't test that out since you will need to do that there supplying the right server and credentials, etc.

' File I/O constants
Const ForAppending = 8

' Define files and folders to process
strBaseDir = "B:\EE\EE28624929\test"
strLogName = "B:\EE\EE28624929\log-files-checks.txt"
arrFiles = Array("m.csv", "tt.csv", "sp.csv")

' Create filesystem object, access folder to scan
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open log file for appending
Set objLog = objFSO.OpenTextFile(strLogName, ForAppending, True)

' Make sure base folder exists, error out if not
If Not objFSO.FolderExists(strBaseDir) Then
   objLog.WriteLine Now & " ERROR: The folder """ & strBaseDir & """ does not exist."
   objLog.Close
   Wscript.Quit
End If

' Check each file in the array of files to check
blnErrors = False
For Each strFile In arrFiles
   ' Build the full path
   strPath = strBaseDir & "\" & strFile

   ' See if this file exists
   If objFSO.FileExists(strPath) Then
      ' It exists, now see if it was modified today
      Set objFile = objFSO.GetFile(strPath)
      If DateDiff("d", objFile.DateLastModified, Now) > 0 Then
         ' Not mofified today, report this error
         objLog.WriteLine Now & " ERROR: The file """ & strPath & """ was not modified today."
         blnErrors = True
      End If
   Else
      ' Doesn't exist, report this error
      objLog.WriteLine Now & " ERROR: The file """ & strPath & """ does not exist."
      blnErrors = True
   End If
Next

' See if any of the files checked didn't exist, or were not modified today
If blnErrors Then
   ' Some files had errors, send notification email
   objLog.WriteLine Now & " ERROR: Notification has been sent."
   SendNotification
Else
   ' No errors, all files good, log that
   objLog.WriteLine Now & " SUCCESS: All files exist and were modified today."
End If

' Close log file
objLog.Close()
Wscript.Quit

Sub SendNotification()
   ' Send by connecting to port 25 of the SMTP server.
   Const cdoSendUsingPort = 2

   set iMsg = CreateObject("CDO.Message")
   set iConf = CreateObject("CDO.Configuration")

   Set Flds = iConf.Fields

   ' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
   With Flds
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
       'ToDo: Enter name or IP address of remote SMTP server.
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "" 
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
       .Update
   End With

   ' Build HTML for message body.
   strHTML = "<HTML>"
   strHTML = strHTML & "<HEAD>"
   strHTML = strHTML & "<BODY>"
   strHTML = strHTML & "<p>-----This message has been sent automatically-----<p>"
   strHTML = strHTML & "Hello,</br><p>Csv files haven't been generated today,</b> please check the interface.</p>"
   strHTML = strHTML & "<p>Regards,</p>"
   strHTML = strHTML & "</BODY>"
   strHTML = strHTML & "</HTML>"

   ' Apply the settings to the message.
   With iMsg
       Set .Configuration = iConf
       .To = "" 'ToDo: Enter a valid email address.
       .From = "" 'ToDo: Enter a valid email address.
       .Subject = "[WARNING] Csv files for CC  haven't been generated today"
       .HTMLBody = strHTML
       .AddAttachment "C:\test\log-files-checks.txt"
       .Send
   End With

   ' Clean up variables.
   Set iMsg = Nothing
   Set iConf = Nothing
   Set Flds = Nothing
End Sub

Open in new window

~bp
Luis Diaz

ASKER
Hello Bill,

I have tested: if the files doesn't exist, if its exist but with a datemodified older than today if one of the file doesn't exists If the folder doesn't exists and ITS WORKS PERFECTLY!!

I forgot an extra check :

Is there a way to add an extra check related to the file size: if one of the file has a size file equal to 0  Now  & ERROR  "One of the file hasn't a proper size"  sendnotification.
ASKER CERTIFIED SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Luis Diaz

ASKER
It works!

Thank you again for your help!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck