the CL command SNDSMTPEMM

[skwon9642]

Hello all,

I am trying to send out an email from as400 with a Excel file attachment. I have my sqlrpgle code doing the creating of the file in qtemp. I am using the SNDSMTPEMM command on my CLLE program like so.
when I debug, it gets an error on the ATTACH line. The error is just "send email failed", no other details.

SNDSMTPEMM RCP((&EMAIL)) SUBJECT(&SUBJECT1) +      
           ATTACH(('/QTEMP/EDIPF.xls' *EXCEL *BIN))

this is me trying the F4 method and putting in the required fields (without a forward slash in the beginning)...still the same error

SNDSMTPEMM RCP((&EMAIL)) SUBJECT(&SUBJECT1) +       
             ATTACH(('QTEMP/EDIPF.xls' *EXCEL *BIN))

I searched online for other syntax, and they all error the same way. Any help would be appreciated. Thank you!

[JonBoy]

What do the lower level messages say about the failure? Look in the log - there should be more than just 'send email failed'.

Your choice of a directory name is also a bit weird - is it really called QTEMP? That is normally reserved for the job specific library QTEMP - not an IFS directory. Did you create that directory? If so is it within your home directory path?

[skwon9642]

looked at the job log. it says
User ********* not enrolled in System Distribution Directory.
Send E-mail Failed.

so it seems like the username I am using can't send emails out...which is odd because my boss told me this username is the only one that can send out emails...

in my sqlrpgle program, I do a query to insert into qtemp/edipf. then immediately after the query I call this CL program to email out. this is the way the psudo-code works that i received.

[JonBoy]

OK - so you know why it doesn't work. You'll have to resolve the authority issue with you system administrator.

Your last part confuses me. If your query is creating a physical file in qtemp then the path to that file is /qsys.lib/qtemp.lib/edipf.file - not qtemp/editpf.xls. Normally if one creates a physical file and sends it as an attachment it is converted to a csv via the CPYTOIMPF command first. You mention pseudocode - maybe that is truly just shorthand and you need the extra step?

Personally if I needed a csv (for excel) then I'd use an Open Access handler such as the one presented here: http://ibmsystemsmag.com/ibmi/develo...ess-templates/ so I could write directly to the IFS.

[skwon9642]

OK - so you know why it doesn't work. You'll have to resolve the authority issue with you system administrator.

Your last part confuses me. If your query is creating a physical file in qtemp then the path to that file is /qsys.lib/qtemp.lib/edipf.file - not qtemp/editpf.xls. Normally if one creates a physical file and sends it as an attachment it is converted to a csv via the CPYTOIMPF command first. You mention pseudocode - maybe that is truly just shorthand and you need the extra step?

Personally if I needed a csv (for excel) then I'd use an Open Access handler such as the one presented here: http://ibmsystemsmag.com/ibmi/develo...ess-templates/ so I could write directly to the IFS.

The CPYTOIMPF is a command I call before the SNDSMTPEMM command in the CLLE program?
not sure what would go into the TOFILE, do I just make up a new file name?

 
 CPYTOIMPF   FROMFILE(/qsys.lib/qtemp.lib/edipf.file)  TOFILE(????)             FLDDLM(' ')

[JonBoy]

You've already "made it up" in your original code i.e.
/QTEMP/EDIPF.xls - Except that I doubt that you have an IFS folder called QTEMP - I've never seen one. Database tables (which is what you appear to be creating) and IFS files are two different things. Excel cannot open a database file of the type you appear to be creating in QTEMP. In order to attach something that can be opened by Excel you need to produce a PC/Unix type stream file and that is the purpose of CPYTOIMPF which will add the column separators, and quote the alpha strings. It would really help if we could see a bit of the code that creates the file in QTEMP - we're just shooting in the dark here. Apologies if this sounds rude, but from your questions it sounds like you are a newbie to the platform. If so tell me your background and maybe I can "translate" better for you.

[RDKells]

JonBoy has pretty much covered it, I used this recently so here is how I did it;

First you need an ADDDIRE/WRKDIRE entry to send e-mails, per previous advice

Then you need a file with data in, i.e; RAREPUSRW2
Next you need a location on the IFS to copy this file to, earlier in the program I build an IFS location/File Name (&IFSP), i.e; \reports\userreports\inactiveusers201708.csv
Copy the iSeries file to the IFS;

