asked on

Monitoring Files - Alert Email

Good Evening.

I am looking for a process that checks a specific folder for a PDF document. If the file does not exist it sends an email to a user.

Conditions

1) Check folder named DDMMYYYY and look for a random PDF document.

2) If no file exists send an email using SMTP configuration information.

3) As part of the email body include the path of the folder being checked.

Thanks

Arana (G.P.)

how often will the email be sent? how often do you want it to be checked? once a day?

we dont want to cehck for the file every millisecond and send thoussands of emails until user uploads or generates file

Nick Collins

ASKER

I am planning to run the script in the task scheduler every few hours...

Bill Prew

Do you have any solutions in VBS that send an email currently (can't remember if I did one for you for something else at some point)?

»bp

Bill Prew

Okay, based on what you described, this seems to work in a test here. Adjust to fit your needs and give it some testing there (after reading and understanding the code).

' Constants
Const cSmtpUser = "username"                                ' *** MAKE CHANGES HERE ***
Const cSmtpPassword = "password"                            ' *** MAKE CHANGES HERE ***
Const cSmtpServer = "smtp.xxx.yyy"                          ' *** MAKE CHANGES HERE ***
Const cSmtpPort = 465                                       ' *** MAKE CHANGES HERE *** (25, 465, 587 common)
Const cFromEmail = "xxxxxxxxx@xxx.yyy"                      ' *** MAKE CHANGES HERE ***
Const cToEmail = "xxxxxxxxx@xxx.yyy"                        ' *** MAKE CHANGES HERE ***
Const cSubject = "No Files Found Alert"                     ' *** MAKE CHANGES HERE ***
Const cBaseDir = "c:\temp"                                  ' *** MAKE CHANGES HERE ***
Const cPdfFile = "random.pdf"                               ' *** MAKE CHANGES HERE ***

' CDO Constants needed to send email
Const cCdoSendUsingPickup = 1   'Send message using the local SMTP service pickup directory.
Const cCdoSendUsingPort = 2     'Send the message using the network (SMTP over the network).
Const cCdoAnonymous = 0         'Do not authenticate
Const cCdoBasic = 1             'basic (clear-text) authentication
Const cCdoNTLM = 2              'NTLM
Const cCdoSendUsingMethod        = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cCdoSMTPServer             = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cCdoSMTPServerPort         = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cCdoSMTPConnectionTimeout  = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cCdoSMTPAuthenticate       = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cCdoSendUserName           = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cCdoSendPassword           = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
Const cCdoSmtpUseSsl             = "http://schemas.microsoft.com/cdo/configuration/smtpusessl"

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Format todays date as YYYYMMDD
strDate =  Year(Now()) & LPad(Month(Now()), 2, "0") & LPad(Day(Now()), 2, "0")

' Build base folder path to check
strBaseDir = cBaseDir & "\" & strDate

' Exit if it doesn't exist
If Not objFSO.FolderExists(strBaseDir) Then
    Wscript.Quit()
End If

strPdfPath = strBaseDir & "\" & cPdfFile
'Exit if file exists
If objFSO.FileExists(strPdfPath) Then
    Wscript.Quit()
End If

' Send email indicating no files found...

' Get a handle to the config object and it's fields
Set objConfig = CreateObject("CDO.Configuration")

' Set config fields we care about
With objConfig.Fields
    .Item(cCdoSendUsingMethod)       = cCdoSendUsingPort
    .Item(cCdoSMTPServer)            = cSmtpServer
    .Item(cCdoSMTPServerPort)        = cSmtpPort
    .Item(cCdoSMTPConnectionTimeout) = 60
    .Item(cCdoSMTPAuthenticate)      = cCdoBasic
    .Item(cCdoSendUserName)          = cSmtpUser
    .Item(cCdoSendPassword)          = cSmtpPassword
    .Item(cCdoSmtpUseSsl)            = True
    .Update
End With

' Create a new message
Set objMessage = CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig

' Populate message fields and send it
With objMessage
    .To       = cToEmail
    .From     = cFromEmail
    .Subject  = cSubject
    .TextBody = "No files found" & vbNewLine & "Folder Path: " & strPdfPath
    .Send
End With

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Open in new window

»bp

Nick Collins

ASKER

I am doing some testing..

In the code you asking for the filename of the PDF document..

Const cPdfFile = "DW_??????_???????.pdf" ' *** MAKE CHANGES HERE ***

The filename is random but based like this "DW_??????_???????.pdf"

Is there a way the code find this file

Bill Prew

We would have to add some regular expression matching.

Please indicate what the question marks can represent (numbers, letters, special characters) in each position, and provide examples of the names that need to be located.

I assume there will be other PDF files in this folder then, that you don't want to consider?

And will there only be one file matching the pattern you described, or could there be multiple?

»bp

Nick Collins

ASKER

There is always going to be one file within the file..

The question marks represent numbers

Bill Prew

And just to confirm, it is 6 numbers in the first group, and then 7 in the second?

»bp

Nick Collins

ASKER

Yes that's correct

Bill Prew

Okay, this seems to work in a test here.

' Constants
Const cSmtpUser = "username"                                ' *** MAKE CHANGES HERE ***
Const cSmtpPassword = "password"                            ' *** MAKE CHANGES HERE ***
Const cSmtpServer = "smtp.xxx.yyy"                          ' *** MAKE CHANGES HERE ***
Const cSmtpPort = 465                                       ' *** MAKE CHANGES HERE *** (25, 465, 587 common)
Const cFromEmail = "xxxxxxxxx@xxx.yyy"                      ' *** MAKE CHANGES HERE ***
Const cToEmail = "xxxxxxxxx@xxx.yyy"                        ' *** MAKE CHANGES HERE ***
Const cSubject = "No Files Found Alert"                     ' *** MAKE CHANGES HERE ***
Const cBaseDir = "c:\temp"                                  ' *** MAKE CHANGES HERE ***
Const cPdfFile = "DW_[0-9]{6}_[0-9]{7}\.pdf"                ' *** MAKE CHANGES HERE ***

' CDO Constants needed to send email
Const cCdoSendUsingPickup = 1   'Send message using the local SMTP service pickup directory.
Const cCdoSendUsingPort = 2     'Send the message using the network (SMTP over the network).
Const cCdoAnonymous = 0         'Do not authenticate
Const cCdoBasic = 1             'basic (clear-text) authentication
Const cCdoNTLM = 2              'NTLM
Const cCdoSendUsingMethod        = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cCdoSMTPServer             = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cCdoSMTPServerPort         = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cCdoSMTPConnectionTimeout  = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cCdoSMTPAuthenticate       = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cCdoSendUserName           = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cCdoSendPassword           = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
Const cCdoSmtpUseSsl             = "http://schemas.microsoft.com/cdo/configuration/smtpusessl"

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Format todays date as YYYYMMDD
strDate =  Year(Now()) & LPad(Month(Now()), 2, "0") & LPad(Day(Now()), 2, "0")

' Build base folder path to check
strBaseDir = cBaseDir & "\" & strDate

' Exit if it doesn't exist
If Not objFSO.FolderExists(strBaseDir) Then
    Wscript.Quit()
End If

' Create regular expression for matching files to search for
Set objRegEx = New RegExp
objRegEx.Pattern = cPdfFile
objRegEx.IgnoreCase = True

' Count number of matching files
intCount = 0
Set objFolder = objFSO.GetFolder(strBaseDir)
For Each objFile In objFolder.Files
    If objRegEx.Test(objFile.Name) Then
        intCount = intCount + 1
    End If
Next

'Exit if file(s) exist
If intCount > 0 Then
    Wscript.Quit()
End If

' Send email indicating no files found...

' Get a handle to the config object and it's fields
Set objConfig = CreateObject("CDO.Configuration")

' Set config fields we care about
With objConfig.Fields
    .Item(cCdoSendUsingMethod)       = cCdoSendUsingPort
    .Item(cCdoSMTPServer)            = cSmtpServer
    .Item(cCdoSMTPServerPort)        = cSmtpPort
    .Item(cCdoSMTPConnectionTimeout) = 60
    .Item(cCdoSMTPAuthenticate)      = cCdoBasic
    .Item(cCdoSendUserName)          = cSmtpUser
    .Item(cCdoSendPassword)          = cSmtpPassword
    .Item(cCdoSmtpUseSsl)            = True
    .Update
End With

' Create a new message
Set objMessage = CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig

' Populate message fields and send it
With objMessage
    .To       = cToEmail
    .From     = cFromEmail
    .Subject  = cSubject
    .TextBody = "No files found" & vbNewLine & "Folder Path: " & strBaseDir
    .Send
End With

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Open in new window

»bp

Nick Collins

ASKER

Hey Bill, I am have been some testing through the task scheduler but I am not having much luck on what errors might be..

Its run through the task scheduler and completes without the results I am looking for..

it is possible to add a log file to the script - so it can detail it progress

Bill Prew

Okay, here's a version that writes to a log file, adjust the path for that as needed. I always like to test from the command line first and only once the script / logic is working well there move on to testing in Task Manager...

' Constants
Const cSmtpUser = "username"                                ' *** MAKE CHANGES HERE ***
Const cSmtpPassword = "password"                            ' *** MAKE CHANGES HERE ***
Const cSmtpServer = "smtp.xxx.yyy"                          ' *** MAKE CHANGES HERE ***
Const cSmtpPort = 465                                       ' *** MAKE CHANGES HERE *** (25, 465, 587 common)
Const cFromEmail = "xxxxxxxxx@xxx.yyy"                      ' *** MAKE CHANGES HERE ***
Const cToEmail = "xxxxxxxxx@xxx.yyy"                        ' *** MAKE CHANGES HERE ***
Const cSubject = "No Files Found Alert"                     ' *** MAKE CHANGES HERE ***
Const cBaseDir = "c:\temp"                                  ' *** MAKE CHANGES HERE ***
Const cPdfFile = "^DW_[0-9]{6}_[0-9]{7}\.pdf$"              ' *** MAKE CHANGES HERE ***
Const cLogFile = "C:\Temp\[[SCRIPTNAME]]_[[DATE]].log"      ' *** MAKE CHANGES HERE ***

' CDO Constants needed to send email
Const cCdoSendUsingPickup = 1   'Send message using the local SMTP service pickup directory.
Const cCdoSendUsingPort = 2     'Send the message using the network (SMTP over the network).
Const cCdoAnonymous = 0         'Do not authenticate
Const cCdoBasic = 1             'basic (clear-text) authentication
Const cCdoNTLM = 2              'NTLM
Const cCdoSendUsingMethod        = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cCdoSMTPServer             = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cCdoSMTPServerPort         = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Const cCdoSMTPConnectionTimeout  = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cCdoSMTPAuthenticate       = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Const cCdoSendUserName           = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Const cCdoSendPassword           = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
Const cCdoSmtpUseSsl             = "http://schemas.microsoft.com/cdo/configuration/smtpusessl"

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2


' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Format todays date as YYYYMMDD
strDate =  Year(Now()) & LPad(Month(Now()), 2, "0") & LPad(Day(Now()), 2, "0")

' Get current script name
strScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)

