Return to the EMail Tips
Automating report transmission
by Mark Beckstrom
Reader Mark Beckstrom needed to get his AS/400 to send the results of a
database query automatically. His tip tells us how he did it.
We had a need to generate a daily report by AS/400 Query, and E-mail
the results to a distant location. We also wanted to automate this process
and let the 400 perform the task without intervention.
The problem was to take the results of the query and attach it to an
e-mail message.
The solution was a CL program that does the following:
Runs the query. Note: to avoid "losing" the spooled file, the output
should be held (not sent to a live printer).
Copies the resultant Spool File to a named file.
Converts this named file to ASCII
Sends the converted file as an attachment to E-mail.
Here is a simple CL program that accomplished this task.
0001.00 PGM
0002.00 RUNQRY QRY(QRYLIB/QUERY) RCDSLT(*NO)
0003.00 CPYSPLF FILE(QPQUPRFIL) TOFILE(QRYLIB/NAMEDFILE)
0004.00 CPYTOPCD FROMFILE(QRYLIB/NAMEDFILE) TOFLR(WTCMAIN) +
0005.00 TODOC(ASCII.TXT) REPLACE(*YES)
0006.00 SNDDST TYPE(*DOC) +
0007.00 TOINTNET((mbeckstrom@weltronic.com)) +
0008.00 DSTD('Automatic E-mail report') MSG('This +
0009.00 is a test file sent by CL program +
0010.00 QRYLIB/MAILFILE') DOC(ASCII.TXT) FLR(WTCMAIN)+
0011.00 ENDPGM
The compiled program can then be scheduled to run daily, weekly, etc.
using the WRKJOBSCDE command.
Any program that produces a spooled output can of course replace the RUNQRY command. We run this program under OS/400 V4R2, and our computer is configured for e-mail support.
[report a broken link by clicking here]