Andrew Fraser DBA

HTML formatted emails with utl_smtp

HTML format emails allows images, fonts, colours, hyperlinks. It can be done with utl_smtp (after the jump) and with utl_mail

-- ask which environment this is, controls who will actually get the emails
-- html code needs to use ampersands, so switch define to instead be another character that isnt used anywhere else
set define }
ACCEPT env PROMPT "Enter Environment (DEV/TEST/LIVE): "

create or replace
PROCEDURE send_mail_cond_reminder
/*
|| Andrew Fraser
|| 8-Jul-2010
|| Sends email html format using utl_smtp
*/
AS
   SENDER   constant VARCHAR2(80) := 'andrew.fraser@mysite.com (Fraser, Andrew)';
   MAILHOST constant VARCHAR2(80) := 'mailhost.mysite.com';
   mail_conn utl_smtp.connection;
   V_CL      constant VARCHAR2(2) := CHR(13)||CHR(10);
   l_rcpt    VARCHAR2(80);
   l_subject VARCHAR2(80)         := 'Email Subject Here';
   l_mesg    VARCHAR2(9900);
   l_body    VARCHAR2(9000);
   CURSOR c_student
   IS
      SELECT s.s_ref,
         s.s_e_mail_address ,
         s.s_forename_1,
         s.s_surname
      FROM students s,
         e_user_com c,
         e_user_id i,
         ab_reg a
      WHERE s.s_ref = i.s_ref
      AND i.s_ref   = c.s_ref
      AND s.s_e_mail_address IS NOT NULL
      -- Check student has reg'd this year
      AND i.ereg_year = a.ab_r_year
      -- Report just for people who have not yet had an email sent to them
      AND c.password_set_datetime IS NOT NULL
      AND c.cond_reminder_email_sent IS NULL
      ORDER BY 1 FOR UPDATE OF c.cond_reminder_email_sent ;
BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25) ;
   utl_smtp.helo(mail_conn, MAILHOST) ;
   FOR d_student IN c_student
   LOOP
      dbms_output.put_line('Sending Email to : ' || d_student.s_e_mail_address ) ;
      BEGIN
         l_body :=

'< html>< head>< title>Email Title Here< /title>< /head>< body>'||v_cl||v_cl||
'< table width="100%" cellpadding="10" cellspacing="0">'||v_cl||v_cl||
'< tr>'||v_cl||v_cl||
'< span style=''font-family:Calibri,Arial,Helvetica,sans-serif''>'||v_cl||v_cl||

'< p>Dear '|| INITCAP(d_student.s_forename_1) ||' '|| INITCAP(d_student.s_surname)||','||v_cl||v_cl||

'< br>< br>There have been a number of significant changes to service over the summer and this E-mail is to make sure you are aware of them:'||v_cl||v_cl||

'< span style=''color: DarkBlue''>'||v_cl||v_cl||
'< ul>< li>Revised opening hours and function of the reception window'||v_cl||v_cl||
'< li>Colour printing'||v_cl||v_cl||
'< li>Changes to FreePCs display'||v_cl||v_cl||
'< li>Windows 7'||v_cl||v_cl||
'< li>Report problems with wireless or we can''t fix'||v_cl||v_cl||
'< li>Power saving'||v_cl||v_cl||
'< li>Backing up your data / work'||v_cl||v_cl||
'< li>Illegal copyright downloads'||v_cl||v_cl||

'< /ul>< /span>< span style=''font-weight:bold; color: SteelBlue; font-family:Cambria,Calibri,Arial,Helvetica,sans-serif''>'||v_cl||v_cl||
'< p>Revised opening hours and function of the reception window< /span>'||v_cl||v_cl||

'< br>< br>From 5th July 2010 the reception window will open 08:30 - 10:30 and 12:30 - 14:30 and only to allow for cash payments to top up print budgets; the minimum charge for this is still £3.  Printing budgets can still be topped up via the portal or at Library at weekends or after 6pm weekdays. ( Full details about printing can be found at: < a href="http://mysite.com/print/index.php">mysite.com/student/print/index.php< /a> ). All other queries should be directed to the ServiceDesk instead of the reception window ( further details on the ServiceDesk can be found at: < a href="http://mysite.com/servicedesk/index.php">mysite.com/servicedesk/index.php< /a> )'||v_cl||v_cl||

