Thursday, January 27, 2022

Advanced email techniques (APEX Mail #9)

   




We have described the most important things about "Emails with Oracle APEX" in the previous blogs. To wrap up this series now, we would like to list a few more techniques, tips, and tricks that we have come across over time. So let's move on to the last topic number nine.

#9 Advanced email techniques

First of all, it should be mentioned that the following points are only based on experience and do not necessarily have to be implemented. But maybe it will help you to create your own email templates. The biggest problem with HTML emails is that the rules for HTML have not yet been standardized for all email clients. This sets limits and calls for creativity during development and should be tested as best as possible. Ideally with as many clients as possible.


Use HTML Tables

It is better to use a <BR> or <Table> tag instead of <P> or <SPAN>. This avoids inconsistent spacing since spacing has different margins in each email client. The safest way to have consistent spacing is to use tables. If you still need to use a <SPAN> tag, end it with a <BR> tag.


Use Inline CSS

If you're wondering why your CSS isn't working, it could be because most email clients remove the <header> of an HTML page so that external style sheets aren't rendered properly. As a solution, you can put a CSS inline with each element instead. Manage links by using an inline style for each link color. Add the colors in hexadecimal, using the longhand version #000000 instead of #000. Keep in mind that not every CSS element is supported in every email client.

In the following HTML code example, you can see how tables and inline CSS can be used:

 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
39
40
41
42
43
44
45
<table border="0" width="100%" cellspacing="0" cellpadding="0" style="padding: 20px 0 30px 0;">
 <tbody>
  <tr>
   <td style="color: #153643; font-family: Arial, sans-serif; font-size: 24px;"><strong>Lorem ipsum dolor sit amet!</strong></td>
  </tr>
  <tr>
   <td style="color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px; padding: 20px 0 30px 0;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. In tempus adipiscing felis, sit amet blandit ipsum volutpat sed. Morbi porttitor, eget accumsan dictum, nisi libero ultricies ipsum, in posuere mauris neque at erat.</td>
  </tr>
  <tr>
   <td>
    <table border="0" width="100%" cellspacing="0" cellpadding="0">
     <tbody>
      <tr>
       <td valign="top" width="260" style="padding: 20px 20px 20px 20px;">
        <table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tbody>
          <tr>
           <td><img style="display: block;" src="https://bit.ly/3rmktSc" alt="" width="100%" height="auto" /></td>
          </tr>
          <tr>
           <td style="color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px; padding: 25px 0 0 0;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. In tempus adipiscing felis, sit amet blandit ipsum volutpat sed. Morbi porttitor, eget accumsan dictum, nisi libero ultricies ipsum, in posuere mauris neque at erat.</td>
          </tr>
         </tbody>
        </table>
       </td>
       <td style="font-size: 0; line-height: 0;" width="20">&nbsp;</td>
       <td valign="top" width="260" style="padding: 20px 20px 20px 20px;">
        <table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tbody>
          <tr>
           <td><img style="display: block;" src="https://bit.ly/3IdtjsA" alt="" width="100%" height="auto" /></td>
          </tr>
          <tr>
           <td style="color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px; padding: 25px 0 0 0;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. In tempus adipiscing felis, sit amet blandit ipsum volutpat sed. Morbi porttitor, eget accumsan dictum, nisi libero ultricies ipsum, in posuere mauris neque at erat.</td>
          </tr>
         </tbody>
        </table>
       </td>
      </tr>
     </tbody>
    </table>
   </td>
  </tr>
 </tbody>
</table>

The HTML body should look like this:




Use LOOPS for mass distribution

If you need to send many emails at once, you can solve this with a loop. So write a SQL query that selects all recipients and execute the procedure or function "apex_mail.send" in the loop. You can find a very good description here (Creating Email Campaign App with Oracle APEX).


A tiny example could be the following code snippet:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
begin
    for rec in (select contact_name, contact_email from contacts) 
    loop        
        apex_mail.send (
        p_to                 => rec.contact_email,
        p_template_static_id => 'HELLO_WORLD',
        p_placeholders       => '{' ||
        '    "CONTACT":'            || apex_json.stringify( rec.contact_name ) ||
        '}' );
    end loop;

    apex_mail.push_queue;
end;

Use different SQL techniques for dynamic content

As we explained in a previous blog, dynamic HTML content can be created by using PL/SQL. This can be very handy in many ways to make the content of your email dynamic, personalized, or even random.

For example, the following SQL snippet can be used to display a personalized order.

 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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
declare
   l_order_table VARCHAR2(32767 CHAR);
begin
   l_order_table := l_order_table || '<table style="font-family: arial, sans-serif; border-collapse: collapse; width: 100%;">' 
                 || '<tr>'
                 || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Position</th>'
                 || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Article</th>'
                 || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Quantity</th>'
                 || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Unit price</th>'
                 || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Total price</th>'
                 || '</tr>';

   for rec in (
       select ROW_NUMBER() OVER (ORDER BY article) AS position,
              article, 
              sum(quantity) as quantity, 
              sum(price) as price, 
              sum(quantity * price) as total
         from 
         (
         select 'Article 1' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual
         union
         select 'Article 2' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual
         union
         select 'Article 3' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual
         union
         select 'Article 4' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual
         union
         select 'Article 5' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual
         )
        group by rollup(article)
   )
   loop
      if rec.article is not null then
        l_order_table := l_order_table || '<tr>'
                        || '<td style="border: 1px solid #dddddd; text-align: left; padding-left: 8px;">' || rec.position || '</td>'
                        || '<td style="border: 1px solid #dddddd; text-align: left; padding-left: 8px;">' || rec.article || '</td>'
                        || '<td style="border: 1px solid #dddddd; text-align: right; padding-left: 8px;">' || rec.quantity || '</td>'
                        || '<td style="border: 1px solid #dddddd; text-align: right; padding-left: 8px;">' || rec.price || '</td>'
                        || '<td style="border: 1px solid #dddddd; text-align: right; padding-left: 8px;">' || rec.total|| '</td>'
                        || '</tr>';
      else 
        l_order_table := l_order_table || '<tr>'
                        || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding-left: 8px; font-weight: bold;">Sum</td>'
                        || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding-left: 8px; font-weight: bold;"></td>'
                        || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: right; padding-left: 8px; font-weight: bold;"></td>'
                        || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: right; padding-left: 8px; font-weight: bold;">' || rec.price || '</td>'
                        || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: right; padding-left: 8px; font-weight: bold;">' || rec.total || '</td>'
                        || '</tr>';
      end if;                  
   end loop;
   
   l_order_table := l_order_table || '</table>';

   :P1_ORDER_TABLE := l_order_table;
end;


And this is how the email output could be:




So, as you have seen and learned, there are many ways to create and send emails. However, you should always pay attention to some details and think in advance who the recipient group will be and what email clients you can expect.


Here is the demo app for reference.


Quellen:

Labels: , , ,

posted by Timo @ January 27, 2022   0 Comments

0 Comments:

Post a Comment

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

Subscribe to Post Comments [Atom]

<< Home