Emailing rich text field in oracle apex

1
votes

Currently have an email process using apex_mail.send on a forms page that emails out the content of the form formatted in html, everything worked great until the users required the ability to format the text in one of the the form fields, so I switched the form type from text area to rich text editor.

Now when the form page is emailed out, that field now sends the html markup instead of formatted text. How can I make it so that the email sends that field out formatted as the user input it into the form?

The column in question is a vchar2 in the actual table.

Here's the offending code:

DECLARE
l_id number;
l_index number;
l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
L_BODY   VARCHAR2(32767);
lc_Message    VARCHAR2(4000);

BEGIN

L_BODY   := '<div><span style="text-align: center; font-family: calibri, ariel; font-size: 24px; font-weight: bold; color: #3270d3;">&P65_REQ_PRIORITY. at &P65_CLIENT!HTML. for a &P65_JOB!HTML. Has Been Updated.</span></div>';
L_BODY   := L_BODY ||'<div>&nbsp;';
L_BODY   := L_BODY ||'<table style="height: 60px; width: 710px;">';
L_BODY   := L_BODY ||'<tbody>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 355px; font-family: helvetica, ariel; font-size: 16px;"><strong>Date: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_DATE_WROTE!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 350px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Approved and Ready to Buy: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_READY!HTML. </span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 353px; font-family: helvetica, ariel; font-size: 16px;"><strong>Sales: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_SALES!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 353px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Req Priority: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_REQ_PRIORITY!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'</tbody>';
L_BODY   := L_BODY ||'</table>';
L_BODY   := L_BODY ||'<table style="height: 30px; width: 710px;">';
L_BODY   := L_BODY ||'<tbody>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 353px; font-family: helvetica, ariel; font-size: 16px;"><strong>Client: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_CLIENT!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 353px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Industry: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_CATEGORY!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'</tbody>';
L_BODY   := L_BODY ||'</table>';
L_BODY   := L_BODY ||'<table style="height: 30px; width: 710px;">';
L_BODY   := L_BODY ||'<tbody>';
L_BODY   := L_BODY ||'<tr style="height: 29.2833px;">';
L_BODY   := L_BODY ||'<td style="width: 351px; height: 29.2833px; font-family: helvetica,ariel; font-size: 16px;"><strong>City: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_CITY!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 160.317px; height: 29.2833px; font-family: helvetica,ariel; font-size: 16px;">&nbsp;<strong>State: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_STATE!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 190.683px; height: 29.2833px; font-family: helvetica,ariel; font-size: 16px;">&nbsp;<strong>Zip: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&P65_ZIP!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'</tbody>';
L_BODY   := L_BODY ||'</table>';
L_BODY   := L_BODY ||'<table style="height: 90px;" width="710">';
L_BODY   := L_BODY ||'<tbody>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 354.5px; font-family: helvetica, ariel; font-size: 16px;"><strong>Working With: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_WORKING_WITH!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 354.5px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Buying Process: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_BUY_PROC!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 354.5px; font-family: helvetica, ariel; font-size: 16px;"><strong>Position: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_JOB!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 354.5px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Number of Openings: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_DATE_WROTE!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 354.5px; font-family: helvetica, ariel; font-size: 16px;"><strong>Required Citizenship: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_CIT_STATUS!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 354.5px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Clearance Needed: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_CLEARED!HTML.</span></td>';
L_BODY   := L_BODY ||'<tr>&nbsp;</tr>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'</tbody>';
L_BODY   := L_BODY ||'</table>';
L_BODY   := L_BODY ||'<table style="height: 90px;" width="710">';
L_BODY   := L_BODY ||'<tbody>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 709px; font-family: helvetica, ariel; font-size: 16px;"><strong>Job Description: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;<html>&P65_DESCRIPTION!HTML.</html></span></p>';
L_BODY   := L_BODY ||'<p>&nbsp;</p>';
L_BODY   := L_BODY ||'</td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 709px; font-family: helvetica, ariel; font-size: 16px;"><strong>Minimum Qualifications and Desirables: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;<html>&P65_MIN_QUALS!HTML.</html><br /></span></p>';
L_BODY   := L_BODY ||'<p>&nbsp;</p>';
L_BODY   := L_BODY ||'</td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 709px; font-family: helvetica, ariel; font-size: 16px;"><strong>Notes: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;<html>&P65_NOTES!HTML.</html></span></p>';
L_BODY   := L_BODY ||'<p>&nbsp;</p>';
L_BODY   := L_BODY ||'</td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'</tbody>';
L_BODY   := L_BODY ||'</table>';
L_BODY   := L_BODY ||'<table style="height: 90px; width: 710px;">';
L_BODY   := L_BODY ||'<tbody>';
L_BODY   := L_BODY ||'<tr style="height: 29px;">';
L_BODY   := L_BODY ||'<td style="width: 354.5px; height: 29px; font-family: helvetica, ariel; font-size: 16px;"><strong>Bill Rate: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_RATE!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 353.5px; height: 29px; font-family: helvetica, ariel; font-size: 16px;"><strong>&nbsp;Overtime: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_OT_RATE!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr style="height: 29.9px;">';
L_BODY   := L_BODY ||'<td style="width: 354.5px; height: 29.9px; font-family: helvetica, ariel; font-size: 16px;"><strong>Contract Duration: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_DURATION!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 353.5px; height: 29.9px; font-family: helvetica, ariel; font-size: 16px;">&nbsp;<strong>Hours: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_HOURS!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr style="height: 29px;">';
L_BODY   := L_BODY ||'<td style="width: 354.5px; height: 29px; font-family: helvetica, ariel; font-size: 16px;"><strong>Shift Differential: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_SHIFT_DIFF!HTML.</span></td>';
L_BODY   := L_BODY ||'<td style="width: 353.5px; height: 29px; font-family: helvetica, ariel; font-size: 16px;"><strong>&nbsp;From Tip Written By: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;&P65_TIP_TO_REQ!HTML.</span></td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'</tbody>';
L_BODY   := L_BODY ||'</table>';
L_BODY   := L_BODY ||'</div>';

