8 Replies Latest reply: Nov 15, 2012 5:21 AM by fac586 RSS

    Count the number of mail views

    archana2
      Hi All,

      I send an email which has following html code :

      <pre >
      <div xmlns="http://www.w3.org/1999/xhtml" class="blank-region">
      <b> <img width="100" height="100" src="
      https://xyz.com/pls/apex/Test.WCE_KM.download_my_files?p_id=22539227238556772"/>

      </b>


      This is the email text 1 <b> 1</b> times.



      This is the email text 2 <b>0</b> people.

      <div class="clear"/>
      </div>
      </pre>
      ------------------------------------------------------------------------------------------

      The code is inserted into outlook or thunderbird as a mail text .
      I would like to know how many people have opened that email. The idea is count the number of times the image is loaded in the mail box. and in the "download_my_files" procedure I write an insert statement to insert one count each time the image is loaded.

      Since it is inserted as a html text, I am not able to capture the image download hits or how many times people have opened the mail.

      Any idea how to achieve this ?

      Regards,
      Archana
        • 1. Re: Count the number of mail views
          AndyH
          >
          https://xyz.com/pls/apex/Test.WCE_KM.download_my_files?p_id=22539227238556772"/>
          I would like to know how many people have opened that email. The idea is count the number of times the image is loaded in the mail box. and in the "download_my_files" procedure I write an insert statement to insert one count each time the image is loaded.

          Since it is inserted as a html text, I am not able to capture the image download hits or how many times people have opened the mail.
          Are you using "download_my_files" to record the number of accesses for the files for later processing on the database server, or are you hoping to dynamically update your users email with that count?
          • 2. Re: Count the number of mail views
            archana2
            Thanks AndyH for the reply. I am using "download_my_files" just to record the number of accesses for the files for later processing on the database server.


            The mail body has few images which are stored in the table as a blob type. Those images are uploaded from the file browse button in the apex application.


            I use "download_my_files" to record the number of accesses for the image files. Who views those image files in their mail when they open the mail- download_my_files process should be executed to show the file in the mail and ideally it should enter a hit in my hits table.


            I use the following process to view a file in the browser as well in the mail body . I pass the file id as a parameter


            CREATE OR REPLACE PROCEDURE download_my_files(p_file in number) AS
            v_mime VARCHAR2(48);
            v_length NUMBER;
            v_file_name VARCHAR2(2000);
            Lob_loc BLOB;
            BEGIN
            SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
            INTO v_mime,lob_loc,v_file_name,v_length
            FROM oehr_file_subject
            WHERE id = p_file;
            --
            -- set up HTTP header
            --
            -- use an NVL around the mime type and
            -- if it is a null set it to application/octect
            -- application/octect may launch a download window from windows
            owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );

            -- set the size so the browser knows how much to download
            htp.p('Content-length: ' || v_length);
            -- the filename will be used by the browser if the users does a save as
            htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
            -- close the headers
            owa_util.http_header_close;
            -- download the BLOB
            wpg_docload.download_file( Lob_loc );
            INSERT INTO mail_hits(hitcount,viewdate) VALUES (1,SYSDATE);

            end download_my_files;
            /


            But when I add the html code as a text which includes image file links and some text, as I mentioned in my first post-- it is pasted as a content on the mail body. It doesn't contain the link to the file in the apex server.

            Is there any solution to track the hits in this method or You suggest any other method to calculate the mail count who has opened the email which has few images.


            Regards,
            Archana
            • 3. Re: Count the number of mail views
              Andy Hardy
              archana2 wrote:
              Thanks AndyH for the reply. I am using "download_my_files" just to record the number of accesses for the files for later processing on the database server.
              But when I add the html code as a text which includes image file links and some text, as I mentioned in my first post-- it is pasted as a content on the mail body. It doesn't contain the link to the file in the apex server.
              So, the problem has nothing to do with your 'download_my_files' package, it sounds like you are not creating your email correctly. What code are you using to do this?
              • 4. Re: Count the number of mail views
                archana2
                Andy,


                I open outlook mail -New mail message - insert a html file .While inserting i insert it as text.

                The html file contains the following code and some text message.

                <img width="100" height="100" src="
                https://xyz.com/pls/apex/Test.WCE_KM.download_my_files?p_id=22539227238556772"/>


                and the image appears properly in the message . If i send the same mail message to 10 people, and 9 ppl open that mail, i should get a count of 9 in my table <where i insert the count >.



                Regards,
                Archana
                • 5. Re: Count the number of mail views
                  fac586
                  archana2 wrote:

                  If i send the same mail message to 10 people, and 9 ppl open that mail, i should get a count of 9 in my table <where i insert the count >.
                  OK, how many of the original recipients have opened the mail when your table contains the following counts?

                  a. 9
                  b. 10
                  c. 11
                  d. 2
                  e. 3
                  f. 1032804
                  • 6. Re: Count the number of mail views
                    archana2
                    fac586,


                    For the time being we are trying to collect just the number of times an email is opened - if the counter says 10- means 10 times it is opened. It could be one person opened it ten times or ten people opened it one time ..We are not capturing the IP .As of now no unique hits are captured.

                    Regards,
                    Archana
                    • 7. Re: Count the number of mail views
                      archana2
                      Is there any other way to get the email count - how many of the recipient have opened the mail which has some text and images ? I am just trying all the options !!

                      Thanks in advance!

                      Regards,
                      Archana
                      • 8. Re: Count the number of mail views
                        fac586
                        archana2 wrote:

                        For the time being we are trying to collect just the number of times an email is opened - if the counter says 10- means 10 times it is opened. It could be one person opened it ten times or ten people opened it one time ..We are not capturing the IP .As of now no unique hits are captured.
                        Why? What use is this? It doesn't tell you that any of the original recipients have actually read it. A hit could be from someone who received an autoforward or from an anti-virus scanner.