2 Replies Latest reply: Jun 24, 2013 12:10 PM by EmanEid RSS

    Events - Invitees

    EmanEid

      Hello all,

      I am a beginner user for Apex. I have been facing lots of issues and I would like to get some help.

      I am trying to set an event and invite people to this event based on their groups, so let's say I have 5 groups and I want to send this event to 4 groups. The application shall send emails to all the contacts from these groups.


      I send the email to send the invitations but it send to all valid emails for now.

       

      This is the email procedure I created:

       

      -----

       

      create or replace procedure "EMAIL_CONTACTS"

          (EVT_ID IN NUMBER, MSG OUT VARCHAR2)

      is

          begin

      declare

          CURSOR c2 IS

          SELECT CONTACT_EMAIL from CONTACTS;

          inv_bod  varchar2(200);

      begin

          SELECT inv_body into inv_bod from EVENTS_TABLE where EVENT_ID = EVT_ID;

       

          FOR con_email in c2

          LOOP

          HTMLDB_MAIL.SEND(

          P_TO => con_email.CONTACT_EMAIL,

          P_FROM => 'email@email.com',

          P_BODY => INV_BOD,

          P_SUBJ => 'subject line'

          );

       

          MSG := 'invitations sent on ' || SYSDATE;

       

      END LOOP;

      exception

          when others then

      --    raise_application_error(-002, 'Sending Email Faild');

          MSG := 'Sending Email Faild';

      end;

      end;

       

      -----

      contacts

      Column NameData TypeNullableDefaultPrimary Key
      CONTACT_IDNUMBERNo-1
      CONTACT_FNAMEVARCHAR2(25)No--
      CONTACT_NUMBERNUMBER(12,0)No--
      CONTACT_EMAILVARCHAR2(50)Yes--
      CONTACT_ADDRESSVARCHAR2(150)Yes--
      COMPANY_IDNUMBERYes--
      GROUP_IDNUMBERYes--
      CONTACT_LNAMEVARCHAR2(25)No--
      CONTACT_TITLEVARCHAR2(10)Yes--


      events_table

       

      Column NameData TypeNullableDefaultPrimary Key
      EVENT_IDNUMBERNo-1
      EVENT_TITLEVARCHAR2(40)No--
      EVENT_DATEDATENo--
      EVENT_LOCATIONVARCHAR2(40)No--
      INV_BODYVARCHAR2(300)Yes--
      INV_BLOBBLOBYes--
      MIMETYPEVARCHAR2(255)Yes--
      FILENAMEVARCHAR2(400)Yes--
      IMAGE_LAST_UPDATETIMESTAMP(6) WITH LOCAL TIME ZONEYes--


      groups_table

      Column NameData TypeNullableDefaultPrimary Key
      GROUP_IDNUMBERNo-1
      GROUP_DESCRIPTIONVARCHAR2(100)Yes--
      GROUP_NAMEVARCHAR2(20)No--


       

      Message was edited by: EmanEid

        • 1. Re: Events - Invitees
          Mike Kutz

          EmanEid wrote:

           

          I send the email to send the invitations but it send to all valid emails for now.

           


           

          Take a look at the SELECT statement that retrieves the contacts:

          SELECT CONTACT_EMAIL from CONTACTS;


          There is no WHERE clause there.

          Also, I would add a DISTINCT so you don't send the e-mail to the same person twice if they belong to two different groups.

           

          SELECT distinct CONTACT_EMAIL
          FROM CONTACTS
          WHERE GROUP_ID != -- THE GROUP to NOT send to
          


          MK

          • 2. Re: Events - Invitees
            EmanEid

            Thank you for your reply.