Monday, January 24, 2022

Including a link in an email to an APEX App (APEX Mail #8)


As already mentioned, here you will find everything you need to know about sending emails with Oracle APEX. Let's continue with topic number eight.

#8 Including a link in an email to an APEX App

In this blog post, we would like to show you how to include a hyperlink that redirects you to an APEX application in an email. This can be a link text, button, or whatever you like. Just make sure that your expected email clients support your solution.

Let´s start by creating a demo app

Create a new application in the App Builder. In the wizard, you can name your application "Email Demo" for example. Our application is now ready to add more components. The home page has no regions at the moment.

Now our application is ready to add a region, some components, and a process to send emails.

First, add a "Static Content" region to your "Content Body" and name it for example "Send Email".
Then we need to have the following items:
  • P1_TO as Text Field (This will have the address of the email recipient)
  • P1_CONTACT as Text Field (This will have the name of the recipient)
Finally, the page needs a button to send an email. So add a button "Text and Icon (Hot)" to the "Close" position. Name the button "SEND" and use e.g. "fa-send" as the icon and "Send email" as the label.

Create a process to send the email

Next, we need a process to send an email when we hit the "SEND" button.

Therefore, switch to Processes in the tree structure and create a new process. For example, name it "Send mail" and select the type "Execute Code". 

Under Source, enter the following PL/SQL Code:

    l_body_html clob;
    l_body      clob;
    l_body := 'Hello '|| :P1_CONTACT || ','||UTL_TCP.crlf;
    l_body := l_body || 'this is an email sent from Oracle APEX.'||UTL_TCP.crlf;
    l_body := l_body || 'Please click the following link to view your email log.'||UTL_TCP.crlf;
    l_body := l_body || apex_mail.get_instance_url || APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:81:::::P81_MAIL:&P80_TO.') ||UTL_TCP.crlf;
    l_body := l_body || 'Best Regards';
    l_body_html := '<p>Hello '|| :P1_CONTACT || ',</p>'
                || 'this is an email sent from <strong>Oracle APEX</strong>.<br />'
                || 'Please click the button to view your email log.<br />'
                || '<table border="0" cellspacing="0" cellpadding="0">'
                || '    <tr>'
                || '        <td>&nbsp;</td>'
                || '    </tr>'
                || '    <tr>'
                || '        <td style="padding: 12px 18px 12px 18px; border-radius:10px; background-color: #056ac8;" align="center">'
                || '            <a href="' || apex_mail.get_instance_url || apex_page.get_url(p_page => 2, p_items => 'P2_MAIL',p_values => :P1_TO) || '" target="_blank" style="font-size: 15px; font-family: Helvetica, Arial, sans-serif; font-weight: bold; color: #ffffff; text-decoration: none; display: inline-block;">Click here</a>'
                || '        </td>      '
                || '    </tr>'
                || '    <tr>'
                || '        <td>&nbsp;</td>'
                || '    </tr>'
                || '</table>'
                || 'Best Regards';
        p_to        => :P1_TO,
        p_from      => :APP_EMAIL,
        p_body      => l_body,
        p_body_html => l_body_html,
        p_subj      => 'Including a link in an email to an APEX App'

Finally, enter the success and error message and the server-side condition when the process should be executed.

Success Message: Email sent
Error Message: Email sent failed!
When Button Pressed: SEND

In the PL/SQL code the function "apex_mail.get_instance" is executed which returns the URL for our APEX instance. The function "apex_page.get_url" returns an APEX navigation and builds with the previous function our whole URL.

Instead of using the "get_instance" function, you could also specify the URL directly in the "href" attribute. However, the "get_instance" function always returns the URL of the instance from which it has been executed. A direct URL input would be static and would have to be adjusted again and again. For example, if you have multiple instances (e.g. Dev, Test, and Production), using "get_instance" the URL will be dynamically generated and doesn't need to be changed for each instance in your code.

As you can see in the URL, we refer to page 2 and enter a parameter that we want to use there as a filter. In this example, we want to show the email log of the recipient on page 2. To make this possible we still need a new page, so let's create it quickly

Create a new page to display the mail log

So, click on "Create page" in the APEX-Builder and select "Report" and "Interactive Report" as page type. Set the page number to "2" and name it "Mail Log" for example. Navigation is not required, so don't create a new entry here. Finally, change the source type to "SQL Query" and enter the following SQL statement:

  FROM apex_mail_log 
 WHERE mail_to = :P2_MAIL
ORDER BY last_updated_on DESC;
Note: If you want to know more information about mail logs or queues you can read the previous blog.

Since we are passing a parameter in the URL, we still need a page item for that. Therefore, create a hidden page item on the new page and name it "P2_MAIL". 

Finally, make sure that "Deep Linking" is enabled, otherwise you will not be redirected to this page if you don't have a session.

So there you have it. Let's try to send an email by clicking on the "Send email" button.

And this is what the result of the email should look like.

If you click on the link (button) now, your APEX application will be opened and the log will be filtered according to the email address.


In this blog post, only the PL/SQL API was used for sending. Of course, you can also send in the same way using email templates. In this case, the URL would have to be set in a page item and implemented in the email template via a placeholder.

So, that's is the link for the demo app.

Labels: ,


At January 24, 2022 at 3:35 PM , Blogger Sorin Apalaghie said...

A nice addition to your blog post would be to include a reference on how to use the p_plain_url parameter of the get_url function when the email is sent from within a modal dialogue page.

At January 24, 2022 at 6:10 PM , Blogger Timo said...

Thanks for the comment. The focus of this blog is more on sending emails. In further blog posts we want to take a closer look at the APIs.

At January 25, 2022 at 8:46 AM , Anonymous Anonymous said...

Very interesting - I have a issue with the url, for example:

apex_mail.get_instance_url || apex_util.prepare_url('f?p=123:456:::::P456_HASH:ABC')

The output is

apex_mail.get_instance_url returns "https://myDomain/ords/"
apex_util.prepare_url returns "/ords/vplims/r/123/456?p456_hash=ABC"

Any idea?

At January 25, 2022 at 12:19 PM , Blogger Timo said...

Please disable friendly url. I think for friendly URLs there is still a bug :(

At February 7, 2022 at 5:33 PM , Blogger Boubacar said...

Très intéressant , maintenant si je veux utiliser mon domaine ( serveur) comment m'y prendre ?

At August 23, 2022 at 9:51 PM , Blogger Jeison said...

How can I take the session of a user, when I enter from the link in the email? This is to keep the user's previous session and not lose the work done previously.


Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to Post Comments [Atom]

<< Home