'< span style=''font-weight:bold; color: SteelBlue; font-family:Cambria,Calibri,Arial,Helvetica,sans-serif''>'||v_cl||v_cl||
'< br>< br>Colour printing service changes< /span>'||v_cl||v_cl||

'< br>< br>As the reception window is now only available for topping up print budgets the colour printer has been moved and is now accessed via a new colour classroom printstations holding queue. There are checks in place to ensure that you don''t try to print colour output on a monochrome printer and vice versa. Further details are available at: < a href="http://mysite.com/student/news/index_1873.php">mysite.com/student/news/index_1873.php< /a>'||v_cl||v_cl||

'< span style=''font-weight:bold; color: SteelBlue; font-family:Cambria,Calibri,Arial,Helvetica,sans-serif''>'||v_cl||v_cl||
'< br>< br>Information on booked and free classrooms< /span>'||v_cl||v_cl||

'< br>< br>In the annual survey to students a number of you commented on the fact that often the notices on the door of many PC classrooms were incorrect / out of date and also that there was no way of knowing if a room was booked, or any machines were free without going to the relevant room. In response to these comments we have looked at this and decided that we will replace the paper notices with screen displays in a number of buildings. These displays will provide information on which rooms are free or booked and also how many machines are currently being used in each room. The course code, where available, for booked rooms will be shown as well as the duration or time of next booking. To see what the display looks like see: < a href="http://mysite.com/freepcs/">mysite.com/freepcs/< /a>'||v_cl||v_cl||

'< span style=''font-weight:bold; color: SteelBlue; font-family:Cambria,Calibri,Arial,Helvetica,sans-serif''>'||v_cl||v_cl||
'< br>< br>Windows 7 and new machines< /span>'||v_cl||v_cl||

'< br>< br>From August 2010 we will be installing the new classroom image which will be based on Windows 7 so that on your return you will see the new, improved classroom images. We will also etc etc etc etc etc'||v_cl||v_cl||

'< br>< br>< i>Best wishes'||v_cl||v_cl||
'< br>< br>You name here'||v_cl||v_cl||
'< br>< a href="http://mysite.com/">Title< /a> ( Title ) - < a href="http://mysite.com/slo/">Full Title< /a>'||v_cl||v_cl||
'< /i>< /p>< /span>< /table>< /body>< /html>' ;

         l_mesg := 'Date: '||TO_CHAR(sysdate,'dd Mon yy hh24:mi:ss')||V_CL||
             'From: < '||SENDER||'>'||V_CL||
             'Subject: '||l_subject||V_CL||
             'To: '|| d_student.s_e_mail_address ||v_cl||
             'MIME-Version: 1.0'||V_CL||
             'Content-type:text/html;charset=iso-8859-1'||V_CL||
             ''||v_cl||l_body ;
         utl_smtp.mail(mail_conn, SENDER) ;

         if upper('}env') = 'DEV' then
             l_rcpt := 'andrew.fraser@mysite.com';               -- For Development
         elsif upper('}env') = 'TEST' then
             l_rcpt := 'tester@mysite.com';         -- For Test
         else
             l_rcpt := d_student.s_e_mail_address;
         end if;
         utl_smtp.rcpt(mail_conn, l_rcpt) ;

         utl_smtp.data(mail_conn, l_mesg) ;
         -- Update email_sent field so that we do not email this student again
         UPDATE e_user_com
            SET cond_reminder_email_sent = SYSDATE
            WHERE CURRENT OF c_student ;
      EXCEPTION
         WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
            NULL ;
         WHEN OTHERS THEN
            dbms_output.put_line('Error Code : ' || SQLCODE) ;
            dbms_output.put_line('Error Message : ' || SQLERRM) ;
            EXIT ;
      END ;
   END LOOP ;
   utl_smtp.quit(mail_conn) ;
END ;
/