Up to this point in the series all emails have been simple, plain text with or without attachments. Often we’ll want to send emails with special formatting – different fonts, highlighting, tables, embedded images, etc.
Much like SMTP not directly supporting attachments, it doesn’t support cosmetic features of formatted content either. The solution to both lies with MIME to define our content structure as HTML which most email clients can extract and then render.
In the previous article on attachments we saw the use of MIME Content-type declarations, including text/plain and image/jpeg for individual blocks within the email body and multipart/mixed at the header level.
For a body consisting only of HTML we’ll use text/html and if we have multiple types we’ll again use multipart/mixed. I’ll also use an additional content type multipart/related which allows MIME-blocks to reference each other.
Simple HTML content
Let’s start with the easiest construction, an email body consisting of only HTML content. In the example below I construct an HTML table of data and present it within my email. The result should look something like this…
| column1 | column2 |
|---|---|
| data 1a | data 1b |
| data 2a | data 2b |
| data 3a | data 3b |
| data 4a | data 4b |
Note you can even include global styling rules in the HTML header or locally to individual tags just as you might if you were creating a web page. In this example I use some of both to illustrate the functionality.
DECLARE
v_connection UTL_SMTP.connection;
BEGIN
v_connection :=
UTL_SMTP.open_connection(
HOST => 'smtp.gmail.com',
port => 587,
wallet_path => 'file:/home/oracle/mywallets/gmail',
-- password not needed because wallet is auto login
secure_connection_before_smtp => FALSE);
UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
UTL_SMTP.starttls(v_connection);
UTL_SMTP.auth(v_connection, 'sean.stuber@gmail.com', :password, 'PLAIN');
UTL_SMTP.mail(v_connection, 'sean.stuber@gmail.com'); --from
UTL_SMTP.rcpt(v_connection, 'sean.stuber@gmail.com'); --- to
UTL_SMTP.open_data(v_connection);
UTL_SMTP.write_data(v_connection,
'From: sean.stuber@gmail.com' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
'To: sean.stuber@gmail.com' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
'Subject: formatted email to myself' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
'Content-Type: text/html' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, '<html>
<head>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:25%")>
<tr style="background-color: #999966;"><th>column1</th><th>column2</th></tr>
<tr style="background-color: #f5f5ef;"><td>data 1a</td><td>data 1b</td></tr>
<tr style="background-color: #d8d8c0;"><td>data 2a</td><td>data 2b</td></tr>
<tr style="background-color: #f5f5ef;"><td>data 3a</td><td>data 3b</td></tr>
<tr style="background-color: #d8d8c0;"><td>data 4a</td><td>data 4b</td></tr>
</table>
</body>
</html>');
UTL_SMTP.close_data(v_connection);
UTL_SMTP.quit(v_connection);
END;
In the example above, I wrote the entire HTML content as a single static string to make it easier to read.
Instead, you could use a similar technique as shown in the APEX_MAIL article and create your own templates. Predefine a varchar2 or clob value with place-holder stubs. Then use REPLACE to fill them in within your code. Below I create an email similar to the expiration notice I used in the APEX_MAIL examples: Looping through customers, fill in the place holders and send each of them personalized email with their name and number of expired orders.
DECLARE
c_template constant varchar2(500) := '<html>
<body>
<h2>Notice of Expirations!</h2>
Hello #FIRST_NAME# #LAST_NAME#,<br>
<br>
We are reaching out to let you know you have #EXPIRED_COUNT# expired order(s).<br>
If you would like to renew, please contact us at our <a href=https://renew.example.com>renewal site.</a><br>
<br>
Thank you
</body>
</html>';
v_connection UTL_SMTP.connection;
v_content varchar2(500);
BEGIN
FOR x
IN ( SELECT c.cust_first_name,
c.cust_last_name,
COUNT(*) expired_count,
c.email_address
FROM customers c JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'EXPIRED'
GROUP BY c.cust_first_name, c.cust_last_name)
LOOP
v_content := c_template;
v_content := REPLACE(v_content, '#FIRST_NAME#', x.cust_first_name);
v_content := REPLACE(v_content, '#LAST_NAME#', x.cust_last_name);
v_content := REPLACE(v_content, '#EXPIRED_COUNT#', x.expired_count);
v_connection :=
UTL_SMTP.open_connection(
HOST => 'smtp.gmail.com',
port => 587,
wallet_path => 'file:/home/oracle/mywallets/gmail',
-- password not needed because wallet is auto login
secure_connection_before_smtp => FALSE);
UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
UTL_SMTP.starttls(v_connection);
UTL_SMTP.auth(v_connection,
'sean.stuber@gmail.com',
:password,
'PLAIN');
UTL_SMTP.mail(v_connection, 'sean.stuber@gmail.com'); --from
UTL_SMTP.rcpt(v_connection, x.email_address); --- to
UTL_SMTP.open_data(v_connection);
UTL_SMTP.write_data(v_connection,
'From: sean.stuber@gmail.com' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
'To: ' || email_address || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
'Subject: Expired Orders' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,
'Content-Type: text/html' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, v_content);
UTL_SMTP.close_data(v_connection);
UTL_SMTP.quit(v_connection);
END LOOP;
END;
HTML content with embedded images
Next we’ll look at sending HTML with images. If you have your images on a server accessible by the recipient you can use <img> tags with a URL in the src attribute pointing to your web or file server. As long as the recipient can access the file on that server you don’t need to take any extra MIME steps in your email content as the image itself isn’t in the email body.
However, if you want to send an image as part of the email, then you will need to attach it. Furthermore, to have it rendered as part of the HTML, you will need to enclose it in multiple layers of MIME blocks. Each layer of has its own boundary string. The basic structure is to declare your email to be of type multipart/related. Each block within the related boundaries can reference another block that follows it within this MIME section.
An outline of 3 related blocks might look something like this…
multipart/related; boundary="related_boundary" --related_boundary first related mime block --related_boundary second related mime block --related_boundary third related mime block --related_boundary--
The HTML content here is a simple, a single line of text indicating that an image (a smiley face) should appear in a particular location within the text.
<html>
<head>
<meta charset="utf-8">
<title></title>
</head>
<body>
a small smiley face goes here ====> <img src="cid:0123456789"> <====
</body>
</html>
Note, the img tag src attribute references a Content-ID (cid) url rather than an http address or file path. This id must be defined as an attribute within one of the related blocks that follow the first (or root) block within the related content.
The Content-ID is an additional attribute line added to the block header; but otherwise the block structure is the same as attachment blocks we saw in prior articles. If you have multiple images to reference in your HTML you give each one its own Content-ID and the reference each of them with the cid: url.
--YtXlNKrPduGjLfjYhbMovEPWeRlFBQ Content-Type: image/jpeg Content-Transfer-Encoding: base64 Content-ID: <0123456789> Content-Location: smiley.jpg
Putting it together with the UTL_SMTP calls.
DECLARE
v_connection UTL_SMTP.connection;
c_mime_boundary CONSTANT VARCHAR2(256)
:= 'YtXlNKrPduGjLfjYhbMovEPWeRlFBQ' ;
v_clob CLOB;
v_len INTEGER;
v_index INTEGER;
v_raw_image RAW(2000)
:= HEXTORAW(
'FFD8FFE000104A46494600010101006000600000FFDB004300020101020101020'
|| '2020202020202030503030303030604040305070607070706070708090B090808'
|| '0A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB0043010202020'
|| '30303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C'
|| '0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC000110'
|| '8000A000A03012200021101031101FFC4001F0000010501010101010100000000'
|| '000000000102030405060708090A0BFFC400B5100002010303020403050504040'
|| '000017D01020300041105122131410613516107227114328191A1082342B1C115'
|| '52D1F02433627282090A161718191A25262728292A3435363738393A434445464'
|| '748494A535455565758595A636465666768696A737475767778797A8384858687'
|| '88898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C'
|| '4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7'
|| 'F8F9FAFFC4001F010003010101010101010101000000000000010203040506070'
|| '8090A0BFFC400B511000201020404030407050404000102770001020311040521'
|| '31061241510761711322328108144291A1B1C109233352F0156272D10A162434E'
|| '125F11718191A262728292A35363738393A434445464748494A53545556575859'
|| '5A636465666768696A737475767778797A82838485868788898A9293949596979'
|| '8999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4'
|| 'D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C030100021'
|| '10311003F00FD8FF8A1ADDE7FC241E3192DBE286BBA0DB2B6990ADADB5858CB36'
|| '813DACE93CB0DB43242D2DCBEA514C90E0ACB8C2F95B5D80AF6DAA17DE16D2F53'
|| 'D56DEFEE74DB0B8BEB4C791712DBA3CB0E338DAC4657193D0F7357E803FFFD9');
BEGIN
v_clob := encode_blob_as_base64_clob(v_raw_image);
v_connection :=
UTL_SMTP.open_connection(
HOST => 'smtp.gmail.com',
port => 587,
wallet_path => 'file:/u01/app/oracle/wallets/gmail',
-- password not needed because wallet is auto login
secure_connection_before_smtp => FALSE);
UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
UTL_SMTP.starttls(v_connection);
UTL_SMTP.auth(v_connection, 'sean.stuber@gmail.com', :password, 'PLAIN');
UTL_SMTP.mail(v_connection, 'sean.stuber@gmail.com'); --from
UTL_SMTP.rcpt(v_connection, 'sean.stuber@gmail.com'); --- to
UTL_SMTP.open_data(v_connection);
UTL_SMTP.write_data(v_connection,
'From: sean.stuber@gmail.com' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'To: sean.stuber@gmail.com' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Subject: test image' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'Content-Type: multipart/related; boundary="'
|| c_mime_boundary
|| '"'
|| UTL_TCP.crlf
|| UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'Content-Type: text/html; charset="utf-8"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, '
And that's all there is to using embedded images from one MIME block within the HTML of another MIME block. If you have multiple images, you can simply add them as additional blocks; each with their own content id and then reference them within the HTML.
While most clients will render HTML correctly, not all will. Either by limitation of the client itself, or by user preference. In the next article we'll look at another MIME structure to allow email content to be read even when the HTML is not processed.
The example code above includes a call to the encode_blob_as_base64_clob function. The code for it is provided here:
CREATE OR REPLACE FUNCTION encode_blob_as_base64_clob(p_blob IN BLOB)
RETURN CLOB
IS
-- The chunk size should be a large multiple of 3
-- to reduce the number of iterations needed.
c_chunk_size CONSTANT PLS_INTEGER := 21000;
v_len PLS_INTEGER;
v_index PLS_INTEGER;
v_chunk RAW(32767);
v_buffer VARCHAR2(32767);
v_clob CLOB;
BEGIN
v_len := DBMS_LOB.getlength(p_blob);
v_index := 1;
DBMS_LOB.createtemporary(v_clob, TRUE);
WHILE v_index <= v_len
LOOP
-- The algorithm encodes 3 input bytes as 4 single-byte characters
-- Thus we'll loop in chunks with a max size that is a multiple of 3.
-- We want it to be large to reduce the number of calls
-- but small enough that UTL_ENCODE.base64_encode can process them
-- and the output can then be converted to a varchar2
v_chunk := DBMS_LOB.SUBSTR(p_blob, c_chunk_size, v_index);
v_index := v_index + c_chunk_size;
v_buffer := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_chunk));
DBMS_LOB.writeappend(v_clob, LENGTH(v_buffer), v_buffer);
END LOOP;
RETURN v_clob;
END;