It is possible to send a spreadsheet from the AS/400 via e-mail as an attachment. The following example uses a Qry/400 output file, but it could just as easily be done with a program output file, or Opnqryf output. There are a few simple rules to follow:
1. Each field within the file must be separated by a ',' (comma). In Qry/400, this can be done by defining result fields such as COM1, COM2, COM3, etc. -- each having an expression of ',' and placing these in between your data fields.
2. Numeric fields MUST be Zoned Decimal, NOT Packed. To do this in Qry/400, just use the field in a calculation. Define a Result field and use the file field in the expression, e.g. add zero to it or multiply by 1. Then use the Result field in the output file. Make SURE you specify a length for the Result field otherwise it will pack it. 'Summary' Result fields are always Zoned Decimal.
Once the file has been created, you need to convert it to ASCII by copying it to an AS/400 folder with the following command:
/* COPY THE FILE TO A PC DOC */
CPYTOPCD FROMFILE(MYFILE) TOFLR(MYFOLDER) +
TODOC(MYFILE.CSV) REPLACE(*YES)
The 'TODOC' name can be any name, but it must have the prefix '.CSV', which is a file extension of 'Comma Separated Values'.
Now, send it as an e-mail attachment with the following command:
SNDDST Type(*DOC) TOINTNET((me@pfizer.com)) +
DSTD('This is my Email Function') MSG('Attached is the data +
You requested') IMPORTANCE(*HIGH) +
DOC(MYFILE.CSV) FLR(MYFOLDER)
The Recipient will get an e-mail message stating 'Attached is the data you requested,' along with the attached file called MYFILE.CSV.
When opened in Excel, there's all your data in nice neat rows and columns. (It should work the same in Lotus).
Comments and caveats
There is an obvious chance that you may have commas embedded within your text fields, which will skew the data columns in the spreadsheet. To prevent this, I wrote a small RPG program to convert any of these embedded commas to 'blank' before it gets converted to the folder.
There is a setting on the 400 that dictates how large a message can be. If this is set too small, your attachment will be sliced and diced into multiple garbled e-mail messages.
Check your POP Server Attributes (CHGPOPA) or see the IBM Support document, reference number 862565c2007cda23 for the fix.
The SNDDST can send to AS/400 user IDs as long as their SMTP addresses has been entered in the Directory Entries. The User ID will resolve to the actual SMTP address.