' Build log file path
strLogFile = objFSO.GetAbsolutePathname(Replace(cLogFile, "[[DATE]]", strDate, 1, -1, vbTextCompare))
strLogFile = objFSO.GetAbsolutePathname(Replace(strLogFile, "[[SCRIPTNAME]]", strScriptName, 1, -1, vbTextCompare))

' Build base folder path to check
strBaseDir = cBaseDir & "\" & strDate

' Exit if it doesn't exist
If Not objFSO.FolderExists(strBaseDir) Then
    LogIt strLogFile, "Missing folder: """ & strBaseDir & """", True, True
    Wscript.Quit()
End If

' Create regular expression for matching files to search for
Set objRegEx = New RegExp
objRegEx.Pattern = cPdfFile
objRegEx.IgnoreCase = True

' Count number of matching files
intCount = 0
Set objFolder = objFSO.GetFolder(strBaseDir)
For Each objFile In objFolder.Files
    LogIt strLogFile, "Checking file: """ & objFile.Path & """", True, True

    If objRegEx.Test(objFile.Name) Then
        LogIt strLogFile, "File Matched: """ & objFile.Path & """", True, True
        intCount = intCount + 1
    End If
Next

LogIt strLogFile, "File count: " & intCount, True, True

'Exit if file(s) exist
If intCount > 0 Then
    LogIt strLogFile, "Matching file found, quitting...", True, True
    Wscript.Quit()
End If

' Send email indicating no files found...
LogIt strLogFile, "No matching file found, sending email...", True, True


' Get a handle to the config object and it's fields
Set objConfig = CreateObject("CDO.Configuration")

' Set config fields we care about
With objConfig.Fields
    .Item(cCdoSendUsingMethod)       = cCdoSendUsingPort
    .Item(cCdoSMTPServer)            = cSmtpServer
    .Item(cCdoSMTPServerPort)        = cSmtpPort
    .Item(cCdoSMTPConnectionTimeout) = 60
    .Item(cCdoSMTPAuthenticate)      = cCdoBasic
    .Item(cCdoSendUserName)          = cSmtpUser
    .Item(cCdoSendPassword)          = cSmtpPassword
    .Item(cCdoSmtpUseSsl)            = True
    .Update
End With

' Create a new message
Set objMessage = CreateObject("CDO.Message")
Set objMessage.Configuration = objConfig

' Populate message fields and send it
With objMessage
    .To       = cToEmail
    .From     = cFromEmail
    .Subject  = cSubject
    .TextBody = "No files found" & vbNewLine & "Folder Path: " & strBaseDir
    .Send
End With

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

' Small function to write a line to log file and/or the console
Sub Logit (strFile, strText, blnFile, blnConsole)
    ' Write to log file if requested
    If blnFile Then
        With objFSO.OpenTextFile(strFile, ForAppending, True)
            .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
            .Close()
        End With
    End If

    ' Write to console if requested
    If blnConsole Then
        Wscript.Echo FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
    End If
End Sub

Open in new window

»bp

Nick Collins

ASKER

I have tested through the command line and it works...

Going through the scheduler it doesn't work - code 2147942401

The only thing I can think of is that the file location on a mapped drive as the file is not local.

What do you think of adding a procedure within the code to map a drive with a username and password

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial