Embedding images in an email (APEX Mail #5)

 

Everything you need to know about sending emails with Oracle APEX can be found in this series. The fifth post will be a bit more exciting and offers different approaches, which we would like to explain here.

#5 Embedding images to an email

If you want to embed or display images in an email, there are several options. The easiest way is to use the <img> tag, which can be used to embed an image in an HTML page. For example:

<img src="https://www.oracle.com/a/ocom/img/dc/em/oracle-apex.png" alt="APEX Logo">

However, it is recommended to avoid references to images with the <img> tag. Remember that the images must be accessible to the recipient's email client in order for them to see the image. In this way, images are not attached to the email but are referenced in the email.

To work around this problem, we can attach the image to the email and then reference it with HTML tags in the email template. This will embed the image when the email is opened. CID (Content-ID) has been used for quite some time for sending images via email and is relatively easy to understand. 

Note, that embedding images in this way will increase the size of your entire email, which can hinder deliverability.

Let's start with an APEX 21.2 environment first

There is good news because if you use APEX 21.2 it is possible to send attachments declaratively as inline-attachments. For this, a new parameter "p_content_id" has been added to the "apex_mail.add_attachment" procedure. So, let's see how it works. 

And again, we'll use the demo application we've already created in the previous blogs so we don't have to describe everything from scratch.

First, we add a new email template to this application. The easiest way is to copy the existing "Hello World" template (btw. another great feature of APEX 21.2). To do this, go to "Other Components" in the "Shared Components" section, select "Email Templates" and click "Copy". Then select "Hello World" under "Copy Email Template" and name it for example "Hello Image" ("New Static Identifier" will be generated automatically).

When this is done, the template must be edited. For that, go to the template details and replace the header code with the following HTML code, for example, so that the image and the title are displayed in the header.

<H1>Hello Image!</H1>

<img src="cid:apex_logo.png" alt="APEX Logo" >

As you can see here, we also use the <img> tag, but instead of specifying a URL as the source, we specify a content-id "cid:apex_logo.png". 

To use the new template, we need another process on page 1. So go to page 1 and copy the process "Send email" and name the copy e.g. "Send mail with Image". Then change the email template to "Hello Image" and add the following SQL code to "Attachment SQL".

select blob_content,

       filename,

       mime_type,

       'apex_logo.png' as content_id

  from apex_application_files

 where flow_id = :APP_ID

   and filename = 'apex_logo.png'

This example again uses the static-file from the previous blog. Please adjust it if you use a different file.

The column "content-Id" is new in APEX 21.2. If the value of the content_id is non-null then the associated file will be marked as being attached inline. And this is exactly what we need here ;-)

Finally, we will set the "Server Side Condition" so that this process is only executed when the "Send" button is clicked and the "Hello Image" email template is selected. Therefore, go to "Server-Side-Condition" and select "Send" as "When Button Pressed". Then set "Item = Value" as the type, "P1_EMAIL_TEMPLATE" as the item, and "HELLO_IMAGE" as the Value. To be consistent, do the same for the existing "Send Email" process and enter "HELLO_WORLD" as the value here.

So, that's it... let's run the application now and try to send an email with an image. 
The email should look like this:

Next, we will look at how it works with an older environment

If you have an APEX version older than 21.2 in your environment, you will have to do a little bit more.

For this, we use the "apex_mail.add_attachment" procedure as we also did when adding an attachment to an email. Then the images are referred in the email content using the attached filename. For example, if you want to add the image "apex_logo.png", you need to attach this image file to the email, first. Then you can reference the attached image in your email template using the syntax described above.

<img src="cid:apex_logo.png" alt="APEX Logo" >

So, the first thing we need is the email template. If "Hello Image" has not been created, please create it as described above.

Next, we need a process to send the email with the image when we hit the "Send email with API" button.

Therefore, switch to Processes in the tree structure and copy the process "Send email with API" and name the copy e.g. "Send mail with Image (API)".

In this case, the PL/SQL code does not need to be changed. As you can see here, we have already attached an image here and sent the email.

declare

    l_id number;

