How do I tell CoolSpools who to email a converted spooled file to?

CoolSpools has some great features to handle this. Which way is best for you depends on a number of things like where your email addresses are stored and what information there is in the spooled file to identify who the output is intended for. Whatever your needs, we're confident we have an option that suits you.

Let's take a look at some of the methods available to you.

Simple scenarios

1. I always want to send the entire spooled file to the same person/people.

This is obviously the simplest situation. You can, of course, just list the email addresses (and, optionally, names) of the people you want to send or cc: the output to on the EMAILTO parameter of the CVTSPLPDF, CVTSPLXLS etc. command, for example:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO((john.smith@sales.mycorp.com 'John Smith, Sales Dept' *PRI)

(sally.brown@sales.mycorp.com 'Sally Brown, Sales Dept.' *CC))

But you should consider using an email address list.

An email address list is a simple list of email addresses (and, optionally, names) that you give a name to. You can then specify just the email address name on the EMAILTO parameter and send the email to everyone in the list that way.

The big advantage of this approach comes when the list of people you need to send to changes. Imagine you're emailing sales reports to everyone in the Sales Department. What happens when somebody leaves or a new person joins? If you've hard-coded the list in your spooled file rules or scripts or in your CL code, you'll need to modify all of those to take account of the change of personnel. But if you've used an email address list, all you need do is modify that list once, and the job is done.

You can create email address lists using the CRTADRL command and you can manage them using WRKADRL.

For example, to create a list for the Sales Department, you might do the following. First create the email address list and give it a name and some descriptive text:

CRTADRL ADRL(SALES_DEPT) TEXT('Sales Department Email Addresses')

Now add John and Sally to the list:

ADDADRLE ... 

ADRL(SALES_DEPT)

EMAIL('john.smith@sales.mycorp.com')

NAME('John Smith, Sales Dept.')

TYPE(*PRI)

ADDADRLE ... 

ADRL(SALES_DEPT)

EMAIL('sally.brown@sales.mycorp.com')

NAME('Sally Brown, Sales Dept.')

TYPE(*CC)

Finally, to send the report to everyone in the list, specify:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO((SALES_DEPT *ADRL *ADRL))

2. I always want to send the entire spooled file to the user who created it

For this you need to use a CoolSpools variable

Those that are relevant here are:

So, using these CoolSpools variables, the command:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO(('<:SPLUSEREMAIL:>' '<:SPLUSERNAME:>'))

would email the report as a PDF to the user who ran the job.

This begs the question: where does CoolSpools get the email address and/or name of a particular user from?

The answer is from the system directory. The system directory can be managed using the WRKDIRE command. Select option 2 against the user profile you wish to change and modify the user's name information there. Press F19 to change the email address associated with the user.

Alternatively, the command WRKNAMSMTP TBLTYPE(*SYSTEM) lets you change just the email address of a user.

Note that CoolSpools also uses the information stored here to obtain the sender's email address and name when EMAILFROM(*CURRENT) is specified on the CVTSPLPDF, CVTDBFXL commands etc. or on the FROM parameter of SNDCMNMSG.

3. The email address(es) to send to is/are printed in the report

That's unusual, but very convenient!