CPYTOIMPF  FROMFILE(RAREPUSRW2) +              
           TOSTMF(&IFSP) +                    
           MBROPT(*REPLACE) FROMCCSID(285) +  
           STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
           RMVBLANK(*BOTH)

I then build a subject and e-mail the file;

SNDSMTPEMM RCP((&EMAIL1)) SUBJECT(&SUBJ) ATTACH((&IFSP +
             *PLAIN *BIN))

If the e-mail address is blank it will fail.
My report can be sent to 5 e-mail addresses, so I initially had it as &EMAIL1&EMAIL2 &EMAIL3 etc. but if &EMAIL1 was the only one in use and the other 4 were blank it failed to send because of the blank addresses.

Hope this helps.

[skwon9642]

You've already "made it up" in your original code i.e.
/QTEMP/EDIPF.xls - Except that I doubt that you have an IFS folder called QTEMP - I've never seen one. Database tables (which is what you appear to be creating) and IFS files are two different things. Excel cannot open a database file of the type you appear to be creating in QTEMP. In order to attach something that can be opened by Excel you need to produce a PC/Unix type stream file and that is the purpose of CPYTOIMPF which will add the column separators, and quote the alpha strings. It would really help if we could see a bit of the code that creates the file in QTEMP - we're just shooting in the dark here. Apologies if this sounds rude, but from your questions it sounds like you are a newbie to the platform. If so tell me your background and maybe I can "translate" better for you.

yea, no offense taken, I am a complete noob. I was just dropped into the world of as400 and it's very hard to learn some of the concepts, trying to learn on the job. Fresh out of college, majored in Comp Sci, learning various languages and concepts, but RPG/as400 is different...everyone at work warned me there is a big learning curve. It's been about 1.5 months since I started learning. I admit I wasn't the most talented programmer in school =/

creating table

Exec SQL Create Table QTEMP/EDIPF          
    (EMDID Char(10) NOT NULL WITH DEFAULT,
     EMBOX Char(10) NOT NULL WITH DEFAULT,
     MBCODE Char(4) NOT NULL WITH DEFAULT,
     EMDAT Char(8)  NOT NULL WITH DEFAULT);

inserting into table

INSERT INTO QTEMP/EDIPF                                          
SELECT EMDID, EMBOX, EDMTAB.MBCODE, EMDAT                        
FROM EDI4XXDTA/EDMAST as EDMAST                                  
INNER JOIN ZCC AS ZCC                                            
ON EDMAST.EMDID = CAST(ZCC.CCDESC AS CHAR(3))                    
INNER JOIN EDI4XXDTA/EDMTAB AS EDMTAB                            
ON EDMAST.EMSTS = EDMTAB.MBMSTS                                  
WHERE CAST(ZCC.CCSDSC AS Char(2)) = :WrkRank AND                
      ZCC.CCTABL = 'DOCCONTB'                                    
      AND EDMTAB.MBCODE NOT IN                                  
      (SELECT CAST(A.CCSDSC AS CHAR(3)) FROM ZCC A WHERE A.CCTABL
      = 'STSCONTB')                                              
GROUP BY EMDID, EMBOX, EDMTAB.MBCODE, EMDAT                      
ORDER BY EMDID, EMBOX, EDMTAB.MBCODE, EMDAT;

sending to CL file to email out

Callp EDIMONITCL(SavDocLst:WrkRank);

//SavDocLst is the subject, Wrkrank is the content of the email

CL FILE

SNDSMTPEMM RCP((&EMAIL)) SUBJECT(&SUBJECT1) +          
             ATTACH(('/qsys.lib/qtemp.lib/edipf.file' +
             *EXCEL *BIN))

I am leaving out a lot of detail of the code for the purposes of simplifying. I guess what matters is that the table is created and records are being inserted successfully. I can copy the qtemp/edipf into another library and export a excel file out from it manually. The ideal program is one that does this process automatically, emailing the given email addresses.

[skwon9642]

This is the (almost real) "psudocode" of the CL program they gave me for reference. They also gave me a psudocode for the rpgle program, but it is super long.
I believe the EMAIL command here is a custom command for this particular client, different from the one who assigned the current task. That is why I resorted to using SNDSMTPEMM, and didn't know you had to create the ADDDIRE/WRKDIRE. It also uses the file directly from qtemp...so I assumed that was how it was done.

