It’s common to want to send an email via a link, and without much effort we can produce a way to do just that from within an Oracle APEX report. But what if we want the option to message all of the addresses in a report? For example, say you want to email a list of all people associated with a particular project or department — with just a single click. Let’s build a small test application to demonstrate how to make this happen.
We can use the common employee/department sample data to produce a good example. First, let’s navigate to SQL Workshop > Utilities > Quick SQL. In the left pane, paste this code:
name vc255
traust_employee /insert 12
name vc255
email vc255
This will produce two tables — department and a child table called employee — and load them with enough data to get you started quickly. Save the script, run it, and create your test application.
You should wind up 7 pages. Go to edit the page that contains your Employee report. In the left pane, expand the columns in the Employee report.
Select the EMAIL column, then in the right pane, change the type to ‘Link’. Now scroll down to the Link sub-section and open the Target. Change the Target to a URL, and use a ‘mailto:’ to create an HTML link that will open the User’s default email client. Complete the link by using a template substitution string to dynamically insert the email address that you clicked on, in this case, #EMAIL#.
Finally, add the link attribute target=“_blank” to ensure that if the User’s default email client opens in their browser, it will open in a new browser tab without disturbing their open session in your application. You should now have a working email link in your Employee report.
Now navigate to the page of the application that contains your Departments form. Right-click on the form region and select “Add a Sub-Region”. Name the new region “Employees”, and set the type as a classic report. Set the Source of the report to your employee table, and use the Where Clause to match up with the page item “:P3_ID”. Update the columns for ID and TRAUST_DEPT_ID, setting their type to “Hidden”.
This report will now show Employees associated with the Department you have opened. Using the above steps, we can turn the EMAIL column into a “mailto:” link here as well, if we want.
We now have two different reports of employees, each showing the employee email address as a convenient link. How then would we send an email to all the Employees in a Department?
The first step is understanding that a “mailto:” link can pre-populate an email client’s TO field with multiple addresses in the form of a comma delimited list. To create this we will need a hidden page item, a button, a dynamic action, and a function.
Navigate back to the Department form page in Page Designer, and on the left pane, right-click on the form region to “Add a Page Item”, then again “Add a Button”. Rename the page item to “EMAIL_LIST”, and rename the button to “EMAIL_ALL_DEPARTMENT_EMPLOYEES”. Then, in the button’s right-pane, assign the button an appropriate Position in the Layout sub-section.
Now, navigate to SQL Workshop > SQL Commands where we can produce a function the will return a comma delimited list of associated Employee email addresses when sent a Department ID. Here is the code to do that:
RETURN varchar2
IS
l_email_list varchar2 (25000);
l_email_count number;
loop_count number := 1;
cursor c1 is
select email
from traust_emp
where traust_dept_id = in_department_id;
BEGIN
for employee in c1 loop
if loop_count = 1 then
l_email_list := employee.email;
else
l_email_list := employee.email ||’, ‘|| l_email_list;
end if;
loop_count := loop_count + 1;
end loop;
return l_email_list;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
END;
Paste the above code into SQL Commands and run it to create the function. Now, navigate back to the Department form page. Right-click the EMAIL_ALL_DEPARTMENT_EMPLOYEES button, then select Create Dynamic Action. Name the new dynamic action “Email All Department Employees”, then change the True action from a Show action to a Set Value action, and the Set Type to PL/SQL Expression.
Paste the expression “getDepartmentEmails(:P3_ID)” into the code window (where P3 = your application’s Department form page number), then add P3_ID to the Items to Submit field. Finally, under Affected Elements, leave the Selection Type set to Item(s), and add the item “P3_EMAIL_LIST” to the field, and under Execution Options, switch off Fire on Initialization.
When you return to the client-side of the application, navigate to the Departments report page, and click the edit pencil on one of the Department Rows to open the Department form page. When you click the “Email All Department Employees” button, the Email List page item should be populated with the email addresses of all the Department’s associated Employees.
Back in page designer, select the EMAIL_LIST page item on the left pane, then on the right pane under Identification, set the type to “Hidden”, and switch off “Value Protected”. This will ensure that item is hidden, but will still accept the Set Value when the button is clicked.
Finally, right-click the dynamic action, and select “Create TRUE Action”. Set the action of the new True action to “Execute JavaScript Code”, and paste in the following code:
‘mailto:’+ $v(‘P3_EMAIL_LIST’)
);
When the button is clicked, this JavaScript code will produce an “emailto:” link, then open that link in the user’s default email client. Congratulations! You now have the ability to email all of the contacts associated with this Oracle APEX report.
In part 2 of the series, we’ll learn how to create automated emails with dynamic messages, and in part 3, we’ll dig into sending automated emails with advanced HTML formatting.
Stuck on a challenging APEX project? Traust provides expert Oracle APEX application development — and training for your team! Contact us today for more information.
