You’re sitting in the middle of your computer room, staring at the reams of paper from the monthly reports, and you keep thinking, “There’s 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).

Here’s 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, you’ll 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/400’s 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.

Once you have created all of the folders and subfolders, go to the Microsoft Exchange Server and open the Exchange Server Administration tool. Open Public Folders, look for the folder you created in the previous step, and then go to the File menu and select Properties. From the Properties dialog, select the Email Addresses tab. Figure 1 shows the screen that will come up. Here, look for the SMTP address for the folder; this name will be something like This email address is being protected from spambots. You need JavaScript enabled to view it.. If this address is long, you may want to change it to something shorter (e.g., This email address is being protected from spambots. You need JavaScript enabled to view it.). This address will be used to send the reports from the AS/400 to the public 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. That’s all of the setup required to send email from the AS/400 to the public folder.

The Programs

Now you are ready to create the programs to send your reports to the folders. It’s 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 Microsoft’s 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.

Once you have created the programs and command, you are ready to add the command to your existing reports. The Job name parameter will accept the special value “*” to point to the current job. This can be useful when sending a report from a batch job. Add the command EMLRPT FILE(prtfilename) JOB(*) EMAIL(This email address is being protected from spambots. You need JavaScript enabled to view it.) to the report program of your choice. Remember to make sure that the report you are trying to send is being held when it’s created; otherwise, the report may be gone before EMLRPT has a chance to process it. The cleanest way to add the email option to an existing report is to have your program delete the report from its output queue once it has been sent. This can be accomplished with the Delete Spooled File (DLTSPLF) command using the same file and job options used on the EMLRPT command.

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.

All Aboard!

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.

Click here
to download code.

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 +

(spool file)')

PARM KWD(JOB) TYPE(JOB) DFT(*) SNGVAL((*)) +

PROMPT('Job Name')

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) +

PROMPT('E-Mail Address')

PARM KWD(REPDESC) TYPE(*CHAR) LEN(50) +

PROMPT('Message Subject')

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) +

PROMPT('Page Width')

JOB: QUAL TYPE(*NAME)

QUAL TYPE(*NAME) PROMPT('User ID')

QUAL TYPE(*CHAR) LEN(6) RANGE(000000 999999) +

PROMPT('Job Number')

Figure 2: The EMLRPT command allows you to send AS/400 reports via Internet (SMTP) email.

Mike Faust is a senior consultant/analyst for Retail Technologies Corporation in Orlando, Florida. Mike is also the author of the books Active Server Pages Primer, The iSeries and AS/400 Programmer's Guide to Cool Things, JavaScript for the Business Developer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Mike Faust available now on the MC Press Bookstore.

		Active Server Pages Primer Learn how to make the most of ASP while creating a fully functional ASP "shopping cart" application. List Price $79.00 Now On Sale
		JavaScript for the Business Developer Learn how JavaScript can help you create dynamic business applications with Web browser interfaces. List Price $44.95 Now On Sale
		SQL Built-in Functions and Stored Procedures Unleash the full power of SQL with these highly useful tools. List Price $49.95 Now On Sale

Also Read

TechTip: Getting Started with Microsoft Visual Studio Express 2012

TechTip: Troubleshooting Team Foundation Server

TechTip: Installing Microsoft SQL Server 2012 Express on Your Local Machine