28
Tue, May
0 New Articles

Easily Distribute AS/400 Reports with Microsoft Exchange

Microsoft
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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
.


Easily_Distribute_AS-_400_Reports_with_Microsoft_Exchange04-00.png 400x300

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

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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: