Start of change

SEND_EMAIL scalar function

The SEND_EMAIL scalar function sends an email to a one or more recipients. This is done by using the SNDSMTPEMM (Send SMTP E-mail Message) CL command.

This function assumes that the user invoking this function has been registered to the SMTP server by using the ADDUSRSMTP (Add User SMTP) CL command. If the SMTP server is not active, this function will attempt to start it before sending the email.

Authorization: See Note below.

Read syntax diagramSkip visual syntax diagram SEND_EMAIL ( TO_EMAIL =>  to-email ,SUBJECT => subject,BODY => body,ATTACHMENT => attachment,CC_EMAIL => cc-email,BCC_EMAIL => bcc-email )
The schema is SYSTOOLS.
to-email
A character string containing one Start of changeor moreEnd of change email addresses. Start of changeAddresses must be separated by a comma. Blanks included before or after each comma are ignored.End of change
The total number of email addresses provided with the to-email, cc-email, and bcc-email parameters cannot exceed 20.
subject
A character string up to 255 characters long containing the subject of the email.
body
A character string up to 5000 characters long containing the body of the email.
attachment
A character string containing Start of changeup to 10End of change path names of integrated file system files to be sent as attachments to the email. Start of changeEach absolute path name can be up to 200 characters long. Path names must be separated by a comma. Blanks included before or after each comma are ignored. End of change
If this parameter is omitted, no attachment is sent with the email.
Start of changecc-emailEnd of change
Start of changeA character string containing one or more email addresses to be included in the carbon copy list. Addresses must be separated by a comma. Blanks included before or after each comma are ignored.End of change
Start of changeThe total number of email addresses provided with the to-email, cc-email, and bcc-email parameters cannot exceed 20.End of change
Start of changebcc-emailEnd of change
Start of changeA character string containing one or more email addresses to be included in the blind carbon copy list. Addresses must be separated by a comma. Blanks included before or after each comma are ignored.End of change
Start of changeThe total number of email addresses provided with the to-email, cc-email, and bcc-email parameters cannot exceed 20.End of change
The result of the function is an integer. If the command is successful, the function returns a value of 1. If the command returns an error, the function returns a value of -1.

Note

This function is provided in the SYSTOOLS schema as an example of how send an email using the SNDSMTPEMM CL command in an SQL scalar function. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Examples

  • Send an email to a user with an attachment.
     VALUES SYSTOOLS.SEND_EMAIL(TO_EMAIL   => 'someuser@gmail.com', 
                            SUBJECT    => 'Status for last week', 
                            BODY       => 'Attached is the status information for last week.', 
                            ATTACHMENT => '/home/myuser/status.log');
  • Send an email to multiple users with 2 attachments.
     VALUES SYSTOOLS.SEND_EMAIL(TO_EMAIL   => 'someuser@gmail.com, mymanager@xyz.com',
                            CC_EMAIL   => 'anotheruser@gmail.com',
                            SUBJECT    => 'Status and future plans', 
                            BODY       => 'Attached is the status for last week and the future plan.', 
                            ATTACHMENT => '/home/myuser/status.log, /home/myuser/plan.docx');
End of change