DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Sendmail with attachment

  1. #1
    Join Date
    Mar 2001
    Posts
    188

    Question

    Hello gurus
    i use the package utl_smtp for sending mails if there is a risc Ã*n the database. Now i want more. Can i send an email with an attachment to me.
    Is it possible and if it is possible. How will I do it.
    If there is one in the world who work with this.
    Can i get a small example.

    You can send me a mail to thomas_schmidt@eplus-online.de

    Thanks for your effords
    Regards
    Thomas Schmidt

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    Thomas,

    Doubt this will help, but you sounded quite desperate, so it's worth a try.

    I wrote a Package in Forms 5, which sends mails through outlook, it allows to to do attachements and multiple recipients. I enclose the package just in case you are running the DB on NT or 2000 and this will work, you never know. I also enclose at the end an example of how to call it.

    Hope this is of some help.



    PACKAGE outlook_mail IS
    PROCEDURE create_mail(p_subject VARCHAR2,
    p_body VARCHAR2,
    p_attachment VARCHAR2 := NULL);
    --
    PROCEDURE add_email(p_email VARCHAR2,
    p_type NUMBER);
    --
    PROCEDURE send;
    --
    happlication oleobj;
    hmailitem oleobj;
    hrecipients oleobj;
    hrecipient oleobj;
    hattachments oleobj;
    hattachment oleobj;
    END;

    PACKAGE BODY outlook_mail IS
    PROCEDURE create_mail(p_subject VARCHAR2,
    p_body VARCHAR2,
    p_attachment VARCHAR2 := NULL) IS
    BEGIN
    --
    -- Create the initial connection to Outlook
    --
    happlication := CREATE_OLEOBJ('Outlook.Application', TRUE);
    INIT_OLEARGS(1);
    ADD_OLEARG(0);
    --
    -- Create a mail item
    --
    hmailitem := call_ole_obj(happlication, GET_OLE_MEMBERID(happlication, 'CreateItem'));
    --
    -- Put in the subject
    --
    set_ole(hmailitem, get_ole_memberid(hmailitem, 'Subject'), p_subject);
    --
    -- Put in the body
    --
    set_ole(hmailitem, get_ole_memberid(hmailitem, 'Body'), p_body);
    --
    -- This bit inserts an attachement
    --

    IF p_attachment IS NOT NULL THEN
    hattachments := get_ole_obj(hmailitem, get_ole_memberid(hmailitem, 'Attachments'));
    init_oleargs(1);
    add_olearg(p_attachment);
    hattachment := call_ole_obj(hattachments, get_ole_memberid(hattachments, 'Add'));
    END IF;
    END;
    --
    --
    --
    PROCEDURE add_email(p_email VARCHAR2,
    p_type NUMBER) IS
    BEGIN
    --
    -- Create a recipient item
    --
    hrecipients := get_ole_obj(hmailitem, get_ole_memberid(hmailitem, 'Recipients'));
    --
    -- The four lines below create an entry in the to list, use them multiple
    -- times to create a list of many people
    --
    init_oleargs(1);
    add_olearg(p_email);
    hrecipient := call_ole_obj(hrecipients, get_ole_memberid(hrecipients, 'Add'));
    set_ole(hrecipient, get_ole_memberid(hrecipient, 'Type'), p_type);
    END;
    --
    --
    --
    PROCEDURE send IS
    BEGIN
    --
    -- This is the bit to send it.
    --
    init_oleargs(0);
    call_ole(hmailitem, get_ole_memberid(hmailitem, 'Send'));
    END;
    END;




    PROCEDURE send_mail(pn_mail_id NUMBER) IS
    CURSOR temp_cursor IS
    SELECT c.email
    FROM p_contacts c,
    p_comps_mailing_lists l
    WHERE l.mail_id = pn_mail_id
    AND c.contact_id = l.pcon_contact_id
    AND c.email IS NOT NULL;
    --
    v_subject VARCHAR2(255);
    v_body VARCHAR2(2000);
    v_to VARCHAR2(100);
    v_cc VARCHAR2(100);
    v_attachment VARCHAR2(255) := '';
    v_contact VARCHAR2(100);
    v_cc1 VARCHAR2(100):= '';
    BEGIN
    v_subject := :cb.subject;
    v_body := :cb.body;
    v_attachment := :cb.attachment;
    --
    FOR report_loop IN temp_cursor LOOP
    IF v_attachment is null THEN
    outlook_mail.create_mail(v_subject, v_body);
    ELSE
    outlook_mail.create_mail(v_subject, v_body, v_attachment);
    END IF;
    --
    v_contact := report_loop.email;
    IF v_contact IS NOT NULL THEN
    outlook_mail.add_email(v_contact, 1);
    END IF;
    --
    IF v_cc1 IS NOT NULL THEN
    outlook_mail.add_email(v_cc1, 2);
    END IF;
    --
    outlook_mail.send;
    END LOOP;
    END;

  3. #3
    Join Date
    Mar 2001
    Posts
    188
    Hi m1l
    this is a good note. I have don't say on which system our db runs. Unfortunaly our system runs under linux with the oracle enterprice edition 8.1.7.

    But i will try my best to get a connection to the outlook server.

    Thomas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width