How Do I Programatically Send Emails Using APEX_MAIL

Introduction
Sending emails programmatically using the APEX_MAIL package in Oracle APEX provides developers with a powerful tool for automating communication directly from their applications. Whether you're sending password resets, user notifications, or workflow alerts, APEX_MAIL allows you to compose and queue messages efficiently using PL/SQL. With just a few lines of code, you can trigger email events in response to user actions or background processes.

 

To programmatically send emails in Oracle APEX, you use the APEX_MAIL package, which provides a simple interface for composing, queuing, and sending email messages directly from PL/SQL. This is particularly useful for sending automatic notifications, confirmations, alerts, or custom messages in response to application events or background jobs.

Here is a detailed guide on how to send emails using APEX_MAIL:

1. Prerequisites and Setup
Before using APEX_MAIL, make sure the following are properly configured:

• SMTP settings are defined in your APEX instance under Manage Instance > Instance Settings > Email.

• The SMTP server, port, username, password, and encryption (SSL/TLS) must be valid.

• The sender email address must match what is allowed by your SMTP provider (e.g., Oracle Cloud, Gmail, Office 365).

2. Basic Syntax of APEX_MAIL.SEND

The APEX_MAIL.SEND procedure is used to compose and queue an email:

BEGIN
  APEX_MAIL.SEND(
    p_to       => 'recipient@example.com',
    p_from     => 'sender@example.com',
    p_subj     => 'Your Subject Here',
    p_body     => 'This is a plain text message body.',
    p_body_html => '<p>This is an <strong>HTML-formatted</strong> message.</p>'
  );

  APEX_MAIL.PUSH_QUEUE;
END;

• p_to: Email address of the recipient (required).

• p_from: Email address of the sender (required and must match SMTP-approved sender).

• p_subj: Subject line of the email.

• p_body: Plain text content.

• p_body_html: Optional HTML content. If this is provided, email clients will render it as a rich email.

• APEX_MAIL.PUSH_QUEUE: Pushes the queued email immediately. If not called, the message may be delayed until the next scheduled mail process.

3. Sending to Multiple Recipients

You can send to multiple recipients by separating email addresses with a comma:

p_to => 'user1@example.com,user2@example.com'

4. Using CC and BCC

To add CC or BCC recipients, use APEX_MAIL.SEND overloaded parameters:

BEGIN
  APEX_MAIL.SEND(
    p_to       => 'to@example.com',
    p_from     => 'from@example.com',
    p_subj     => 'Subject',
    p_body     => 'Plain text body',
    p_cc       => 'cc@example.com',
    p_bcc      => 'bcc@example.com'
  );

  APEX_MAIL.PUSH_QUEUE;
END;

5. Adding Attachments

To add an attachment, use the APEX_MAIL.ADD_ATTACHMENT procedure after sending the mail and before pushing the queue:

DECLARE
  l_mail_id NUMBER;
BEGIN
  l_mail_id := APEX_MAIL.SEND(
    p_to       => 'user@example.com',
    p_from     => 'sender@example.com',
    p_subj     => 'File attached',
    p_body     => 'Here is the file.'
  );

  APEX_MAIL.ADD_ATTACHMENT(
    p_mail_id    => l_mail_id,
    p_attachment => UTL_RAW.CAST_TO_RAW('This is file content'),
    p_filename   => 'example.txt',
    p_mime_type  => 'text/plain'
  );

  APEX_MAIL.PUSH_QUEUE;
END;

6. Checking Delivery and Logs

You can monitor delivery using the following views:

• APEX_MAIL_LOG: Shows email logs and errors

• APEX_MAIL_QUEUE: Lists pending messages

• APEX_MAIL_ATTACHMENTS: Shows details of attachments

7. Error Handling

Always include exception handling for better traceability:

BEGIN
  APEX_MAIL.SEND(...);
  APEX_MAIL.PUSH_QUEUE;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error sending email: ' || SQLERRM);
END;

8. Best Practices

• Always validate email addresses before calling APEX_MAIL.SEND.

• Avoid sending large numbers of emails in loops without throttling or batching.

• For high-volume email systems, consider scheduling jobs and monitoring the mail queue.

• Secure sensitive content and avoid sending confidential information unencrypted.

By using the APEX_MAIL package, you can integrate reliable and flexible email functionality into your Oracle APEX applications. Whether it's a single alert or a scheduled newsletter, this tool ensures that messages are properly composed, queued, and delivered.

Example: Sending a Simple Email

BEGIN

    APEX_MAIL.SEND (

        p_to    => 'recipient@example.com',

        p_from  => 'your_email@gmail.com',

        p_subj  => 'Test Email',

        p_body  => 'This is a test email from Oracle APEX.'

    );

    COMMIT;

END;

/

Example: Sending an HTML Email

BEGIN

    APEX_MAIL.SEND (

        p_to        => 'recipient@example.com',

        p_from      => 'your_email@yahoo.com',

        p_subj      => 'HTML Email from APEX',

        p_body_html => '<h1>Hello</h1><p>This is an email with HTML formatting.</p>'

    );

    COMMIT;

END;

/

Manually Processing the Email Queue

Emails are stored in the APEX mail queue before being sent. To process the queue immediately:

BEGIN

    APEX_MAIL.PUSH_QUEUE;

END;

/

Conclusion
The APEX_MAIL package is an essential part of any Oracle APEX developer’s toolkit, enabling dynamic and automated email communication. By understanding how to construct messages and manage the mail queue, you can integrate reliable messaging into your applications. Combined with proper SMTP configuration, APEX_MAIL ensures your APEX apps can reach users with timely and relevant information.