Oracle APEX Setting up SMTP with office 365

 Make sure to download the bundle certificate from Microsoft website:

In google, search for "Microsoft 365 Root Certificate Chain Bundle - Worldwide"

mkdir /home/oracle/wallet/Office365

orapki wallet create -wallet /home/oracle/wallet/Office365 -pwd xx -auto_login

orapki wallet add -wallet /home/oracle/wallet/Office365 -trusted_cert -cert m365_root_certs_20220331.p7b

orapki wallet display -wallet /home/oracle/wallet/Office365 -pwd xx

BEGIN

  -- Allow SMTP access for user APPS

  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

    host => 'smtp.office365.com',

    lower_port => 587,

    upper_port => 587,

    ace => xs$ace_type(privilege_list => xs$name_list('SMTP'),

                       principal_name => 'APPS',

                       principal_type => xs_acl.ptype_db));

END;

/

DECLARE

 crlf varchar2(2) := UTL_TCP.crlf;

 connection UTL_SMTP.connection; 

 smtpServer varchar2(100) := 'smtp.office365.com';

 emailSubject varchar2(100) := 'Test Generic Email Subject'; 

 -- Set sender email

 smtpUsername varchar2(100) := 'YourSMTPmail';

 -- Set sender email password

 smtpPassword varchar2(100) := 'YourSMTPpassword';

 -- Set destination email

 emailDestination varchar2(100) := '';

 -- Set Oracle Wallet path

 walletPath varchar(100) := 'file:/home/oracle/wallet/Office365';

 -- set Oracle Wallet password

 walletPassword varchar2(100) := 'WalletPassword'; 

BEGIN  

  connection := UTL_SMTP.open_connection(

    host => smtpServer,

    port => 587,

    wallet_path => walletPath,

    wallet_password => walletPassword,

    secure_connection_before_smtp => FALSE);

    

  UTL_SMTP.ehlo(connection, smtpServer);

  UTL_SMTP.starttls(connection);

  UTL_SMTP.ehlo(connection, smtpServer);  

  UTL_SMTP.auth(

    c => connection,

    username => smtpUsername,

    password => smtpPassword,

    schemes => utl_smtp.all_schemes);    

  UTL_SMTP.mail(connection, smtpUsername);  

  UTL_SMTP.rcpt(connection, emailDestination);  

  UTL_SMTP.open_data(connection);

  UTL_SMTP.write_data(connection, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf);

  UTL_SMTP.write_data(connection, 'From: ' || smtpUsername || crlf);

  UTL_SMTP.write_data(connection, 'Subject: ' || emailSubject || crlf);

  UTL_SMTP.write_data(connection, 'To: ' || emailDestination || crlf);

  UTL_SMTP.write_data(connection, '' || crlf);

  FOR i IN 1 .. 10

  LOOP

    UTL_SMTP.write_data(connection, 'This is only a test line. ' || TO_CHAR (i) || crlf);

  END LOOP;

  UTL_SMTP.close_data(connection);

  UTL_SMTP.quit(connection);

END;

/