begin

    -- this functions sends an email message from a Oracle APEX application

    -- this function returns a NUMBER

    -- the NUMBER returned is the unique numeric identifier associated with the mail message

    l_id := apex_mail.send(

        -- Valid email address to which the email is sent

        p_to                 => :P1_TO,

        -- Email address from which the email is sent

        p_from               => :APP_EMAIL,

        -- Static identifier string, used to identify the shared component email template

        p_template_static_id => :P1_EMAIL_TEMPLATE,

        -- JSON string representing the placeholder names along with the values, to be substituted

        p_placeholders       => '{' ||

        '    "CONTACT":'     || apex_json.stringify( :P1_CONTACT ) ||

        '}'

    );  

    -- in this example an image from the "Static application files" is used

    -- the filename that is attached must match with the filename that is specified as src

    -- if the filename does not match the image will be attached as a normal attachment

    for rec in (

        select filename, blob_content, mime_type

          from apex_application_files

         where flow_id = :APP_ID

           and filename = 'apex_logo.png'

    )

    loop   

        apex_mail.add_attachment(

            p_mail_id    => l_id,

            p_attachment => rec.blob_content,

            p_filename   => rec.filename,

            p_mime_type  => rec.mime_type);

    end loop;

    -- Oracle APEX stores unsent email messages in a table named APEX_MAIL_QUEUE.

    -- You can manually deliver mail messages stored in this queue to the

    -- specified SMTP gateway by invoking the APEX_MAIL.PUSH_QUEUE procedure.

    apex_mail.push_queue;

end;  

We should only set the "Server Side Condition" as described before so that this process is only executed when the "Send API" button is clicked and the "Hello Image" email template is selected. To do this, go to "Server Side Condition" and select "Send API" as "When Button Pressed". Then set "Item = Value" as the type, "P1_EMAIL_TEMPLATE" as the item, and "HELLO_IMAGE" as the value. 

And again, to be consistent, do the same for the existing process "Send Email with API" and enter here "HELLO_WORLD" as a value.

That's it... start the application and try to send an email with an image via the API.
Again, the email should look like this:

Note: Both processes still have a difference. In APEX environments that are older than 21.2, attachments are always added with the Content-Disposition as attachment. Now in 21.2, APEX will specify Content-Disposition as inline for those attachments. This enables more reliable embedding of images in email content and works in all email clients that support the cid:schema approach.

Feel free to let us know if these tips and tricks helped you. 

If you have questions or suggestions, please leave us a comment ;-)

Here is the demo app for reference.

Quellen:

• https://bit.ly/3lOAmiF
• https://bit.ly/31O75gz