[B]PGM        PARM(&SUBJECT &RANK)[/B]
[B]DCL        VAR(&EMAIL) TYPE(*CHAR) LEN(35) VALUE(' ')  [/B]
[B]DCL        VAR(&LIB) TYPE(*CHAR) LEN(10) VALUE(' ')  [/B]
[B]DCL        VAR(&SUBJECT) TYPE(*CHAR) LEN(100) VALUE(' ') [/B]
[B]DCL        VAR(&RANK) TYPE(*DEC) LEN(2 0) [/B]

[B]/* EMAIL THE WORK FILE THAT WAS CREATED                              */[/B]
[B]/* EMAIL ADDRESS DETERMINE BY TEST/PROD LIBRARY */                     [/B]

[B]  RTVJOBA    CURLIB(&LIB)                                             [/B]
[B]  IF         COND(&LIB = '********') THEN(DO)                           [/B]
[B]  CHGVAR     VAR(&EMAIL) VALUE('********@***.COM')     [/B]
[B]  ENDDO                                                               [/B]
[B]  ELSE       CMD(DO)                                                  [/B]
[B]  CHGVAR     VAR(&EMAIL) VALUE('********@*****.com')           [/B]
[B]  ENDDO[/B]

[B]CHGHVAR (&SUBJECT1) Value(?EDI Monitoring Status:? *TCAT &SUBJECT) [/B]
[B]EMAIL        SUBJECT(&SUBJECT1) +  [/B]
[B]           TO(&EMAIL) +                                  [/B]
[B]           FILE(QTEMP/FILENAME)  [/B]
[B]ENDPGM                [/B]

[john.sev99]

Your code shows you are creating a database file (a physical file) in QTEMP. That is a file with columns of x length of y type. This is not a xls file and it's not something excel can natively read. Your original code however had you trying to send EDIPF.xls. How was this created?
I can't comment on the EMAIL command. It could do anything with your input file. It might create a xls file from a physical file and email that. It might create a csv file and email that. It's not something we can advise on as it's not a native command.

[skwon9642]

JonBoy has pretty much covered it, I used this recently so here is how I did it;

First you need an ADDDIRE/WRKDIRE entry to send e-mails, per previous advice

Then you need a file with data in, i.e; RAREPUSRW2
Next you need a location on the IFS to copy this file to, earlier in the program I build an IFS location/File Name (&IFSP), i.e; \reports\userreports\inactiveusers201708.csv
Copy the iSeries file to the IFS;

CPYTOIMPF FROMFILE(RAREPUSRW2) +
TOSTMF(&IFSP) +
MBROPT(*REPLACE) FROMCCSID(285) +
STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
RMVBLANK(*BOTH)

I then build a subject and e-mail the file;

SNDSMTPEMM RCP((&EMAIL1)) SUBJECT(&SUBJ) ATTACH((&IFSP +
*PLAIN *BIN))

If the e-mail address is blank it will fail.
My report can be sent to 5 e-mail addresses, so I initially had it as &EMAIL1&EMAIL2 &EMAIL3 etc. but if &EMAIL1 was the only one in use and the other 4 were blank it failed to send because of the blank addresses.

Hope this helps.

when you say you built an IFS location/File name, how did you do that? and for my case, it would be /qsys.lib/qtemp.lib/edipf.file?

[skwon9642]

just found this document which basically says you can't transfer data from QTEMP...which is what I am doing. But this is also dated as 2013

http://www-01.ibm.com/support/docview.wss?uid=nas8N1013456

thoughts?

[JonBoy]

I see nothing in what you have said to-date that implies that you are using the products involved. Neither of them would usually be involved in a send mail operation. They are tools used (mostly by programmers) to download files directly to a PC.

As john.sev99 said - the EMAIL command is not a standard IBM i command - so unless you can point us to the documentation for it we can't help much more with that aspect. The implication of the way the command as shown is structured is that it will automagically convert the file to csv or whatever before sending it.

