24 Replies Latest reply on Apr 5, 2007 12:22 AM by craig_mac

    email insert

    craig_mac Level 1
      Gday,

      just wondering if there is a way of inserting the body of an email into a database table.

      i will have up to 1000 emails coming in at one time, all with the same fields of information, i can not use http form, this need to come in via mail server.

      so what i was thinking if i recieve an email to a specific email address is there a way of then maybe pushing the email to a http page where the body can get extracted then inserted in to my table?
        • 2. Re: email insert
          craig_mac Level 1
          Thanks jdeline

          i have checked that out and it will work well but how will i seperate the body ie

          my body of every email will be formated like this

          message:message text here
          to:number here
          nm:user here
          acc:user pass here

          so i will need to set variables for

          message:
          to:
          nm:
          acc:

          so the text that follows the colon
          • 3. Re: email insert
            jdeline Level 1
            If I understand you correctly, you will need to do a screen scrape. If "message text here", "number here", "user here" and "user pass here" all end in ASCII 10 (new line), do the following to get "message text here":

            <CFSET found = FindNoCase("message:" emailBody)>
            <CFSET found2 = FindNoCase("#Chr(10)#", emailBody, found)>
            <CFSET messageTextHere = Mid(emailBody, found + 9, found2 - found - 9)>

            Repeat this process for the remaining fields.
            • 4. email insert
              craig_mac Level 1
              ok i have tried that but get this error below, i am using mysql

              an expression beginning with "FindNoCase", on line 17, column 16.This message is usually caused by a problem in the expressions structure.
              a CFSET tag beginning on line 17, column 2.
              a CFSET tag beginning on line 17, column 2.

              also the code is below do i have to change the chr 10 ? for each? not sure what this is

              <CFSET found = FindNoCase("message:" emailBody)>
              <CFSET found2 = FindNoCase("#Chr(10)#", emailBody, found)>
              <CFSET message = Mid(emailBody, found + 9, found2 - found - 9)>

              <CFSET founda = FindNoCase("to:" emailBody)>
              <CFSET founda2 = FindNoCase("#Chr(10)#", emailBody, founda)>
              <CFSET toa = Mid(emailBody, founda + 3, founda2 - founda - 3)>

              <CFSET foundb = FindNoCase("nm:" emailBody)>
              <CFSET foundb2 = FindNoCase("#Chr(10)#", emailBody, foundb)>
              <CFSET username: = Mid(emailBody, foundb + 3, foundb2 - foundb - 3)>

              <CFSET foundc = FindNoCase("acc:" emailBody)>
              <CFSET foundc2 = FindNoCase("#Chr(10)#", emailBody, foundc)>
              <CFSET password = Mid(emailBody, foundc + 4, foundc2 - foundc - 4)>
              • 5. Re: email insert
                jdeline Level 1
                You need to use the NAME="queryname" in your CFPOP attribute list. Then change emailBody to queryName.body.
                • 6. email insert
                  craig_mac Level 1
                  ok yes i tried that but still get the same error i have



                  <CFSET found = FindNoCase("message:" email.Body)>
                  <CFSET found2 = FindNoCase("#Chr(10)#", email.Body, found)>
                  <CFSET message = Mid(email.Body, found + 9, found2 - found - 9)>
                  • 7. Re: email insert
                    jdeline Level 1
                    <CFSET found = FindNoCase("message:" email.Body)> is missing a comma after "message:"
                    • 8. Re: email insert
                      craig_mac Level 1
                      ok thanks that does work but when i change the text of the fields i get an error ie

                      message:message text here
                      to:61421365488
                      nm:keith
                      acc:keith

                      i get this error
                      Diagnostics: Parameter 3 of function Mid which is now -58 must be a non-negative integer
                      The error occurred on line 31.
                      • 9. Re: email insert
                        jdeline Level 1
                        Post the entire error message, including the code fragment showing line 31.
                        • 10. email insert
                          craig_mac Level 1
                          ok the error code is
                          Parameter 3 of function Mid which is now -58 must be a non-negative integer


                          The error occurred in IncomeEmail.cfm: line 31

                          29 : <CFSET foundc = FindNoCase("acc:", email.Body)>
                          30 : <CFSET foundc2 = FindNoCase("#Chr(10)#", email.Body, foundc)>
                          31 : <CFSET password = Mid(email.Body, foundc + 4, foundc2 - foundc - 4)>
                          32 :
                          33 : <cfoutput>


                          • 11. Re: email insert
                            jdeline Level 1
                            A couple of things...

                            (1) I think you should assure that ASCII 10 is at the end of each of your lines. The code below will display the ASCII values for your entire e-mail body.

                            (2) Also, doing your FindNoCase( ) functions from the very beginning of the body works only for the first line. The code below should fix that issue.
                            • 12. Re: email insert
                              craig_mac Level 1
                              nope still the same error

                              when i have in the email body the code below it works fine but when i change the acc: to keith i get the error

                              message:message text here
                              to:number here
                              nm:user here
                              acc:user pass here
                              • 13. email insert
                                jdeline Level 1
                                Did you run the code in (1) above to verify you have a ASCII 10 at the end of each line? Please post the output.

                                Also, <CFSET foundc on line 29 needs fixing:
                                <CFSET foundc = FindNoCase("acc:" emailBody, foundb2 + 1)>
                                • 14. email insert
                                  craig_mac Level 1
                                  ok this is what i have

                                  <CFLOOP INDEX="i" FROM="1" TO="#Len(email.Body)#">
                                  #Mid(email.Body, i, 1)# (#Asc(Mid(email.Body, i, 1))#)
                                  </CFLOOP>

                                  • 15. Re: email insert
                                    jdeline Level 1
                                    Whoops. You need to put <CFOUTPUT></CFOUTPUT> around the code that displays the ASCII.
                                    • 16. Re: email insert
                                      craig_mac Level 1
                                      ok still get the error the output is
                                      m (109) e (101) s (115) s (115) a (97) g (103) e (101) : (58) m (109) e (101) s (115) s (115) a (97) g (103) e (101) (32) t (116) e (101) x (120) t (116) (32) h (104) e (101) r (114) e (101) (13) (10) t (116) o (111) : (58) 6 (54) 1 (49) 4 (52) 2 (50) 1 (49) 3 (51) 6 (54) 5 (53) 4 (52) 8 (56) 8 (56) (13) (10) n (110) m (109) : (58) k (107) e (101) i (105) t (116) h (104) (13) (10) a (97) c (99) c (99) : (58) k (107) e (101) i (105) t (116) h (104)
                                      • 17. Re: email insert
                                        jdeline Level 1
                                        The e-mail body does not have a ASCII 10 at the end of it, so that is throwing off the screen scrape. I think an easier way is to convert all occurrences of ASCII 13 ASCII 10 to the pipe ( | ) character. Then process a pipe-delimited list. See below.
                                        • 18. email insert
                                          craig_mac Level 1
                                          ok its working just one small problem,

                                          i have a loop which loops through the cfpop, what i need to do is loop through each email from the pop, then send email with the body, then delete that current email so this is the code i have but it dosent loop through each email?

                                          • 19. Re: email insert
                                            Level 7
                                            try this:

                                            <CFSET mymessage = Replace(email.Body, "#Chr(13)##Chr(10)#", "|", "ALL")>
                                            <cfset str_message = trim(listlast(listfirst(mymessage, "|"), ":"))>
                                            <cfset str_to = trim(listlast(listgetat(mymessage, 2, "|"), ":"))>
                                            <cfset str_nm = trim(listlast(listgetat(mymessage, 3, "|"), ":"))>
                                            <cfset str_acc = trim(listlast(listlast(mymessage, "|"), ":"))>

                                            <cfoutput>
                                            #str_message#<br />
                                            #str_to#<br />
                                            #str_nm#<br />
                                            #str_acc#<br />
                                            </cfoutput>
                                            --

                                            Azadi Saryev
                                            Sabai-dee.com
                                            http://www.sabai-dee.com
                                            • 20. email insert
                                              craig_mac Level 1
                                              ok thanks do i still need the loop?

                                              and will this send one email for each of the emails in the inbox and then delete each one as it loops through?

                                              i have tried that but still if there are two emails in the inbox, the email gets sent twice to the first one and once to the second?

                                              does it have something to do with the pop delete?
                                              • 21. Re: email insert
                                                Level 7

                                                > ok thanks do i still need the loop?

                                                which loop are you referring to?

                                                > and will this send one email for each of the emails in the inbox and then delete each one as it loops through?

                                                this is some new function of your page you are referring to, since you
                                                have not mentioned any of this in your previous posts...

                                                all the help given to you so far was only about retrieving the message
                                                body from an email. you will have to add code to store it in your db
                                                and do any other things you want to. if you need help - ask specific
                                                questions and post any code that gives you trouble.
                                                --

                                                Azadi Saryev
                                                Sabai-dee.com
                                                http://www.sabai-dee.com
                                                • 22. email insert
                                                  craig_mac Level 1
                                                  hi i have posted about the loop a few posts back but heres what i need

                                                  say i have 3 emails in my pop account they will all have the same fields of information.

                                                  1. loop through each of the emails
                                                  2. send email to a new address with the fields in the cfmail below (so if there are 3 emails in the pop 3 emails will get sent).
                                                  3. inerst information into my table
                                                  4. delete the pop email after the above has been done
                                                  • 23. Re: email insert
                                                    Level 7
                                                    so, what's the problem with the code? it look OK to me, except maybe:

                                                    > <cfquery datasource="#application.ds#">
                                                    > insert into SMS_Records
                                                    > (SentTo, ClubID, Cost, RCODE, NumberSent)
                                                    > values
                                                    > ('#Send22.PlayerSurname#' '#Send22.PlayerFirstName#', '#ClubID#', 1.4, 'T2',
                                                    > '#str_toa#')
                                                    > </cfquery>

                                                    i think you should change
                                                    '#Send22.PlayerSurname#' '#Send22.PlayerFirstName#'
                                                    to
                                                    '#Send22.PlayerSurname# #Send22.PlayerFirstName#'



                                                    --

                                                    Azadi Saryev
                                                    Sabai-dee.com
                                                    http://www.sabai-dee.com
                                                    • 24. Re: email insert
                                                      craig_mac Level 1
                                                      ok once again the cfmail sends out the emails twice to the first loop and once to the second loop (ussuming there are 2 emails at the pop account)

                                                      so if i have
                                                      EMAIL 1
                                                      message:MEASGE 1
                                                      to:PHONE NUMBER HERE
                                                      nm:USERNAME 1
                                                      acc:ACC1


                                                      EMAIL2
                                                      message:MEASGE 2
                                                      to:PHONE NUMBER HERE
                                                      nm:USERNAME 2
                                                      acc:ACC2

                                                      then on the cfloop the cfmail sends 2 emails to EMAIL 1 and 1 email to EMAIL 2

                                                      where i only want to send 1 email to each