The request... send emails using data driven email recipient specifications. The implementation is 100% SQLcandoit.

Data driven emails from IBM i.sql

--
-- Subject: Data driven emails from IBM i
-- Author: Scott Forstie  
-- Date  : May, 2024
-- Features Used : This Gist uses SQL PL, listagg, SYSTOOLS.GENERATE_SPREADSHEET, SYSTOOLS.SEND_EMAIL, and database know-how
--
-- Now that it's simple to generate spreadsheets and send emails from the IBM i, the request was to 
-- send emails and NOT have the recipient(s) of the email hard-coded.
--
-- One solution is found below. Store the email recipients within a Db2 for i table and 
-- use the LISTAGG built-in function to build the email TO, CC, and BCC lists.
--
-- #SQLcandoit
--
-- https://www.ibm.com/docs/en/i/7.5?topic=services-generate-spreadsheet-scalar-function

--
-- Create an SQL table that contains who should be emailed, by team name
--
create table coolstuff.email 
(team varchar(50), email_address for column emailaddr varchar(100));

--
-- The following table could contain more designations for email delivery.
-- You choose how complex and elaborate the data model becomes.
--
insert into coolstuff.email values('Sec_team', 'forstie@us.ibm.com');
insert into coolstuff.email values('Sec_team', 'timmr@us.ibm.com');
select * from coolstuff.email;
stop;

--
-- Test how LISTAGG can transform multiple column values into a single, comma separated list
--
select
  listagg(
    cast(email_address as clob(1m)), ', ') within group (order by email_address)
    as EMAIL_LIST
  from coolstuff.email
  where team = 'Sec_team';
stop;  

--
-- IBM Security configuration (for purposes of demonstration)
--
select * from qsys2.security_info;
stop;

-- One time setup. 
-- Note that user in the following line is the USER SQL special register that returns
-- the value of the current user of the connection/job.
-- 
-- call qsys2.qcmdexc('QSYS/ADDUSRSMTP  USRPRF(' concat user concat ')');
-- 

--
-- Built a Spreadsheet that resides within Scott's home in the IFS
--
VALUES SYSTOOLS.GENERATE_SPREADSHEET(
  PATH_NAME         => '/home/scottf/sec_info',        
  SPREADSHEET_QUERY => 'select * from qsys2.security_info',    
  SPREADSHEET_TYPE  => 'xlsx',    
  COLUMN_HEADINGS   => 'NONE'   
);
stop;

--
-- Is the spreadsheet created?
--
select PATH_NAME, OBJECT_TYPE, OBJECT_CHANGE_TIMESTAMP
  from table (
      qsys2.ifs_object_statistics('/home/scottf/', SUBTREE_DIRECTORIES => 'YES')
    );
stop;

--
-- What is the name of the current IBM i?
--
select host_name from sysibmadm.env_sys_info;
stop;
          
--
-- Send the email, with an attachment, 
-- but use a Db2 file (coolstuff.email) for the email addresses
--
values SYSTOOLS.SEND_EMAIL(
  TO_EMAIL => (select
        listagg(cast(email_address as clob(1m)), ', ') 
        within group (order by email_address)
          as EMAIL_LIST
        from coolstuff.email
        where team = 'Sec_team'), 
  SUBJECT => 'Security config on IBM i: ' concat (select host_name
          from sysibmadm.env_sys_info), 
  BODY => 'Security config captured on ' concat
      current timestamp, 
  ATTACHMENT => '/home/scottf/sec_info.xlsx');
stop;