Email Notification with Editable FROM Address

The following snippet provides a possibility to get an email notification for failed jobs using a different FROM address.

To create the sendMail procedure, please run this code:

create VIRTUAL PROCEDURE views.sendMail
(IN Recipient string, 
IN Subject string, 
IN Body string,
IN FromAddress string,
IN FromName string, 
IN AttachmentName string,
IN Attachment blob,
IN AttachmentMimeType string) 
RETURNS (result string) AS
BEGIN
SELECT * FROM OBJECTTABLE(LANGUAGE 'javascript' '
var result="success";
function getEmailSession() {
        var  context = new javax.naming.InitialContext();
        return  context.lookup("java:/mail");
}
try{
var session = getEmailSession();
var m = new javax.mail.internet.MimeMessage(session);         
            var to = [
                    new javax.mail.internet.InternetAddress(Recipient)];
            m.setRecipients(javax.mail.internet.MimeMessage.RecipientType.TO, to);
            m.setSubject(Subject);
            m.setFrom(new javax.mail.internet.InternetAddress(FromAddress, FromName));
            m.setSentDate(new java.util.Date());
            if (Attachment==null) {
              m.setText(Body, "utf-8", "html");
            } else
            {           
              if (AttachmentName==null||AttachmentMimeType==null) {
                      throw "If Attachment<>null, then AttachmentName and AttachmentMimeType cannot be null";
              }
              var  messagePart = new javax.mail.internet.MimeBodyPart();
              messagePart.setText(Body);
                           //
              // Set the email attachment file
              //
              var iStream=Attachment.getBinaryStream();
              var byteDataSource = new javax.mail.util.ByteArrayDataSource(iStream,AttachmentMimeType);

              var attachmentPart = new javax.mail.internet.MimeBodyPart();
              attachmentPart.setDataHandler(new javax.activation.DataHandler(byteDataSource));
              attachmentPart.setFileName(AttachmentName);

              //
              // Create Multipart E-Mail.
              //
              var multipart = new javax.mail.internet.MimeMultipart();
              multipart.addBodyPart(messagePart);
              multipart.addBodyPart(attachmentPart);

              m.setContent(multipart);
             }
            javax.mail.Transport.send(m);
}
catch(err) {
//err.javaException.printStackTrace();
result=err;
}
finally
{
if(iStream!=null) iStream.close();
}
result' 
PASSING Recipient AS Recipient, 
Subject AS Subject, 
Body AS Body,
FromAddress AS FromAddress,
FromName AS FromName,
AttachmentName as AttachmentName,Attachment as Attachment, AttachmentMimeType as AttachmentMimeType COLUMNS result string 'result') AS o;
END;;

To send this, you can use the following snippet:

BEGIN 
DECLARE STRING job_errors;
job_errors = (SELECT cast(to_chars(textagg("description"),'UTF-8')AS string) FROM "SYSADMIN.ScheduleJobs" AS a WHERE "LastExecutionStatus" <> 'SUCCESS');
CALL "views.sendMail"(
    "Recipient" => 'string_Recipient',
    "Subject" => 'string_Subject',
    "Body" => job_errors,
    "FromAddress" => 'string_FromAddress',
    "FromName" => 'string_FromName'
);
END;