1 2 3 Previous Next 34 Replies Latest reply: May 9, 2009 3:59 AM by Sunil Bhatia Go to original post RSS
      • 15. Re: Incorporating Tree
        Sunil Bhatia
        yes andy,

        actually want to send the email at this instant only. But as in other blog, just want to save the sent items and the data sent in the emails. Dont want this functionality to send email later or once in a day. I just want to send email as soon as the button send email hits, and if any of the checkbox is not selected, simple error message in alertbox or any other alternative comes which says "no employee selected"

        Thanks andy
        Sunil Bhatia
        • 16. Re: Incorporating Tree
          ATD
          OK

          I'll work on the basis that a table is just for an email history

          Andy
          • 17. Re: Incorporating Tree
            Sunil Bhatia
            thanks dear for this awesome help of urs

            Sunil Bhatia
            • 18. Re: Incorporating Tree
              ATD
              Hi,

              np - I will need an email address to send the emails to. Either post one here or send one to ................

              Andy
              • 19. Re: Incorporating Tree
                Sunil Bhatia
                hi andy,

                i will agree if u can send it to ur email id, because i will be unable to check my personal email here. Still if u cant, u can send it to "sun.bhatia21@gmail.com", bt if you can send email to ur personal id and test there, and here i will replace ur personal id to my official id. Will that work??? Sorry dear,

                Thanks
                SunilBhatia
                • 20. Re: Incorporating Tree
                  Sunil Bhatia
                  ok....

                  i have talked to network department. If u r not testing on ur email id, u can test it on sun.bhatia21@gmail.com

                  Sorry for late reply,

                  Sunil Bhatia
                  • 21. Re: Incorporating Tree
                    ATD
                    Hi,

                    OK - I'll send emails to my own account to test things. I can't let you have access to that. Once tested, do you want me to switch to your gmail account?

                    Andy
                    • 22. Re: Incorporating Tree
                      Sunil Bhatia
                      hey andy,

                      its fyn, if the testing of mails is successful at ur email id, then it will work fine for me too. I just want how u did it, i mean the actual coding of how u looped through on all checkboxes and the table structure based on which history saved thing was done. Thanks for so much help.

                      Thanks
                      Sunil Bhatia
                      • 23. Re: Incorporating Tree
                        ATD
                        Hi,

                        OK - the functionality works ok. I've just sent one email and have now forwarded this to you at your gmail account.

                        Andy
                        • 24. Re: Incorporating Tree
                          Sunil Bhatia
                          yupzzz

                          i have recieved an email on my account. its working fine, you have sent 1 email, thats it?? or multiple emails?

                          With Regards,
                          Sunil Bhatia
                          • 25. Re: Incorporating Tree
                            Sunil Bhatia
                            hi andy,

                            Whenever possible at your end, do tell me how u implemented these things at your end, plus the table structure which u did for it.

                            I will implement it at my end, and will reply after implementing it. Please have your lunch, as you are with me right from the morning, and i really appreciate your help

                            Thanks a lot andy,
                            Sunil Bhatia
                            • 26. Re: Incorporating Tree
                              ATD
                              Hi,

                              OK - I've updated the page to include a field for you to enter in an email address. Try the page, enter in your own email address and then check whatever employees you like.

                              I have just done this and received several emails.

                              Andy
                              • 27. Re: Incorporating Tree
                                Sunil Bhatia
                                hi andy,

                                yups i have tested it,

                                it is working absolutely fine at my end. All the emails are perfect.

                                Brilliant andy

                                Regards,
                                Sunil Bhatia
                                • 28. Re: Incorporating Tree
                                  ATD
                                  Hi Sunil,

                                  Yeah! OK - I've hidden the email addresses in the report so users can not see these.

                                  This is what I've done on the page.

                                  Firstly, I have created the following in SQL (this was uploaded as a script and then run):
                                  CREATE TABLE "SENT_EMAILS"
                                  (
                                       "SENT_EMAIL_ID" NUMBER(10,0) NOT NULL ENABLE,
                                       "EMPNO" NUMBER(4,0),
                                       "EMAIL_TYPE" VARCHAR2(4),
                                       "EMAIL_TO" VARCHAR2(200),
                                       "EMAIL_FROM" VARCHAR2(200),
                                       "EMAIL_BODY" VARCHAR2(2500),
                                       "EMAIL_BODY_HTML" VARCHAR2(2500),
                                       "EMAIL_SUBJECT" VARCHAR2(200),
                                       "EMAIL_CC" VARCHAR2(200),
                                       "EMAIL_BCC" VARCHAR2(200),
                                       "EMAIL_SENT" DATE,
                                       CONSTRAINT "SENT_EMAILS_PK" PRIMARY KEY ("SENT_EMAIL_ID") ENABLE
                                  )
                                  /
                                  CREATE SEQUENCE "SENT_EMAIL_ID_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE
                                  /
                                  CREATE OR REPLACE TRIGGER "SENT_EMAILS_BI"
                                  BEFORE
                                  INSERT ON "SENT_EMAILS"
                                  FOR EACH ROW
                                  DECLARE
                                       v_id NUMBER;
                                  BEGIN
                                       BEGIN
                                            IF :NEW.SENT_EMAIL_ID IS NULL THEN
                                                 SELECT SENT_EMAIL_ID_SEQ.nextval INTO v_id FROM dual;
                                                 :NEW.SENT_EMAIL_ID := v_id;
                                            END IF;
                                       END;
                                  END;
                                  /
                                  ALTER TRIGGER  "SENT_EMAILS_BI" ENABLE
                                  /
                                  This just creates by SENT_EMAILS table and related objects.

                                  I've added a new item on my page called P76_EMAIL_ADDRESS - this is just so that I don't have to hardcode an email address into the process (otherwise, I'm likely to receive loads of test emails!)

                                  Then, on my page, I created a new PL/SQL process called P76_SEND_EMAILS. The process is set to run "On Submit - After Computations and Validations" and uses the following Process code:
                                  DECLARE
                                   vSELECTED VARCHAR2(1000);
                                   vEMPNO NUMBER;
                                   vENAME VARCHAR2(100);
                                   vEMAIL VARCHAR2(100);
                                  BEGIN
                                   vSELECTED := APEX_UTIL.TABLE_TO_STRING(APEX_APPLICATION.G_F01);
                                   :P76_SELECTED := vSELECTED;
                                   IF APEX_APPLICATION.G_F01.COUNT = 0 THEN
                                    RAISE_APPLICATION_ERROR(-20001, 'No employees selected!');
                                   END IF;
                                   vEMAIL := :P76_EMAIL_ADDRESS;
                                   IF vEMAIL IS NULL THEN
                                    RAISE_APPLICATION_ERROR(-20001, 'Email address required');
                                   END IF;
                                   FOR x IN 1..APEX_APPLICATION.G_F01.COUNT
                                   LOOP
                                    vEMPNO := APEX_APPLICATION.G_F01(x);
                                    SELECT ENAME INTO vENAME FROM EMP WHERE EMPNO = vEMPNO;
                                    INSERT INTO SENT_EMAILS (EMPNO, EMAIL_TYPE, EMAIL_TO, EMAIL_FROM, EMAIL_BODY, EMAIL_BODY_HTML, EMAIL_SUBJECT, EMAIL_CC, EMAIL_BCC, EMAIL_SENT)
                                    VALUES (vEMPNO, '76', vEMAIL, vEMAIL, 'Email for ' || vENAME || ' EMPNO ' || vEMPNO, 'Email for ' || vENAME || ' EMPNO ' || vEMPNO, 'Test email', null, null, SYSDATE);
                                    HTMLDB_MAIL.SEND
                                    (
                                    vEMAIL,
                                    vEMAIL,
                                    'Email for ' || vENAME || ' EMPNO ' || vEMPNO,
                                    'Email for ' || vENAME || ' EMPNO ' || vEMPNO,
                                    'Test email',
                                    '',
                                    ''
                                    );
                                   END LOOP;
                                   APEX_MAIL.PUSH_QUEUE();
                                  END;
                                  The vSELECTED lines are not required for normal use - this was just to get a string containing the selected EMPNO values for display purposes. You can remove these lines.

                                  The code first checks if there are any items checked by the user - if there are none, an error is raised. This displays on an error page - the user has to click OK on that page to return to the tree. This stops any further code from running.

                                  It also checks for a value in P76_EMAIL_ADDRESS - though this could be done using Validation if required.

                                  If there are items checked, the next part loops through these and gets the EMPNO (as the checkboxes have a "value" attribute that is the EMPNO for each item, this is the value submitted and stored in the APEX_APPLICATION.G_F01 collection.

                                  We then use the EMPNO value to retrieve the ENAME for the employee.

                                  Finally, a record is added into the SENT_EMAILS table and the email itself is constructed and put into the email queue. Finally, the queue is pushed to trigger the actual sending of the emails by the server.



                                  Andy
                                  • 29. Re: Incorporating Tree
                                    Sunil Bhatia
                                    oh wow andy,

                                    will reply in 5-10 min as i am trying to do the steps which you have taken.

                                    Thanks a lot

                                    Sunil Bhatia