Sending emails with attachment (APEX Mail #4)

 

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

#4 Sending emails with attachment

If you want to add attachments to an email, you can do this declaratively in the APEX builder again or use the "APEX_MAIL" API. In this blog we will show you both options.

Let´s start by creating (or modifying) a demo app

To avoid having to create a new application again, let's take the demo app from the previous blog post. 

Then we need a new button to send an email with attachment. So, add a button "Text and Icon (Hot)" to the "Close" position. Name the button "SEND_ATTACHMENT" and use e.g. "fa-paperclip" as the icon and "Send email with attachment" as the label.

To send an email by using the API, add another "Text and Icon (Hot)" button at the "Close" position. Name the button "SEND_ATTACHMENT_API" and use e.g. "fa-paperclip" as an icon and "Send email with attachment (API)" as caption.

If you start the application now, it should look like this.

Add attachment to your application

For our example we upload a file to the static files which we attach to the email. However, you can also use files from existing database tables if you wish.

To do this, go to "Static application files" under "Files" of the "Shared components" section. Then click "Create File" and select a file you want to attach. 

In this example, the file "apex_logo.png" was uploaded and will be used in the further course. So adjust the filename if necessary.

Create processes to send the email with attachment

Next, we need two processes to send an email with attachment. The first one when we click on the "Send email with attachment" button and the second one when we click on the "Send email with attachment (API)" button.

Therefore, switch to Processes in the tree structure and create a new process. For example, name it "Send mail with attachment" and select the type "Send E-Mail". 

Under Settings, enter the following:

From: &APP_EMAIL.

To: &P1_TO.

Email Template: Hello World

Placeholder Values: Placeholder -> Contact / Value -> &P1_CONTACT.

Attachment SQL: 

select blob_content,

       filename,

       mime_type

  from apex_application_files

 where flow_id = :APP_ID

   and filename = 'apex_logo.png';

Send immediately: true

Success Message: Email sent

Error Message: Email sent failed!

When Button Pressed: SEND_ATTACHMENT

So that's all we need to do for the declarative solution. 

Now let's move on to the "APEX_MAIL" API process.

Create again a new process. For example, name it "Send mail with attachment (API)" and select the type "Execute Code". 

 

Under Source, enter the following PL/SQL Code: 

declare

    l_id number;

begin

    -- this functions sends an email message from a Oracle APEX application

    -- this function returns a NUMBER

    -- the NUMBER returned is the unique numeric identifier associated with the mail message

    l_id := apex_mail.send(

        -- Valid email address to which the email is sent

        p_to                 => :P1_TO,

        -- Email address from which the email is sent

        p_from               => :APP_EMAIL,

        -- Static identifier string, used to identify the shared component email template

        p_template_static_id => :P1_EMAIL_TEMPLATE,

        -- JSON string representing the placeholder names along with the values, to be substituted

        p_placeholders       => '{' ||

        '    "CONTACT":'     || apex_json.stringify( :P1_CONTACT ) ||

        '}'

    );  

    -- to add multiple attachments to a single email,

    -- APEX_MAIL.ADD_ATTACHMENT can be called repeatedly in a loop

    for rec in (

        select filename, blob_content, mime_type

          from apex_application_files

         where flow_id = :APP_ID

           and filename = 'apex_logo.png'

    )

    loop

        -- this procedure adds an attachment to an email        

        apex_mail.add_attachment(

            p_mail_id    => l_id,

            p_attachment => rec.blob_content,

            p_filename   => rec.filename,

            p_mime_type  => rec.mime_type);

    end loop;

    -- Oracle APEx stores unsent email messages in a table named APEX_MAIL_QUEUE.

    -- You can manually deliver mail messages stored in this queue to the

    -- specified SMTP gateway by invoking the APEX_MAIL.PUSH_QUEUE procedure.

    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_ATTACHMENT_API

That´s it. Let's try to send an email with attachment by clicking on the "Send email with attachment" and/or "Send email with attachment (API)" button.

And this is how the result of the email should look like. As you can see, the attachment was attached to the e-mail.

And here is the link for the demo app.

 

Quellen:

• https://bit.ly/3y4zGKT
  

Javascript APIs (#3 the date namespace)

Hello everyone and welcome back to a new blog, this blog is one of the blog series about the Javascript APIs that we have started. In this blog, we will talk about the date namespace so let's get started :). 

The apex.date namespace contains the oracle APEX functions related to date operations, so this API has functions that will help you work with the date items and values. It makes working with different dates format much easier than using normal Javascript. 

Before we start, go to our demo app, navigate to the first page and add a new static region. Now we can start...

date.parse()

this function returns parsed date object of a given date string and a format mask, it accepts a date in a string format and converts it into a date object so you can use all methods available on a date object. The function uses the default date format mask defined in the application globalization settings. 

Go to the demo app and add the following in the new static region

• Date page item: name= P1_DATE1
• Display only page item: name=P1_DATE1_VAL
• Dynamic action that fires when P1_DATE1 changes: add a new true action that run the following Javascript code:

                                            let date_val = apex.item('P1_DATE1').getValue();

                                            let parsed_date = apex.date.parse(date_val);

                                            apex.items.P1_DATE1_VAL.value=parsed_date.toString();

you should have something like 

in the code above, we converted the value of the date object into a string to show it in the display-only item. the parse() function accepts a second parameter to specify the format, if the parameter is not given, the function uses the default format and language defined in the application globalization settings. To check your application setting, you can go to shared components and see globalization, or open the developer tool in your browser and write the following javascript code:

apex.local.getDateFormat();

apex.local.getLanguage();

you can also try using a different format, type the following in you console

apex.date.parse('2021.12.02 11:20','YYYY.MM.DD HH24:MI')

date.add() 

This function is used to add a certain amount of time to an existing date object, it also returns a date object. the function accepts three parameters, the first is the date object you want to modify, the second is the amount you want to add, and the third is optional which is the unit.

Go back to the Demo app and add the following:

• Date page item: name= P1_DATE2
• button: name= addDay
• dynamic action fires when addDay button click: add a true action that execute the following Javascript code

let strDate = apex.items.P1_DATE2.value;

let dateVal = apex.date.parse(strDate);

let newDate = apex.date.add(dateVal, 1, apex.date.UNIT.DAY);

apex.items.P1_DATE2.value = newDate.toString();

in the same way, you can add a Month, year, or any amount of time. Just change the unit. You can also subtract a specific amount of date from a date object using subtract()

date.format()

you can use this function to change the default date format, meaning if you want to use a specific date format other than the default one. but keep in mind that you can only use oracle compatible format masks. by giving an extra parameter you can also specify the language you want.

Add the following items in the demo app

• Date page item: name= P1_FORMAT
• Display only page item: name=P1_FORMAT_VAL
• Button: name=format
• Dynamic action fires when the format button is clicked, add a true action which runs the following Javascript code:

let strDate = apex.items.P1_FOMRAT.value;

let selectedDate = apex.date.parse(strDate);

let formattedDate = apex.date.format(selectedDate, 'yyyy.Month.dd', 'de');

apex.items.P1_FORMAT_VAL.value = formatted date;

date.firstOfMonth()

this function is very useful, it returns a date object for the first day a month of a given date object. You have a date object and you want to get the first day in the month of your date, just use this function.

Add the follwoing in the demo app

• Date page item: name=P1_DATE3
• Display only page item: name= P1_FIRST_DAY
• Button: name= getFirstDay
• Dynamic action fires when getFirstDay button click: add a new true action which runs the following Javascript code

let strDate = apex.items.P1_DATE3.value;

let selectedDate = apex.date.parse(strDate);

let firstDay = apex.date.firstOfMonth(selectedDate);

apex.items.P1_FIRST_DAY.value = apex.date.format(firstDay,'yyyy.mm.dd');

you should have something like this:

there is a similar function to get the last of the month, just use lastOfMonth() instead of firstOfMonth()

isAfter()

this function is used to compare two date objects, it returns true if the first date object is after the second date object, otherwise, it returns false.

Add the following items to the demo app

• 2x Date page item: name= P1_FIRST_DATE, P1_SECOND_DATE
• Display only page item: name= P1_RESULT
• Button: name=compare
• Dynamic action fires when compare button is clicked: add a true action that runs the following Javascript code

let strFirstDate = apex.items.P1_FIRST_DATE.value;

let strSecondDate = apex.items.P1_SECOND_DATE.value;

let firstDate = apex.date.parse(strFirstDate);

let secondDate = apex.date.parse(strSecondDate);

let result = apex.date.isAfter(firstDate, secondDate);

apex.items.P1_RESULT.value = result;

in the same way, you can compare is a date object is before a given date, just use isBefore()

date.isSame()

this function is used for comparing two dates, it returns true if the first date matches the second.

Go to the demo app and add the following:

• Date page item: name=P1_IS_SAME1
• Date page item: name=P1_IS_SAME2
• Display only page item: name = P1_IS_SAME_VAL
• Button (name=P1_IS_SAME) with a dynamic action which fires on the button click and runs the following Javascript code:

let strDate1 = apex.items.P1_IS_SAME1.value;

let strDate2 = apex.items.P1_IS_SAME2.value;

let date1 = apex.date.parse(strDate1);

let date2 = apex.date.parse(strDate2);

let result = apex.date.isSame(date1,date2);

apex.items.P1_IS_SAME_VAL.value = result;

date.isLeapYear()

this will be the last function for this blog. As the name suggests, this function tells you whether the given year is a leap year or not, it returns true when it is. let's try it by adding the following to the demo app

• Date page item: name= P1_LEAP
• Display only page item: name= P1_LEAP_VAL
• Button: name= P1_CHECK
• Dynamic action fires when the button is clicked, add a true action that runs the following Javascript code

let strDate = apex.items.P1_LEAP.value;

let leapDate = apex.date.parse(strDate);

let isLeap = apex.date.isLeapYear(leapDate);

apex.items.P1_LEAP_VAL.value = isLeap;

So that's it for this post, in the next post we will start with a new namespace :).

Demo

References:

• https://docs.oracle.com/en/database/oracle/application-express/21.2/aexjs/apex.date.html#.isLeapYear

Preview of your email (APEX Mail #3)

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

#3 Preview of your email

If you want to preview the email before sending it, you can use another procedure from the "APEX_MAIL" package. This can be very handy, especially if the content of the email is dynamic.

Let´s start by creating (or modifying) a demo app

For the preview, we will again use the demo application that we have already created in the previous blogs, so that we don't have to describe everything from the beginning.

To display the preview, we add another button on page 1, which then opens a modal dialog. So add a button "Text and Icon (Hot)" to the "Help" position. Name the button "PREVIEW" and use e.g. "fa-envelope-search" as the icon and "Preview" as the label.

Next, we need a branch that will open the modal dialog when you click the Preview button and set items on the target page. Therefore, switch to Processes in the tree structure and create a new branch. For example, name it "Show Preview" and select the type "Page or URL". The target page doesn't exist yet, but we can still enter the information. So enter page "2" under "Target" and the following information for "Set Items".

• Name: P2_CONTACT
  Value: &P1_CONTACT.
• Name: P2_EMAIL_TEMPLATE
  Value: &P1_EMAIL_TEMPLATE.
• Name: P2_TO
  Value: &P1_TO.

It should look like this:

If you start the application now, it should look like this.

Create a new page to preview the email

Next, we need a new page to preview the email when we click the "Preview" button. So, click on "Create page" in the APEX-Builder and select "Blank page" as page type. Set the page number to "2" and name it "Preview" for example. The page mode could be a "modal dialog", but choose what you want here. Navigation is not required, so don't create a new entry here.

First we add a "Static Content" region to the page and create 6 new page items.

• P2_TO as Hidden (This will have the address of the email recipient)
• P2_CONTACT as Hidden (This will have the name of the recipient)
• P2_EMAIL_TEMPLATE as Hidden (This will have the email template that we will preview)
• P2_SUBJECT as Hidden (This will have the subject of the email template)
• P2_BODY_HTML as Hidden (This will have the HTML Body of the email template)
• P2_BODY_TEXT as Hidden (This will have the Plain Text of the email template)

Then we need a new process that generates the preview. To do this, create a new "Before Header" process under "Pre-Rendering" and name it e.g. "Create Preview". The Process executes PL/SQL Code, so choose the type "Execute Code" and enter the following PL/SQL code:

declare

    l_placeholders CLOB;

begin    

    -- build a JSON formatted string for the placeholders

    APEX_JSON.initialize_clob_output;

    APEX_JSON.open_object;        

    APEX_JSON.write('CONTACT', :P2_CONTACT);

    -- If there are more placeholders

    -- they can be added here with another APEX_JSON.write

    APEX_JSON.close_object;

    l_placeholders := APEX_JSON.get_clob_output;

    APEX_JSON.free_output;

    -- Preview Email

    apex_mail.prepare_template (

         -- Static id string, used to identify the shared component email template

         p_static_id     => :P2_EMAIL_TEMPLATE

         -- JSON string representing the placeholder names along with the values

        ,p_placeholders  => l_placeholders

        -- The subject line generated from the template

        ,p_subject       => :P2_SUBJECT

        -- The HTML code for the email

        ,p_html          => :P2_BODY_HTML

        -- The plain text of the email

        ,p_text          => :P2_BODY_TEXT

    );    

end;

Now we have generated the output for the preview and placed it in the page items. To display the items, we need 3 more "Static Content" regions. Create them with the following information:

Region 1

Title: E-Mail

HTML-Code: <b>From:</b> &APP_EMAIL.</br><b>To:</b> &P2_TO.</br><b>Subject:</b> &P2_SUBJECT.

Template: Content Block using heading level 3

The recipient, the sender and the subject of the email are displayed here.

Region 2

Title: HTML Body

HTML-Code: &P2_BODY_HTML!RAW.

Template: Content Block using heading level 3

Here we want to display the email body in HTML. Therefore we have to escape special characters in the substitution value. To do this, we append "!RAW" to the item. "!RAW" preserves the original item value and does not escape characters. You can read more about "Controlling Output Escaping in Substitution Strings" here.

Region 3

Title: Plain Text

HTML-Code: &P2_BODY_TEXT.

Template: Content Block using heading level 3

Finally the plain text is displayed here.

So there you have it. Let's try to preview our email

And here is the link for the demo app.

Quellen:

• https://bit.ly/3FMuZYJ
• https://bit.ly/3DTTSBd

Javascript APIs (#2 apex.item)

Hello everyone and welcome back to the next post where we will cover the second part of the item interface. This post is one of a series in which we will discuss the most commonly used Javascript APIs. 

In the following, we will continue by explaining the methods provided by the item interface and see how we can use them in our Demo app. 

Disable, Enable, and isDisabled methods

as the name suggests, these methods can be used to Disable or Enable one of the page items, they make it either available for editing or unavailable. Please notice, that not all items support being editable, so we can't use this method on all items. We can use them only on items that can be edited e.g text field items.

Go to the demo app we created in the last post and add the following item and dynamic actions in a new static region:

• Text field page item: name=P1_ITEM3
• Button: name=change
• Dynamic action: fires when change button is clicked, add a true action that execute the following javascript code:

if (apex.items.P1_ITEM3.isDisabled())

    apex.items.P1_ITEM3.enable();

    else

    apex.items.P1_ITEM3.disable();

as you can see in the code we first test whether the text field is already disabled if this was the case, we enable it, otherwise, we disable it. 

run the application and test it, please note that you can achieve the same by simply using the predefined dynamic action which enables/disables an item. but the idea here is to learn how to use the JS API.

getValue/setValue methods

getValue returns the current value of an item. There is a shorthand for this function which is $v() and also as we discussed in the previous post you can use the value attribute of the item. e.g 

apex.items.P1_ITEM1.value

is equivalent to

apex.item('P1_ITEM1').getValue()

and are the same as

$v('P1_ITEM1')

Now, for setting an item value we can use the shorthand $s() or the item attribute value or we can use the  following function which give us more options

apex.item('P1_ITEM1').setValue(param1,param2,param3)

this function sets the current value of the item, but the session state will not be affected until you submit the page (or you can use AJAX) .

param1: the value to set. if the item can accept more than one value, you can give those values as an array of string

param2: the display value. This parameter is optional, you have to provide it when the display value is not the same as the item value.

param3: some items have a change event, this event will run the callback function whenever the item value has been changed. You can prevent the event from firing by giving true as a value for this parameter. 

Go back to our demo and add the following:

• text field page item: name= P1_ITEM5
• display only page item: name=P1_ITEM5_VAL
• dynamic action that fires on key release: selection type=item, item=P1_ITEM5. this dynamic action has a true action which runs the following javascript: 

apex.item('P1_ITEM5_VAL').setValue(apex.item('P1_ITEM5').getValue())

run the application and test it. 

setStyle method

this method sets the style of an item, meaning you change the style of an item as you do use CSS. However, keep in mind that this method is not the best practice. It is also recommended that you add/remove the class of an item and then edit the style in your CSS code using those classes.

Go to the demo app and add the following 

• text field page item: name=P1_ITEM6
• button: name=change_color
• dynamic action that fires on change_color click: add a true action which runs the following javascript code

apex.item( "P1_ITEM6" ).setStyle( "background", "red" );

so at the end your application should look something like

so as you see there are many ways to use these cool APIs... all other methods are basically the same, you know have an idea about how to use them... so go to the JS APIs documentation and have a look and stay tuned for our coming posts :)

Demo

References

• https://docs.oracle.com/en/database/oracle/application-express/21.2/aexjs/item.html#setStyle