Windows cmd batch check file exists in various directories – send email notification via blat.exe commandline tool

4. Oktober 2020

This code solves the following issue:

Check if a file with a specific extension (here *.laccdb) exists in various directories and send an email to a group of recepients using blat.exe only if at least one file exists.

Send multiple lines in blat email body using pipe-character (|) via txt-log-file.

I wrote this code for a customer who is running multiple MS Access routines over night to pull data from an SQL-Server into an ACCDB.

If any of these processes would hang the LACCDB-file is not deleted and therefore the Administrator has to fix the issue.

The following batch is running every morning at 6am to check for these issues.

REM Check if a file with a specific extension (here *.laccdb) exists in various directories and send an email to a group of recepients using blat.exe only if at least one file exists.
REM Send multiple lines in blat email body using pipe-character (|) via txt-log-file.
REM Author: Gerd Besch (https://www.besch-it.com) (c) 2020
@echo off

set Log_File=D:\Scripts\Check_LAccdb_Log.txt

REM these are the directories to be checked. If at least one file (*.laccdb) in any of these directories exists something went wrong and we want to receive an email notification
set Folder1=C:\Data\*.laccdb
set Folder2=D:\Data1\Dir2\Access\*.laccdb
set Folder3=\\Server2\Dir2\GetData1.laccdb
set Folder4=\\Server2\Dir3\GetData2.laccdb
set Folder5=\\Server2\Dir4\ACC\Databases\GetData.laccdb

set Count1=0
set Count2=0
set Count3=0
set Count4=0
set Count5=0
set Total_Count=0

set Mail_Subject="GetData is NOT finished - laccdb(s) exists"
set Mail_Body=
set Separator=______________________________________________

echo|set /P ="Checkdate: %DATE%-%TIME%|" >%Log_File%
echo|set /P ="The following directories contain laccdb-files:|" >>%Log_File%

@for /f %%a in ('2^>nul dir "%Folder1%" /a-d/b/-o/-p/s^|find /v /c ""') do set Count1=%%a
if %Count1% gtr 0 (
echo|set /P ="%Folder1%|" >>%Log_File%
)

@for /f %%a in ('2^>nul dir "%Folder2%" /a-d/b/-o/-p/s^|find /v /c ""') do set Count2=%%a
if %Count2% gtr 0 (
echo|set /P ="%Folder2%|" >>%Log_File%
)

@for /f %%a in ('2^>nul dir "%Folder3%" /a-d/b/-o/-p/s^|find /v /c ""') do set Count3=%%a
if %Count3% gtr 0 (
echo|set /P ="%Folder3%|" >>%Log_File%
)

@for /f %%a in ('2^>nul dir "%Folder4%" /a-d/b/-o/-p/s^|find /v /c ""') do set Count4=%%a
if %Count4% gtr 0 (
echo|set /P ="%Folder4%|" >>%Log_File%
)

@for /f %%a in ('2^>nul dir "%Folder5%" /a-d/b/-o/-p/s^|find /v /c ""') do set Count5=%%a
if %Count5% gtr 0 (
echo|set /P ="%Folder5%|" >>%Log_File%
)

set /a Total_Count=%Count1%+%Count2%+%Count3%+%Count4%+%Count5%
echo Total_Count: %Total_Count%

REM read the Log-File into a variable. Each line has to be separated with a pipe character (| --> this ist ascii-charakter 124) --> press and hold ALT-key, type 124 on numeric keyboard, release ALT-key)
set /p Mail_Body=<%Log_File% REM only send emails if total error count is > 0
if %Total_Count% gtr 0 (
"C:\Program Files (x86)\Blat\Blat.exe" -Body "%Mail_Body%" -subject %Mail_Subject% -to "recipient1@domain.com" -priority "1" -sensitivity "2" -server MailServer3 -f administrator@domain.com
"C:\Program Files (x86)\Blat\Blat.exe" -Body "%Mail_Body%" -subject %Mail_Subject% -to "recipient2@domain.com" -priority "1" -sensitivity "2" -server MailServer3 -f administrator@domain.com
"C:\Program Files (x86)\Blat\Blat.exe" -Body "%Mail_Body%" -subject %Mail_Subject% -to "recipient3@domain.com" -priority "1" -sensitivity "2" -server MailServer3 -f administrator@domain.com
echo eMail send
)

Dieser Code behebt folgendes Problem:

Prüfe ob eine Datei eine bestimmte extension (hier: *.laccdb) hat in verschiedenen Verzeichnissen und sende eine mail an eine Gruppe von Empfängern mit blat.exe nur dann, wenn mindestens eine Datei existiert.

Sende mehrere Zeilen im blat email text (body) mit dem pipe-character (|) über ein txt-log-file.

Ich habe diesen Code für einen Kunden geschrieben der über Nacht mehrere MS Access jobs laufen lässt welche Daten aus einem SQL-Server in eine ACCDB abziehen.

Wenn einer dieser Prozesse hängen bleibt befindet sich im jeweiligen Zielverzeichnis eine LACCDB-Datei die normalerweise bei korrekter Beendigung des Prozesses gelöscht wird. Ist dies nicht der Fall muss der Administrator eingreifen um das Problem zu beheben.

Dieser Batch läuft jeden Morgen um 6 Uhr um die Probleme an eine Gruppe von Personen zu mailen.

How to fix it

❦