I needed to write a batch file that would email some files, and could be run as a scheduled task. I chose to use Blat as my email program, you can download it for free from SourceForge.
The batch file requirements were:
1. Had to email all the .xls files in one directory from the current date. Luckily for me the date was in the file name, so I just had to find all files in the format *MMDDYY*.xls
2. The emails had to be sent “To” some users, then “CC”d to others
3. I could only use free software (no shareware) but could use our internal smtp replay server to send the mail through.
4. The Scheduled task that executes the script must run on a Windows 2003 server.
The following script is what I came up with. I’ll go through it line by line, then post the entire thing at the end.
****************************************************
Here I’m getting the current date in the month month day day year year format, and saving it to a variable named search. This can obviously be changed to meet your particular need
REM set search date variable in MMDDYY format for file search
REM set search to month-date-year for date formatting
for /f “Tokens=1-4 Delims=/ ” %%i in (‘date /t’) do set search=%%j%%k%%l
Adding a blank line to the log file ReportLog.txt since I like my logs nice and neat. Makes them easy to read.
echo. >> ReportLog.txt
Writing the date and time the script starts to the log file ReportLog.txt
echo %date% %time% Starting script >> ReportLog.txt
Using the variable search, which contains the current date, I’m getting a list of all .xls files from today and saving it in FileNames.txt
dir /b “\\server\share\%search%*.xls” > FileNames.txt
If there’s a problem retrieving the names of the files, write a message to the log file
IF ERRORLEVEL 1 (echo %date% %time% problem with retrieving file names >> ReportLog.txt)
Using find /c to count how many files are in filenames.txt, and saving that number in the variable NUMFILES
for /f “tokens=3″ %%i in (‘find /v /c “SomeStringNotToBeFound” filenames.txt’) do set NUMFILES=%%i
If the variable NumFiles equals zero, there were no files found for today. Skip to ZEROFILES to send an email alert
If %NUMFILES% EQU 0 GOTO ZEROFILES
If NUMFILES isn’t zero, then files exist that need to be emailed. There may be more than one file, so we’re going to use a loop to process all the files listed in FileNames.txt
For /F “tokens=1-2* delims=” %%B IN (FileNames.txt) DO (
write the name of the file to the log file
echo file to email is %%B >> ReportLog.txt
Use blat.exe to send the email to addresses specified in tolist.txt
Use blat.exe to send emails as CC to addresses specified in cclist.txt
Attached file is listed at the end of the command
This command is all one line, it may wrap on the page
blat.exe -tf tolist.txt -cf cclist.txt -subject “Report %%B” -body “Here is the current report.” -server smtp.yourdomain.com -f
sender@yourdomain.com -attach “\\server\share\%%B”
)
We’re done emailing the files, so skip over to WRITELOG
goto WRITELOG
Zero files were found, so we need to send an email alert
:ZEROFILES
write the name of the file to the log file, along with the date and time
echo %date% %time% problem with retrieving number of current files >> ReportLog.txt
Use blat.exe to send an email alert to addresses specified in problist.txt
This command is all one line, it may wrap on the page
blat.exe -tf problist.txt -subject “Problem with Report” -body “Problem sending the current report.” -server smtp.yourdomain.com -f
sender@yourdomain.com
Writing the date and time the script starts to the log file ReportLog.txt
:WRITELOG
echo %date% %time% Ending Script >> ReportLog.txt
****************************************************
The script in it’s entirely:
REM set search date variable in MMDDYY format for file search
REM set search to month-date-year for date formatting
for /f “Tokens=1-4 Delims=/ ” %%i in (‘date /t’) do set search=%%j%%k%%l
REM format ReportLog.txt
echo. >> ReportLog.txt
echo %date% %time% Starting script >> ReportLog.txt
REM Get the names of all log files for specified date, save to FileNames.txt
dir /b “\\server\share\%search%*.xls” > FileNames.txt
IF ERRORLEVEL 1 (echo %date% %time% problem with retrieving file names >> ReportLog.txt)
REM Count how many files are in filenames.txt, put into %NUMFILES%
for /f “tokens=3″ %%i in (‘find /v /c “SomeStringNotToBeFound” filenames.txt’) do set NUMFILES=%%i
If %NUMFILES% EQU 0 GOTO ZEROFILES
REM loop when more than one file to be emailed
For /F “tokens=1-2* delims=” %%B IN (FileNames.txt) DO (
echo file to email is %%B >> ReportLog.txt
blat.exe -tf tolist.txt -cf cclist.txt -subject “Report %%B” -body “Here is the current report.” -server smtp.yourdomain.com -f
sender@yourdomain.com -attach “\\server\share\%%B”
)
goto WRITELOG
:ZEROFILES
echo %date% %time% problem with retrieving number of current files >> ReportLog.txt
blat.exe -tf problist.txt -subject “Problem with Hold Report” -body “Problem emailing the current hold report.” -server smtp.yourdomain.com -f
sender@yourdomain.com
:WRITELOG
REM format ReportLog.txt
echo %date% %time% Ending Script >> ReportLog.txt
****************************************************
