Configuring email in Oracle APEX

Reason for wrote this post:

    From Mylearn Workspace we can easily send mails without any obstacles. But in our OCI free tier account we need to do some initial setup for sending email from APEX workspace or DB. Else it will be in mail queue itself.

What you will get:

    Basic idea of email triggering from DB. How to configure default sender email and send test email from OCI free tier APEX workspace.

Steps involved:

    1. Create user and generate SMTP credentials

    2. Getting Public end point URL

    3. Updating Instance settings in Administration

    4. Validating the configuration

• Create user and generate SMTP credentials

In left navigation menu go to the Identity & Security tab navigate to Domain section.

Under Default domain go to Users tab and Create User with required details (we can go with default user also, for the testing purpose I created new user and used it)

Once the user is created then click that user name and scroll down to resource section

Find the SMTP credentials section and click Generate credentials with valid description

    Note: The credentials will be shown only once for security reasons. Please copy the username and password and store them in a secure place. If you lose the credentials, nothing will happen, we can create new ones ;)

• Getting Public end point URL

Go to left side menu Developer Services / Application Integration (Email Delivery)

Under Email Delivery screen navigate to the Configuration tab. Copy the Public Endpoint URL of SMTP Sending Information

• Updating Instance settings in Administration

After getting the SMTP credential and Public Endpoint URL, we can go to the INTERNAL workspace and update the Instance settings for the email configurations.

Manage Instance / Instance settings

Update the Instance Settings / Email section as per the First two steps

    SMTP Host Address: Public Endpoint (SMTP Sending Information)

    SMTP Host Port: 587 or 25

    SMTP Authentication Username: Generated credential Username

    SMTP Password: Generated credential password

    TLS: After connection is established

    Default Email From Address: Email address which is used for generating the credentials.

• Validating the configuration

Once all the setup is done, we can validate the changes using APEX_INSTANCE_ADMIN.VALIDATE_EMAIL_CONFIG procedure

Open SQL worksheet and run the below snippet to validate the configuration

    BEGIN
        APEX_INSTANCE_ADMIN.VALIDATE_EMAIL_CONFIG;
    END;

If you followed each step the code will be successfully executed. Else it will though error message which is causing the issue.

If you are facing any issues try to redo from step 1

Test email triggering from APEX

BEGIN
    
    apex_mail.send(  p_from => Default Email From Address, 
                                p_to      => To email address, 
                                p_subj => 'Test OCI email trigger',
                                p_body => 'Hello, world!');
                    
    apex_mail.push_queue();

END;
/

Mostly the sent email will be in Archived folder or in Spam section.
Use the below queries to view the email queue and logs
    
    select * from APEX_MAIL_QUEUE;
    select * from APEX_MAIL_LOG;


Mail queue is used for view the pending mails to be send. We can verify the details of the mail if it is not sent.

Note: This is only suitable for the OCI region email provider service. If we use any third party email domain we need to do some more changes in the steps.

Thank you!