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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

DECLARE l_body_html clob; l_body clob; BEGIN 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'; apex_mail.send( 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' ); apex_mail.push_queue; END;

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.

Note:

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:

1 2 3 4

SELECT * 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.

Annotation

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 it...here is the link for the demo app.

Quellen:

• https://bit.ly/3CSrTQR
• https://bit.ly/3InoPja