If the email address is not already printed on the report, and you're able to change the report easily, you might consider adding it. If you don't want people to see it, you could put it beyond the right margin, or below the bottom margin, so it falls off the page, or (if you're using AFP) define it as white text on a white background so it's invisible. Either way, CoolSpools can still find it and use it.

To use it, you will need to use one of the special CoolSpools variables <:EXITPGMPOSn:> or <:EXITPGMKEYn:> to pick it up from inside the spooled file contents at run time. For example, if the email address was on page 1, line 65, position 3 and was 25 characters long, you could use:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO(('<:EXITPGMPOS1:>'))

EXITPGM(*VAR)

EXITPGMPRM(*POS)

EXITPGMPOS((1 65 3 25))

See the FAQ answer on CoolSpools variables for further details of how to use <:EXITPGMPOSn:> and <:EXITPGMKEYn:> to pick it information from inside the spooled file contents at run time.

More complex scenarios

I'm splitting the spooled file up into multiple PDFs (Excel files, etc.) and want to email each file to a different person/list of people.

This is a very common scenario and one CoolSpools can handle really well.

Perhaps your spooled file is a batch of customer invoices and each invoice needs to be sent to the relevant customer as a PDF. Or maybe the spooled file is a sales report by region, and you want to email the relevant section of the report as a spreadsheet to the salesforce for the region in question.

For details of how to split a spooled file into multiple output files (PDFs, Excel files etc.), see the FAQ answer on that topic.

There are several ways in which the email address(es) to be used for each output file can be notified to CoolSpools. Which method is best for you will depend on factors such as

• Do you already have the email address(es) in a file or do you need to store them somewhere new?

  If they're already in a file, you'll want to pick them up from there. If they're not already in a file, you can use a CoolSpools email address list as an easy new way to store them.

• What information is there in the report that can be used to look up the email address(es) and how easy is it to map from that information to the email address(es)?

  For example, if your report contains a customer number, and your email addresses are on the customer file or some other file keyed by customer number, CoolSpools can read the addresses from the file for you using the number extracted from the report at run time.

  But if your report doesn't have the customer number, but just an invoice number, then a two-stage lookup will be needed (get the customer number for the invoice, then the email address(es) for the customer), and that will need a different technique.

I don't have the email address(es) on file at the moment

In this case, you might want to use a CoolSpools email address list as a convenient place to store your email addresses.

Imagine then you need to send each invoice in a batch of invoices to the relevant customer, and that the customer number is in the report on page 1, line 10, position 20 for a length of 6 characters. If you create an email address list for each customer, and make sure you implement a strict naming convention such as CUSTOMER_nnnnnn for each address list (where nnnnnn is the customer number), you can then send each report to the appropriate customer using a command such as this:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO(('CUSTOMER_<:EXITPGMPOS1:>' *ADRL *ADRL))

EXITPGM(*VAR)

EXITPGMPRM(*POS)

EXITPGMPOS((1 10 20 6))

See the FAQ answer on CoolSpools variables for further details of how to use <:EXITPGMPOSn:> and <:EXITPGMKEYn:> to pick it information from inside the spooled file contents at run time.

See below for details of how to handle the situation where, for whatever reason, no email address list exists for the customer, or that list is empty.

I want CoolSpools to read the email address(es) from my customer file (email address file etc.) using a key that's in the report.

This could be a job for the EMAILTO(*EMAILFILE)option and the EMAILFILE command parameter.

This parameter allows you to specify a file from which CoolSpools should read the email address(es) to use. You can also specify the key or keys to use to look up the email address(es). Typically, at least one of those keys will be a piece of data you've extracted from the spooled file at run time and you're referring to using CoolSpools variables.

For example, you might pick up the customer number from the page contents at run time and use that as a key to read email addresses from your customer file.

You can also, optionally, read a name from the file and also an email recipient type (primary, cc:, bcc:).

For example:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO(*EMAILFILE)

EMAILFILE(CSTMST EMAIL CUSNAM *NONE ((CUSTNO *EQ <:CUST_NBR:>')))

SPLIT(*POS)

SPLITPOS((5 9 7))

EXITPGMPRM(*POS)

EXITPGMPOS((1 5 9 7 CUST_NBR))

Here, the spooled file contains a batch of customer account statements, and each separate statement needs to be emailed to the appropriate customer, which is different in each case.

The spooled file contains the customer number on line 5, position 9 for 7 characters, and a new PDF is started every time this value changes. The same value is also extracted from page 1 of the customer statement at run time by means of the EXITPGMPOS parameter, and given the user-defined name CUST_NBR.

The EMAILFILE parameter specifies that the email addresses to be used are in customer file CSTMST and that the email address is held in field EMAIL and the name in field CUSNAM. There is no type field, so all emails will be sent as primary recipient (To:) emails rather than cc: or bcc: emails.

The correct customer record is read from the file at run time by using the field CUSTNO in file CSTMST as the key. The key value used is supplied in the form of a CoolSpools variable, namely the customer number item defined on the EXITPGMPOS parameter and extracted from page 1 of the statement.

I want CoolSpools to read the email address(es) from my customer file (email address file etc.) but the key's not in the report.

One option here would be to use an exit program but, if you're familiar with SQL, there's another possibility you might want to consider too. You could use the EMAILTO(*EMAILSQL)option and the EMAILSQL command parameter.

This option allows you to specify that email addresses to which the email will be sent should be read at run time by executing an SQL statement. The SQL statement might do a join between, say and invoice file and the customer file, in order to retrieve the email address(es) to use based on an invoice number extracted from the spooled file.

The key values can be CoolSpools variables. For example, you might extract a piece of data from the spooled file such as a customer number at run time and use that as a variable in the SQL statement.

The SQL statement must return between 1 and 3 values. The first value returned will be interpreted as the email address. The second value returned, if any, will be interpreted as the recipient’s name. The third value returned, if any, will be interpreted as the recipient type and must be one of: *PRI (primary to: recipient), *CC (cc: recipient) or *BCC (bcc: recipient). Each of the fields returned must be either fixed or variable length character or graphic data.

Although it is possible to code CoolSpools variables in the SQL statement itself and carry out tests against items of data extracted from the spooled file in that way, that technique is not recommended, as it requires the SQL statement to be prepared every time it is run. It is more efficient to code parameter markers in the SQL in the form of ? placeholders and then specify the values to be substituted for those placeholders at run time on the "Variable values or names" element, as this allows the SQL statement to be prepared just once.

Example:

CVTSPLPDF ... 

EMAIL(*YES)

EMAILTO(*EMAILSQL) 

EMAILSQL(‘select EMAIL, CUSNAM from INVMST A, CSTMST B

where A.INVNO = ? AND A.CUSTNO = B.CUSTNO’ ‘<:CUST_NBR:>')

SPLIT(*POS)

SPLITPOS((5 9 7))

EXITPGMPRM(*POS)

EXITPGMPOS((1 5 9 7 CUST_NBR))

Here, the spooled file contains a batch of customer account statements, and each separate statement needs to be emailed to the appropriate customer, which is different in each case.

The spooled file contains the invoice number on line 5, position 9 for 7 characters, and a new PDF is started every time this value changes. The same value is also extracted from page 1 of the report at run time by means of the EXITPGMPOS parameter, and given the user-defined name INV_NBR.

The EMAILTO(*EMAILSQL) option and associated EMAILSQL parameter indicate that the email address information to be used is to be retrieved at run time by executing the SQL statement specified on the EMAILSQL parameter, namely:

select EMAIL, CUSNAM from INVMST A, CSTMST B where A.INVNO = ? AND A.CUSTNO = B.CUSTNO

This SQL statement joins the invoice file to the customer file on customer number to retrieve the email address information from the customer record for the customer whose invoice is being processed. The SQL statement contains a single ? parameter marker corresponding to the value of the customer number. The second part of the EMAILSQL parameter indicates that the ? parameter marker should be replaced at run time by the value of the CUST_NBR CoolSpools variable.

Note that it would be possible to specify the SQL statement in the form:

select EMAIL, CUSNAM from INVMST A, CSTMST B

where A.INVNO = <:CUST_NBR:> AND A.CUSTNO = B.CUSTNO

rather than using a ? parameter marker, but this is not so efficient, as it requires the SQL statement to be prepared each time it is run rather than just once.

You can't work out the correct email address(es) based just on what's in the report.

Maybe there's some other logic you need to follow or some other business rule you need to implement in order to work out who the report should be sent to. For example, maybe your report is a list of overdue customer accounts, and you're splitting it up into separate customer account statements and those need to be emailed to a different person depending on how long overdue the account is. Maybe working out who to send the statement to then needs a date calculation to be done to determine how many days or months late payment is.

This kind of thing would be a job for an exit program. Using an exit program gives you total control. The exit program will be passed some basic, standard information (spooled file details, output file details etc.) and can also potentially be passed a set of information extracted from the spooled file at run time (e.g. the customer number, the due date etc.). The exit program could then carry out whatever calculations are necessary to determine the correct people to email the report to, then notify CoolSpools of those email addresses by calling an API.

See the FAQ answer on exit programs for further details of how to write an exit program and use the EXITPGMPOS and EXITPGMKEY command parameters to pick up information from inside the spooled file contents at run time and pass it to your exit program.

I only want to send the report by email to some customers. There are still a few customers who want hard copies.

There are several ways to handle this.

The simplest perhaps is just not to define any email address for those customers who do not want to receive the report by email. You can then handle the situation where no email address is available using one of the methods described below.

A neater solution would be to use an exit program. The exit program could check to see if the customer wanted the report by email or as hard copy before attempting to process the relevant section of the report. If the customer wanted an email, the report could let conversion to PDF proceed and the email be created; otherwise, if the customer wanted the report as hard copy, the exit program could run CVTSPLSPLF to create a new spooled file just containing the section of the original spooled file relating to the customer in question, which could be routed to an output queue for printing.

See the FAQ answer on exit programs for further details of how to write an exit program and use the EXITPGMPOS and EXITPGMKEY command parameters to pick up information from inside the spooled file contents at run time and pass it to your exit program. Feel free to contact ariadne support for further information on how to write an exit program to do this.

What happens if an email can't be sent?

You can tell CoolSpools what to do if an email cannot be sent by means of the environment variable CS_EMAIL_SEND_ERROR_ACTION.

To create a system-level environment variable, use the command:

ADDENVVAR ... LEVEL(*SYS):

Note that both environment variable names and values are case-sensitive.

CS_EMAIL_SEND_ERROR_ACTION can take the following values.

Note that this only determines the action taken if CoolSpools cannot create the email in the first place, e.g. because there is no email address specified or that email address is no well formed (e.g. has no @ sign in it). It does not and cannot determine what happens if the email is sent to an invalid (i.e. well formed but non-existent email address) or the email cannot be delivered after it has been created for whatever other reason. For advice on troubleshooting email problems and tracking nondelivery, see the Email Setup Guide.