Youre sitting in the middle of your computer room, staring at the reams of paper from the monthly reports, and you keep thinking, Theres got to be a better way. Well, suppose I could show you a way to reduce all those piles of paper and simplify report distribution in the process (not to mention save a few trees).
Heres the concept. Instead of printing the reports, you send them to a Microsoft Exchange Server public folder. The best part is that you can set the security on the public folder to allow only those users who need to be able to view the reports to have access to them. And the whole process is a lot easier than you might think. There is only a small amount of setup on the AS/400 and Exchange Server, and, using the included utility program, youll be able to send the report in your choice of either plain text format or Microsoft Rich Text Format (RTF). A little bit of modification to your report programs will have your system riding the Great Report Exchange in no time.
Configuring Your AS/400
The setup requirements for this process are minimal. First, you need to get your AS/400 talking to your Exchange Server. To do this, your Exchange Server needs to be running as an Internet mail server. Next, you need to set up your AS/400s distribution services to allow your AS/400 to send email.
To do this, the first thing you need to do is add a host table entry for your Exchange Server using the command ADDTCPHTE INTNETADR(xxx.xxx.xxx.xxx) HOSTNAME(mailservername). Next, you need to point the AS/400 Simple Mail Transfer Protocol (SMTP) server to the mail server using the command CHGSMTPAMAILROUTER(mailservername) AUTOSTART(*YES). This command will also ensure that the SMTP service will start when the TCP/IP interface is started.
Now you must add an entry to the system distribution directory to use as the route from the AS/400 to the Exchange Server. To do this, type the command ADDDIRE USRID(INTERNET SMTPRTE) USRD(SMTP Mail Route) SYSNAME(INTERNET) PREFADR(NETUSRID *IBM ATCONTTXT). After that, enter the command CHGDSTA SMTPRTE(INTERNET SMTPRTE) to tell distribution services to route Internet email
using the SMTP route created earlier. You can test your configuration by first starting the SMTP server using the command STRTCPSVR SERVER(*SMTP), then starting distribution services by typing STRSBS QSNADS, and finally sending a message using the command SNDDST TYPE(*LMSG) TOINTNETADR(your email address) DSTD(Test Message) LMSG(This is a Test!!!). If everything is working properly, you should receive this message in an email from your Exchange Server.
Setting Up Exchange
Now that you have completed the required AS/400 configuration changes, you need to create public folders on the Exchange Server to act as a repository for your reports. This is accomplished from Microsoft Outlook by first going to the All Public Folders folder under the public folders section. Next, go to the File menu and select New Folder from the Folder submenu. Set up the folder to contain POST items and name the folder AS/400 Reports. Make sure you create this folder under the All Public Folders system folder. If you are going to use this tool for several different daily reports, you may want to create a subfolder under the AS/400 Reports folder for each report. This will allow you to keep copies of each report saved for archival purposes. You can do this via the same process used to create the AS/400 Reports folder.
Before leaving the Properties dialog, click on the General tab, and you will notice a button labeled Client Permissions. From this screen, you will be able to control user access to the report folder. This can be used to restrict access to sensitive reports such as those containing payroll information. Thats all of the setup required to send email from the AS/400 to the public folder.
Now you are ready to create the programs to send your reports to the folders. Its important to remember that the programs included with this article can be used to send reports to any Internet email address as long as your email server does Internet email serving.
The process of sending your reports is a fairly simple one. All that is really required is to copy the spool file for the report to a physical file using the Copy Spool File (CPYSPLF) command and then use the Send Distribution (SNDDST) command to send the physical file as an attachment to the email address for the public folder. Shown in Figure 2 (page 90), the Email Report (EMLRPT) command accomplishes this in addition to giving you the option to translate the report into an RTF file that can be viewed by Microsoft Word and most other word processing programs.
Go to the Midrange Computing Web site (www.midrangecomputing. com/mc) to download the RPG program Convert Report to RTF (CVTRPTRTF), which converts the spool file output to RTF. This program uses the RTF code stored in the bottom of the program to convert the file from a text document with no formatting into a file format that is compatible with Microsofts RTF. This format is more printer-friendly than a plain text document. By passing the page width parameter through the EMLRPT command, the program can decide how to fit the report on an 8.5 x 11 inch page. The result is similar to the output achieved when a report is sent to a laser printer that supports automatic page orientation. An 80-column report is presented in portrait orientation, and a 132- or 198- column report is presented in landscape format with differing amounts of font reduction.
The other option is to send the report as plain text. This format can be read by Notepad and any word processing program but will not support the added formatting of the RTF option. The text option may be preferable if you are planning to read the report into Microsoft Excel or another spreadsheet program for parsing.
Also available from the MC Web site, the CL program EMLRPTC uses the CPYSPLF command to copy the report to a physical file. You will notice that the program uses the &FMT variable, which comes from the FMT parameter of the EMLRPT command, to determine what to use for the CTLCHAR parameter on the CPYSPLF command. The program does this because the CVTRPTRTF program needs CTLCHAR to be *PRTCTL, which tells the system, when copying the report to a physical file, to insert two fields at the beginning of the line, one being the SPACEB value for the line and the other the SKIPB value. This is used by the CVTRPTRTF program to insert line feeds into the new RTF file to ensure proper formatting of the RTF document.
At this point, if the &FMT variable is *RTF, CVTRPTRTF is run to convert the report in the physical file to RTF. The output (either the converted RTF version or the plain text version) is copied to an AS/400 shared folder for conversion to a PC document format. Again, the value of &FMT is used to determine the file extension to add to the file name
(.txt or .rtf). Next, the SNDDST command is used to send the PC document from the shared folder to the email address specified on the &EMAIL parameter. When using the EMLRPT command to send the report, you need to specify the spool file name, user ID, job number, and job name for the job as well as the email address to which the report is to be sent.
Remember that, if your report program has multiple files with the same printer file name, you will need to specify the file number as well; otherwise, you can simply use the *LAST option.
Distributing your reports via Exchange Server public folders is easy to do and can help you reduce paper and printer supply expenditures. Plus you now have an efficient way to archive your reports off your AS/400. This means that, when someone is looking for the end-of-month inventory reports for January in June, you can simply send them to the Great Report Exchange.
Figure 1: Use the Microsoft Exchange Server Administration tool to view or change the properties on your report folder.
/* E-MAIL A REPORT IN TEXT, RTF OR HTML FORMAT */
/* COMPILE: CRTCMD CMD(QGPL/EMLRPT) */
/* PGM(QGPL/EMLRPTC) */
CMD PROMPT('Send Report via E-Mail')
PARM KWD(FILE) TYPE(*NAME) MIN(1) PROMPT('Report +
PARM KWD(JOB) TYPE(JOB) DFT(*) SNGVAL((*)) +
PARM KWD(SPLNBR) TYPE(*DEC) LEN(4) DFT(*LAST) +
SPCVAL((*ONLY 0) (*LAST -1)) +
PROMPT('Spool File #')
PARM KWD(EMAIL) TYPE(*CHAR) LEN(40) MIN(1) +
PARM KWD(REPDESC) TYPE(*CHAR) LEN(50) +
PARM KWD(FMT) TYPE(*CHAR) LEN(5) RSTD(*YES) +
DFT(*RTF) VALUES(*TEXT *RTF) +
PROMPT('Transfer to Format')
PARM KWD(LLEN) TYPE(*DEC) LEN(3 0) RSTD(*YES) +
VALUES(80 132 198) DFT(132) +
JOB: QUAL TYPE(*NAME)
QUAL TYPE(*NAME) PROMPT('User ID')
QUAL TYPE(*CHAR) LEN(6) RANGE(000000 999999) +
Figure 2: The EMLRPT command allows you to send AS/400 reports via Internet (SMTP) email.