How to integrate gmail and send email from Oracle APEX

Manually managing communications with vendors and customers in today's enterprise environment is a time-consuming and often inefficient process. Juggling emails, follow-ups, and notifications can quickly become overwhelming, leading to missed opportunities and delays. To streamline this critical aspect of business, it's essential to modernize your communication system. By integrating email directly into your business software, you can automate these interactions, ensuring timely and consistent communication without the manual effort.

I have try to demonstrate step by step how to integrate gamil account with application in Oracle APEX. For better understanding, you can follow the email configuration system.

Step 1: Create Access Control List (ACL). Connect your sys user and execute the below command-

DECLARE

    l_acl_path    VARCHAR2(3000);

BEGIN

    SELECT acl

    INTO l_acl_path

    FROM dba_network_acls

    WHERE host = '*'

    AND lower_port IS NULL

    AND upper_port IS NULL;

    IF dbms_network_acl_admin.check_privilege(

        acl                => l_acl_path

        , user            => 'APEX_240200' -- Specify your apex_user

        , privilege    => 'connect'

    ) IS NULL THEN

        dbms_network_acl_admin.check_privilege(

            acl                => l_acl_path

            , user            => 'APEX_240200' -- Specify your apex_user

            , privilege    => 'connect'

        );

    END IF;

EXCEPTION

    WHEN no_data_found THEN

    dbms_network_acl_admin.create_acl(

        acl                => 'SEND_MAIL.xml'

        , description => 'Email integration with Oracle APEX and send mail'

        , principle    => 'APEX_240200'

        , is_grant    => true

        , privilege    => 'connect'

    );

    dbms_network_acl_admin.assign_acl(

        acl                => 'SEND_MAIL.xml'

        , host            => '*'

    );

END;

BEGIN

    dbms_network_acl_admin.append_host_acl(

        host    => '*'

        , ace    =>  xs$ace_type(

                                 privilege_list        => xs$name_list('connect')

                                , principal_name    => 'APEX_240200' -- replace your APEX user here.

                                , principal_type    => xs_acl.ptype_db

                         )

                    );

END;

Step 2: Connect your gmail account and ensure the two step verification is enabled. Follow the below step to enable -

    i. Login your gmail account.

    ii. Click on the user icon on top right corner and then click 'Manage your google account'.

    iii. Go to the security tab from the left side navigation menu bar.

    iv. Scroll down till 'How you sign in to Google'. 

        a. Click on the '2 Step Verification'.

        b. Click on the 'Add phone number' button.

       c. Choose your country from the drop down list from the first field and then enter your phone                    number in the second field. Click next.

        d. You will get an OTP from google. Enter your phone number by providing the OTP and                             click Verify. You will get a successful message. Click OK to finish the process.

        e. Type  'app_password' in the search bar and press enter. You will redirect to a new page. Enter                 your app name here and click 'Create' button.

         f. A popup dialog box open with your app password. This is one time password. You can't show                 this password next time in readable format. So, copy it first and then press 'Done' button.

Step 3: Download SSL certificate to create wallet. Follow the bellow step-

    1. Log in your gmail account. Click on the icon immediate before the url.

     2. A popup menu open. Click on 'Connection is secure' and then click on 'Certificate is valid'.

     

    3. A new dialog box open. Select 'Details' tab and export all three certificate from the 'Certificate                 Hierarchy one by one.

Step 4: Create Wallet using the downloaded ssl certificate. 

    a. open cmd/cerminal and execute then below command-

        orapki wallet create -wallet 'Your wallet Directory here' -pwd 'Your wallet password' -auto_login

            Note: replace Your wallet Directory here' with your physical wallet location and 'Your wallet                  password'.

    b. Add all downloaded certificate to the wallet. Execute the below command to add-

        orapki wallet add -wallet 'Your wallet Directory here' -trusted_cert cert 'Your first downloaded                 certificate path' -pwd 'Your wallet password'.

        You need to execute three time the above command by providing the different certificate path.

    c. Check, if the certificate is added or not with the wallet. To check, execute the below command-

        orapki wallet display -wallet -pwd 'Your wallet password'.

Step 5: Instance Settings. To set the workspace instance setting, follow the step below-

    i. Login into internal workspace.

    ii. Click on "Manage Instance".

    iii. Click on "Instance Settings" under Manage Instance.

    iv. Select "Email" tab and set the below properties-

            Oracle APEX instance URL: Click on "Default URL"

            Oracle APEX Image URL   : Click on "Default URL"

            SMTP Host Address            : smtp.gmail.com

            SMTP Host Port                  : 465

            SMTP Authentication Username : "Your Gmail ID Here"

            SMTP Password                  : "Google Provided Password" Which one you get in step 2

            Confirm SMTP Password   : "Google Provided Password" Which one you get in step 2

            Use ssl/tls                            : Yes

            Default Email From Address: "Your Gmail ID Here"

    v. Click on wallet tab and set wallet path like "file:\physical directory of your wallet".

Step 6: Loging your application workspace and open the application.

    i. Create a blank page and take a region

    ii. Create 6 items and rename them as like P2_FROM, P2_TO, P2_CC, P2_BCC, P2_SUBJECT &             P2_BODY. Create 2 buttons, one for send email and another for close the page.

     iii. Create a page process to send the email. 

            apex_mail.send(
                p_to                => :P2_TO

                , p_cc              => :P2_CC

                , p_bcc            => :P2_BCC

                , p_from          => :P2_FROM

                , p_subj            => :P2_SUBJECT 

                , p_body           => :P2_BODY

            );

Note: This process is only for sending text mail. No attachment will send with this process.

Integrating email functionality into your Oracle APEX applications is a game-changer for any enterprise. The steps we've covered—from setting up email credentials to coding the PL/SQL logic—provide a robust foundation for building a more responsive and intelligent application. Embracing this integration not only streamlines your business operations but also significantly enhances the user experience, making your applications more powerful and efficient than ever before.

Happy Coding. lets enjoy!