One other thought - you do know that QTEMP is not a single library right? Rather it is a name given to a job specific temporary library. So if, for example, in your interactive job you were to create a table in QTEMP and then attempt to email it by submitting the email task to batch it would not work as the QTEMP library for the batch job is different. By the way, that's why there is the restriction mentioned in the document you referenced. The Client Access stuff is a server job and therefore has its own QTEMP - it could not access yours. Also of course QTEMP goes away every time you sign off - or indeed if you have multiple sessions open on different green screens each one will have a unique QTEMP.

[skwon9642]

I see nothing in what you have said to-date that implies that you are using the products involved. Neither of them would usually be involved in a send mail operation. They are tools used (mostly by programmers) to download files directly to a PC.

As john.sev99 said - the EMAIL command is not a standard IBM i command - so unless you can point us to the documentation for it we can't help much more with that aspect. The implication of the way the command as shown is structured is that it will automagically convert the file to csv or whatever before sending it.

One other thought - you do know that QTEMP is not a single library right? Rather it is a name given to a job specific temporary library. So if, for example, in your interactive job you were to create a table in QTEMP and then attempt to email it by submitting the email task to batch it would not work as the QTEMP library for the batch job is different. By the way, that's why there is the restriction mentioned in the document you referenced. The Client Access stuff is a server job and therefore has its own QTEMP - it could not access yours. Also of course QTEMP goes away every time you sign off - or indeed if you have multiple sessions open on different green screens each one will have a unique QTEMP.

like you mentioned from the beginning, I found that EMAIL command is a custom command made for the client. It would seems like it "automagically convert the files to csv" before sending it out. I looked inside the program and it does just that, it uses CPYTOIMPF and then SNDDST in it among other things, to make it easy to simply just give it the subject, email address and attachment. I was given THAT as a reference, and me being a noobie thought, "oh, that is how you send stuff from as400 to PC". But I now realize that, without a custom command, I need to do CPYTOIMPF and SNDDST/SNDSMTPEMM depending on the situation.

as for the QTEMP issue...I guess this means that putting things into QTEMP and then simply attaching it with the custom "EMAIL" command is something that can only be done for that client. So you guys are saying that I need to turn this file in QTEMP into a stream file using CPYTOIMPF, putting it in a location on the IFS to copy this file to. Am I now following correctly?

EDIT: or, in my rpgle program, instead of creating in QTEMP/EDIPF, I can create in NEWLIB/EDIPF and then send, then delete contents, do away with QTEMP in the first place? Then my question would be, why would the original code author use QTEMP in the psudocode at all? maybe because that would be considered hard coding? From my limited understanding, the whole point of QTEMP is to not have to create real files that are only going to be used once for that one program...

[john.sev99]

On the subject of QTEMP.
When a job is started (by logging on, from a SBMJOB or whatever), a library called QTEMP is created for that job. It is local to that job only, so only it can see/access the contents. Every job has a QTEMP library created for it by the system and jobs can only see their own QTEMP library. When the job ends, the library is deleted. As such, it's an excellent place to put things that are temporary and that are only needed by that job and no other job. From what you are saying, I'd think that QTEMP is exactly where you should put this file.

From your original and subsequent posts:

SNDSMTPEMM RCP((&EMAIL)) SUBJECT(&SUBJECT1) +             
                  ATTACH(('/QTEMP/EDIPF.xls' *EXCEL *BIN))

You haven't said how the EDIPF.xls file was created. Being in QTEMP, this is a physical file and not an xls file. Maybe you have created a flat file with the necessary data to make it an xls file, but I would think that is unlikely. Xls files would normally be created in the /Root file system as they're not database files.

looked at the job log. it says
User ********* not enrolled in System Distribution Directory.

The SNDSMTPEMM is failing as this user needs adding to the system distribution directory. As RDKells mentioned, you will need to add that via e.g. WRKDIRE or get a system admin to do that if you are unable to.

I found that EMAIL command is a custom command made for the client. It would seems like it "automagically convert the files to csv" before sending it out. I looked inside the program and it does just that, it uses CPYTOIMPF and then SNDDST in it among other things, to make it easy to simply just give it the subject, email address and attachment.

It seems like that may be a better option to send the email as it will automatically convert a PF to a csv file for emailing which I'm guessing is what you are really trying to do. The ID sending the email though will still need to be enrolled in the system distribution directory via e.g. WRKDIRE.