Sending Emails From SQR

My first encounter with SQR was very enriching. There was an existing SQR program which was generating a report based on some business rules. The assignment was to split single report to multiple based on DEPTID and to send out those reports to the concerned Department managers. Creating multiple reports was okay, but I wasn't sure how to email those reports to the correct recipient. I had an option to create a Job, with two processes- One will be SQR that will generate the output file and another will be App Engine that will send out the email with report as an attachment.

Though it was a good idea, somehow I wasn't convinced with it. Creating a new process just to send emails- I wasn't in favor of this. After few days of research, I came up with a procedure which could send emails from SQR. Below is sqr procedure for Windows NT :

!********************************************
!Procedure to send Email through SQR
!********************************************
Begin-PROCEDURE  SEND-EMAIL

let $enter=chr(10)
let $EMAIL= 


let $BODY = 'Hi ,'||$enter||' This is a test Mail . '
let $system_command ='psmail -TO"'||$EMAIL||'" -SUBJECT"Test Mail'||'" -BODY"'||$BODY||'"'

Call system using $system_command #call_status
if #call_status    <> 0
      show ' '
      show 'Error Sending Mail'
      show ''
      show  #call_status
      show  $system_command
      show ' '
end-if

End-PROCEDURE  ! SEND-EMAIL

!********************************************
!Procedure to send Email based on role
!********************************************
Begin-PROCEDURE  SEND-EMAIL

let $enter=chr(10)
let $email_ids= ''

begin-select
Z.EMAILID 

if  $email_ids= ''
let $email_ids= &Z.EMAILID
else
let  $email_ids= $email_ids ||';' ||&Z.EMAILID
end-if

FROM  PS_ROLEUSER_VW Y, PSOPRDEFN Z
WHERE Y.ROLENAME = ''
   AND Y.ROLEUSER = Z.OPRID
   AND Z.EMAILID <> ' '
   AND Z.ACCTLOCK=0

end-select

let $EMAIL=$email_ids
let $BODY = 'Hi ,'||$enter||' This is a test Mail . '
let $system_command ='psmail -TO"'||$EMAIL||'" -SUBJECT"Test Mail'||'" -BODY"'||$BODY||'"'

Call system using $system_command #call_status
if #call_status    <> 0
      show ' '
      show 'Error Sending Mail'
      show ''
      show  #call_status
      show  $system_command
      show ' '
end-if

End-PROCEDURE  ! EMAIL-Report

!*********************************************************
!Procedure to send Email with File as an attachment
!*********************************************************
Begin-PROCEDURE EMAIL-REPORT

! $outFileName- Complete File name (with Path)
! TestFile.txt- Alias Name for output file ( will be File Name in Email attachment )         

let $BODY = 'Test Report'
let $system_command ='psmail -TO"'||
              $emailid||
              '" -SUBJECT" Test Attachment'||'" -BODY"'||$BODY||'" -FILE"'||$outFileName
             ||'" -ALIAS"TestFile_' ||'.txt'||'"'          
Call system using $system_command #call_status


 if #call_status    <> 0
      show ' '
      show 'Error Sending Mail'
      show ''
      show  #call_status
      show  $system_command
      show ' '
 end-if

End-PROCEDURE  !EMAIL-REPORT
!*********************************************************