Send IBM i Email including SQL Table Data automatically

  • Home
  • /
  • Blog
  • /
  • Send IBM i Email including SQL Table Data automatically

August 16, 2017

Have you ever wanted to extract some data out of an IBMi/AS400 file using SQL and then email that data to someone as an Excel Spreadsheet?

Now you can… all from one IBMi command without having to write any CL code to do it.

Projex4i: DSTDTA command now handles SQL STRINGS for selecting data


I added this new function to the PROJEX4i – Distribute Data command last night – so it can now take a bite of selected data using SQL and send it as a TXT, CSV, XML or PDF as EMAIL.

distribute iseries data using sql

Huge time saver for me and has been on my wish-list for ages.


 DATASQL('select * from CSP20 where CONO20 = ''??'' and PSTD20 between 1061001 and 1101022')
 SUBJECT('Here is the spreadsheet for data range 10/01/2010 thru 10/22/2010')
 IFSDOC('CSP20 Spreadsheet')

This captures the list of data selected by your SQL statement, converts that data in a spreadsheet and saves it in the IFS – then it convert the spreadsheet to the format you selected (plain TXT, PDF, XML, CSV) and emails it directly to the address you specified on the command using the IBM i SNDSMTPEMM command. The new version allows you to choose between using SNDSMTPEMM, MMAIL and SNDDST as your email delivery mechanism.

It’s a thing of beauty. Hope it helps somebody.


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

  • Nick… love your bio! Don’t know how old this blog/post is, but jic you’re still checking it…

    I need to email an IFS excel spreadsheet from the iSeries to Outlook. After days of unsuccessful
    attempts to use the “SNDDST” command (it always errored out with “Folder Not Found” message)
    I’m now trying to use the “SNDSMTPEMM” command. I guess I made some progress… now it errors
    out with “File Not Found” message. See below. So I guess it is finding the folder(s)… but not the file.
    My FolderName/FileName syntax is: ‘/CompanyName/EXCEL/IT/EXPLOTS.CSV’ with EXPLOTS.CSV
    being the file name, of course. And yes, it definitely exists in the IT folder.

    I saw a few posts in other “help” websites reporting the exact same error problem…
    but I didn’t see any explanations/resolutions.

    How is your nifty DSTSTA command different/better than the SNDSMTPEMM command…
    and do you have any ideas on why my SNDSMTPEMM command can’t find the specified file?
    Much thanx…

    Integrated File System File Not Found.
    Send E-mail Failed.
    Function check. TCP5092 unmonitored by EXCELTEST at statement 0000002101
    instruction X’0000′.
    TCP5092 received by procedure EXCELTEST. (C D I R)

    Randy Stratton

    • Firstly – *whoa* this is an old comment at I missed and
      Secondly – assuming you found the problem what was it? I always use WRKLNK ‘/CompanyName/EXCEL/IT/EXPLOTS.CSV’ to check if the IFS location is valid 😉

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Start your
    7-day free trial

    Take This Course with ALL ACCESS

    Unlock your Learning Potential with instant access to every course and all new courses as they are released.
     [ For Serious Software Developers only ]

    IBM i Training For Technology Experts


    Successfully Work Remote 

     $ 129.00  $59.00

    By adding new skills in information technology, employers will be confident that you have the necessary skills and tools needed to successfully work remotely


    Snug CBD

     20% Discount

    I have partnered with SNUG CBD givING you Organic CBD
    20% discount code "NL20"