lc_Message :=               'Date Written   :' || :P65_DATE_WROTE || chr(10);
lc_Message := lc_Message || 'Sales          :' || :P65_SALES|| chr(10);
lc_Message := lc_Message || 'Client         :' || :P65_CLIENT|| chr(10);
lc_Message := lc_Message || 'City           :' || :P65_CITY|| chr(10);
lc_Message := lc_Message || 'State          :' || :P65_STATE|| chr(10);
lc_Message := lc_Message || 'Position       :' || :P65_JOB|| chr(10);
lc_Message := lc_Message || 'Type           :' || :P65_REQ_PRIORITY || chr(10);
lc_Message := lc_Message || 'Rate           :' || :P65_RATE || chr(10);
lc_Message := lc_Message || 'Overtime Rate  :' || :P65_OT_RATE || chr(10);
lc_Message := lc_Message || 'Description    :' || :P65_DESCRIPTION || chr(10);
lc_Message := lc_Message || 'Minimum Qualifications and Desireables         :' || :P65_MIN_QUALS || chr(10);
lc_Message := lc_Message || 'Notes          :' || :P65_NOTES || chr(10);

l_id := APEX_MAIL.SEND( 
p_to => '[email protected]', 
p_from => :P65_SALES,
p_subj => ''|| :P65_REQ_PRIORITY || ' at '  || :P65_CLIENT|| ' for a ' || :P65_JOB|| ' in ' || :P65_STATE|| ' Has Been Updated' || chr(10),
p_body_html => L_BODY,
p_body => lc_Message);
FOR c1 IN (SELECT filename, req, mimetype, pkey
FROM REQS
WHERE pkey = :P65_pkey
and REQ IS NOT NULL) loop

APEX_MAIL.ADD_ATTACHMENT(
p_mail_id => l_id,
p_attachment => c1.req,
p_filename => c1.filename,
p_mime_type => c1.mimetype);
END LOOP;

FOR c2 IN (SELECT filename2, exp_res, mimetype2, pkey
FROM REQS
WHERE pkey = :P65_pkey
and EXP_RES IS NOT NULL) loop

APEX_MAIL.ADD_ATTACHMENT(
p_mail_id => l_id,
p_attachment => c2.exp_res,
p_filename => c2.filename2,
p_mime_type => c2.mimetype2);
END LOOP;
COMMIT;
APEX_MAIL.PUSH_QUEUE();
END;
3
oracle-apexoracle-apex-5.1
please mention page item name in your code for which you have changed the type from text area to rich text editor - kapiell
I do apologize, it's P65_MIN_QUALS, P65_NOTES and P65_DESCRIPTION - greg
that helps .. :-) - kapiell

3 Answers

0
votes

Have you tried using dbms_lob.append + crlf at the end of each line?? This way you get an email with all the HTML formatting in place.

See sample code here:

begin l_body := '<div style="margin: 10px"> <h1 style="margin: 0 0 5px 0; padding: 10px; font: bold 18px/32px Arial, sans-serif; color: #EA0000; background-color: #F0F0F0; border-bottom: 2px solid #E6E6E6">'||l_app_name||'</h1> <p style="margin: 15px 0; font: normal 13px/18px Arial, sans-serif;padding: 0 10px">'||crlf; dbms_lob.append(l_body,'<p style="margin: 15px 0; font: normal 13px/18px Arial, sans-serif;padding: 0 10px">'||crlf); dbms_lob.append(l_body,' <table style="width: 100%;" cellspacing="0" cellpadding="0" border="0"> <tr> <th style="background-color: #CCC; color: #333; font: bold 11px/18px Arial, sans-serif; text-align: left; width: 10%; padding: 5px 10px">Event Date</th> </tr>'||crlf); dbms_lob.append(l_body,'</table></div>'||crlf); . . .

0
votes

You are almost there, don't escape HTML from rich text items as it will not get rendered. Remove !HTML from rich text item's substitution string. Here is modified version of your code from line #61 to line # 71,

L_BODY   := L_BODY ||'<td style="width: 709px; font-family: helvetica, ariel; font-size: 16px;"><strong>Job Description: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;<html>&P65_DESCRIPTION.</html></span></p>';
L_BODY   := L_BODY ||'<p>&nbsp;</p>';
L_BODY   := L_BODY ||'</td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 709px; font-family: helvetica, ariel; font-size: 16px;"><strong>Minimum Qualifications and Desirables: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;<html>&P65_MIN_QUALS.</html><br /></span></p>';
L_BODY   := L_BODY ||'<p>&nbsp;</p>';
L_BODY   := L_BODY ||'</td>';
L_BODY   := L_BODY ||'</tr>';
L_BODY   := L_BODY ||'<tr>';
L_BODY   := L_BODY ||'<td style="width: 709px; font-family: helvetica, ariel; font-size: 16px;"><strong>Notes: </strong><span style="font-family: helvetica, ariel; font-size: 16px;">&nbsp;<html>&P65_NOTES.</html></span></p>';

More information @ Oracle APEX Docs --> link

0
votes

Thanks for the input, but I ended up going about it the easy way.

Basically attached a separate email process for each sales field and then had they only fire off if the field isn't null.

I know it's not the approved way I'm sure, but it's easy and it works.