Send an Email from APEX
There are couple of ways in which you can send email from APEX, lets go through
1. Use the combination of PLSQL, HTML and APEX_MAIL
In this case we are fetching the data from Table into cursor and putting the data through a loop and formatting it using HTML in a table format and then sending an email using APEX_MAIL
DECLARE
l_body CLOB := NULL;
CURSOR c1 IS select PROPERTY, WEEK, ALERTS, JIRA, HOST, AVG_OPEN_ALERTS from WEEKLY_STATS where property='ABC' order by ID desc fetch first 5 rows only;
CURSOR c2 IS select PROPERTY, WEEK, ALERTS, JIRA, HOST, AVG_OPEN_ALERTS from WEEKLY_STATS where property='XYZ' order by ID desc fetch first 5 rows only;
BEGIN
l_body := l_body
|| '<head>
<style>
table, th, td {
border:.05px solid black;
}
table {
text-align: center;
}
</style>
</head>
<h2><a href="https://apex.oracle.com/pls/apex/r/<workspace>/<app_name>/<Link>">ABC</a></h2>
<table style="width:100%"><tr>
<th><b>WEEK</b></th>
<th>ALERTS</th>
<th>JIRA</th>
<th>HOST</th>
<th>AVG_OPEN_ALERTS</th> </tr>';
FOR rec IN c1 LOOP
l_body := l_body ||'</td> <td>' || rec.WEEK
||'</td> <td>' || rec.ALERTS
||'</td> <td>' || rec.JIRA
||'</td> <td>' || rec.HOST
||'</td> <td>' || rec.AVG_OPEN_ALERTS
||'</td> </tr>'|| utl_tcp.crlf;
END LOOP;
l_body := l_body || '</table>' || utl_tcp.crlf;
l_body := l_body || '<br>' || utl_tcp.crlf;
l_body := l_body || '</td>'|| utl_tcp.crlf;
l_body := l_body || '</tr>'|| utl_tcp.crlf;
l_body := l_body || '</table>'|| utl_tcp.crlf;
l_body := l_body
|| '<head>
<style>
table, th, td {
border:.05px solid black;
}
table {
text-align: center;
}
</style>
</head>
<h2><a href="https://apex.oracle.com/pls/apex/r/<workspace>/<app_name>/<Link>">XYZ</a></h2>
<table style="width:100%"><tr>
<th><b>WEEK</b></th>
<th>ALERTS</th>
<th>JIRA</th>
<th>HOST</th>
<th>AVG_OPEN_ALERTS</th> </tr>';
FOR rec IN c2 LOOP
l_body := l_body ||'</td> <td>' || rec.WEEK
||'</td> <td>' || rec.ALERTS
||'</td> <td>' || rec.JIRA
||'</td> <td>' || rec.HOST
||'</td> <td>' || rec.AVG_OPEN_ALERTS
||'</td> </tr>'|| utl_tcp.crlf;
END LOOP;
l_body := l_body || '</table>' || utl_tcp.crlf;
l_body := l_body || '<br>' || utl_tcp.crlf;
l_body := l_body || '</td>'|| utl_tcp.crlf;
l_body := l_body || '</tr>'|| utl_tcp.crlf;
l_body := l_body || '</table>'|| utl_tcp.crlf;
--dbms_output.Put_line(l_body);
apex_mail.Send(p_to => 'test_send_mail@gmail.com',
p_from => 'test.server@oracle.com',
p_subj => 'Weekly Volume ABC and XYZ',
p_body => l_body,
p_body_html => l_body);
apex_mail.push_queue;
END;
The Email Content:
2. Second way of sending an email using
Within the APEX page created, go to Processing section and under Processes section create a Process by name "Send E-Mail"
On the Right Side Pane, under Process in the Identification section
Name: Send E-Mail
Type: Send E-mail
Execution Chain: None
Editable Region: Leave it to default value
Under the Settings section
From: &APP_EMAIL.
To: test_send_mail@gmail.com
Add Cc, Bcc and Reply To if required
Email Template: select if you have created one, if not leave it to default value
Subject: Add the Subject
Add the following in the Body HTML section. We are going to fetch the values from the Page Items and send those data through an email
<head>
<style>
table, th, td {
border:.05px solid black;
}
table {
text-align: center;
}
</style>
</head>
<h3>Please find the Weekly Volume of ABC and XYZ Properties</h3>
<table style="width:100%">
<tr>
<th>PROPERTY</th>
<th>JIRA</th>
<th>ALERTS</th>
<th>AVG_OPEN_ALERTS</th>
<th>HOST</th>
</tr>
<tr>
<td>&P2006_PROPERTY.</td>
<td>&P2006_JIRA.</td>
<td>&P2006_ALERTS.</td>
<td>&P2006_AVG_OPEN_ALERTS.</td>
<td>&P2006_HOST.</td>
</tr>
<tr>
<td>&P2006_PROPERTY_1.</td>
<td>&P2006_JIRA_1.</td>
<td>&P2006_ALERTS_1.</td>
<td>&P2006_AVG_OPEN_ALERTS_1.</td>
<td>&P2006_HOST_1.</td>
</tr>
</table>
The Email Content:
