locked
Reading emails into VFP table RRS feed

  • Question

  • I want to open an email and transfer data from the emil body and any attachments (attachments would only be DBF files) into a VFP table in code. I can get the data once i get the email open and downloade the attachment. Is this possible entirely in code?

    thanks, Jim
    Wednesday, January 28, 2009 2:11 PM

Answers

  • Then you will probably be best off using CDO - that does not depend on the EMail client.

    Unfortunately that is not really something that we can cover in a forum answer. However, there are lots of resources available on line about CDO - just google "VFP and CDO" and you'll find lots of answers.

     

    If you want something a little more permanent, there is also a whole chapter (Chapter 4 actually) dedicated to sending and receiving EMail from FoxPro in "MegaFox: 1002 Things You Wanted to Know About Extending Visual FoxPro" which you can get (if you don't already have it) as an E-Book from http://www.hentzenwerke.com/catalog/megafox.htm

     

    • Marked as answer by Jim Hol Monday, February 9, 2009 1:52 PM
    Wednesday, January 28, 2009 10:55 PM

All replies

  • Yes, it is possible, but the methodology depends on the EMail client you have installed - what are you using?

     

    Wednesday, January 28, 2009 4:35 PM
  • I have hotmail and yahoo. I would like to also include outlook and outlook express if possible. This is for an application where I will not know before hand what client they are using.

    Thanks, Jim
    Wednesday, January 28, 2009 6:01 PM
  • Then you will probably be best off using CDO - that does not depend on the EMail client.

    Unfortunately that is not really something that we can cover in a forum answer. However, there are lots of resources available on line about CDO - just google "VFP and CDO" and you'll find lots of answers.

     

    If you want something a little more permanent, there is also a whole chapter (Chapter 4 actually) dedicated to sending and receiving EMail from FoxPro in "MegaFox: 1002 Things You Wanted to Know About Extending Visual FoxPro" which you can get (if you don't already have it) as an E-Book from http://www.hentzenwerke.com/catalog/megafox.htm

     

    • Marked as answer by Jim Hol Monday, February 9, 2009 1:52 PM
    Wednesday, January 28, 2009 10:55 PM
  • This will help immensely , thank you.

    Jim
    Thursday, January 29, 2009 12:24 AM
  • There is an old example at the Fox Wiki that might be of help:

    http://fox.wikis.com/wc.dll?Wiki~GetPopEmail

    Thursday, January 29, 2009 12:32 PM
  • Jim Hol said:

    I want to open an email and transfer data from the emil body and any attachments (attachments would only be DBF files) into a VFP table in code. I can get the data once i get the email open and downloade the attachment. Is this possible entirely in code?

    thanks, Jim


    Andy and I wrote a Kitbox colum in FoxTalk called "Another look at Outlook" that includes complete code to accomplish this task. Unfortunately, that will not help you when it comes to Outlook Express or Yahoo e-mail. Here is the class that handles this in Outlook:

    ******************************************************************************

    *** Program: Inbox2Dbf

    *** Time Stamp:   07/31/02 08:26:02 PM

    *** Instantiates the custom class defined in this program for automating outlook 2000.

    *** Includes functionality to read

    *** messages from the Outlook Inbox and store them in OlMail.dbf

    ******************************************************************************

    Local loMail, loRootFolder

    loMail = Createobject( 'sesOutlook' )

    If Vartype( loMail ) = 'O'

      With loMail

        loRootFolder = .oNameSpace.Folders(1)

        If .ReadMail( loRootFolder )

          Set DataSession To loMail.DataSessionId

          Select SaveMail

          Browse Last

        Endif

      Endwith

    Endif

    Return

     

    Define Class sesOutlook As Session

     

      *** Object reference to the outlook application object

      oOutlook = .Null.

      *** Object reference to the MAPI namespace object in Outlook from which you reference all the other objects (like the inbox)

      oNameSpace = .Null.

     

      *** array for holding contacts

      Dimension aContacts[1,1]

      *** name of attachment subdirectory

      cDirAttach = "ATTACH\"

      *** add a counter property

      iCounter = 1

     

      *******************************************************************************************************

      Function CreateSession()

        *******************************************************************************************************

        *** Instantiate outlook and logon

        Local llRetVal

     

        *** See we we already have an instance of Outlook Running

        If Type( 'This.oOutlook' ) = 'O' And Not Isnull( This.oOutlook )

          *** No need to create a new instance

        Else

          With This

            .oOutlook = Createobject( 'Outlook.Application' )

            If Type( 'This.oOutLook' ) = 'O' And Not Isnull( .oOutlook )

              .oNameSpace = .oOutlook.GetNameSpace( 'MAPI' )

              If Type( 'This.oNameSpace' ) = 'O' And Not Isnull( .oNameSpace )

                llRetVal = .T.

              Endif

            Endif

          Endwith

        Endif

     

        Return llRetVal

      Endfunc

     

      *******************************************************************************************************

      Function OpenMsgTables()

        *******************************************************************************************************

        With This

          If Not Used( 'SaveMail' )

            Use SaveMail Again In 0

          Endif

          If Not Used( 'OlAttach' )

            Use SaveAttach Again In 0

          Endif

        Endwith

     

        Create Dbf SaveCont (cnal c(50), cnaf c(50), cnafl c(80), cemail1 c(80), cemail2 c(80), cemail3 c(80))

     

      Endfunc

     

      *******************************************************************************************************

      Function ReadMail( toFolder )

        *******************************************************************************************************

        *** The ReadMail Method goes through all the Outlook folders recursively and save all

        *** of the mailItems to the table OlMail.dbf. If the message has any attachments,

        *** it then calls the SaveAttachments method to save the attachment as a file on disk and

        *** insert a record with the name of the file into OlAttach.dbf along with a foreign key

        *** to the message in OlMail.dbf

        Local loItems, loItem, loFolders, loFolder

     

        *** Get a local referenece to the collection of of items in the current folder

        loItems = toFolder.Items

     

        *** Process all the items in the current folder

        *** If it is a mail item, save it and process the attachments

        If Vartype( loItems ) = 'O'

          For Each loItem In loItems

            Wait Window Nowait Transform(This.iCounter)

            If   loItem.Class =   43    && olMail

     

              *** Add a record to the messages tabel

              *** store list of recipients as well

              lnCount = loItem.Recipients.Count

              m.lcRecip = ''

              For lnRecip = 1 To lnCount

                loRecipient = loItem.Recipients[ lnRecip ]

                m.lcRecip = m.lcRecip + loRecipient.Name + ': ' + loRecipient.Address + Chr( 13 ) + Chr( 10 )

              Endfor

     

              *** 2002-09-12 Marcia G. Akins: let's try a different strategy for matching up the e-mail address

              *** First see if the sender name actually IS an e-mail address

              If '@' $ loItem.SenderName

                m.lcSenderEm = loItem.SenderName

              Else

                lnRow = Ascan( This.aContacts, loItem.SenderName, -1, -1, 3, 15 )

                If lnRow > 0

                  m.lcSenderEm = This.aContacts[ lnRow, 6 ]

                Else

                  lnRow = Ascan( This.aContacts, loItem.SenderName, -1, -1, 4, 15 )

                  If lnRow > 0

                    m.lcSenderEm = This.aContacts[ lnRow, 7 ]

                  Else

                    lnRow = Ascan( This.aContacts, loItem.SenderName, -1, -1, 5, 15 )

                    If lnRow > 0

                      m.lcSenderEm = This.aContacts[ lnRow, 8 ]

                    Else

                      m.lcSenderEm = ''

                    Endif

                  Endif

                Endif

              Endif

     

              If '@' $ loItem.SenderName

                m.lcSenderEm2 = loItem.SenderName

              Else

                If Empty(loItem.To)

                  * don't reply - this is just a draft

                  m.lcSenderEm2 = "Draft"

                Else

                  loReply = loItem.Reply()

                  loRecip = loReply.Recipients[ 1 ]

                  m.lcSenderEm2 = Iif( Not Empty( loRecip.Address ), loRecip.Address, loRecip.Name )

                Endif

              Endif

     

              Insert Into SaveMail ( omInDate, omSender, omSubject, omBody, omFolder, omRecip, omSenderEm, omSenderEm2 ) ;

                VALUES ( loItem.ReceivedTime, loItem.SenderName, loItem.Subject, loItem.Body, toFolder.Name, m.lcRecip, m.lcSenderEm, m.lcSenderEm2 )

              *** Now see if we have attachments

              If loItem.Attachments.Count > 0

                This.SaveAttachments( loItem )

              Endif

            Endif

     

            This.iCounter = This.iCounter + 1

     

          Endfor

        Endif

     

        *** Now see if this folder has folders to process

        If toFolder.Class = 2  &&  olFolder

          loFolders = toFolder.Folders

          For Each loFolder In loFolders

            This.ReadMail( loFolder )

          Endfor

        Endif

     

        Return

      Endfunc

     

      *******************************************************************************************************

      Function SaveAttachments( toMsg )

        *******************************************************************************************************

        Local  loAttachment,  lcFileName

     

        *** Saves the attachmens associated with the current message to the specified file name

        For Each loAttachment In toMsg.Attachments

     

          **** send attachment files into a subdir

          lcFileName = Fullpath( Curdir() + This.cDirAttach ) + loAttachment.FileName

          If !Empty( m.lcFileName )

            loAttachment.SaveAsFile( lcFileName )

            *** Make sure it was saved

            *** The SaveAsFile method returns null...it doesn't tell us about success or failure

            If File( lcFileName )

              *** ok...now add it to the table

              Insert Into SaveAttach ( omMailFK, attFname ) Values ( SaveMail.omMailPK, lcFileName )

            Endif

          Endif

        Endfor

      Endfunc

     

     

      *******************************************************************************************************

      Function Destroy()

        *******************************************************************************************************

        With This

          .oNameSpace = .Null.

          .oOutlook = .Null.

        Endwith

      Endfunc

     

     

      *******************************************************************************************************

      Function Init()

        *******************************************************************************************************

        Local llRetVal

        llRetVal = DoDefault()

     

        If llRetVal

          llRetVal = This.CreateSession( )

        Endif

     

        If llRetVal

          This.OpenMsgTables()

        Endif

     

        *** create the contacts list as well

        If llRetVal

          This.GetContacts()

        Endif

     

        Return llRetVal

     

      Endfunc

     

      *******************************************************************************************************

      Function GetContacts()

        *******************************************************************************************************

     

        Local loAddressBook As Outlook.MAPIFolder, loContact As Object, lnContactCount

     

        *** Get a reference to the contacts folder

        *** olFolderContacts is 10

        loAddressBook = This.oNameSpace.GetDefaultFolder( 10 )

        If Vartype( loAddressBook ) = 'O'

          lnContactCount = 0

          *** Get info about each contact into the array

          For Each loContact In loAddressBook.Items

            With loContact

              *** Make sure we only get individual contacts

              *** and skip any distribution lists

              Wait Window Nowait "Parsing Contacts: " + Transform(m.lnContactCount) + " processed"

              *** olContact is 40

              If .Class = 40

                lnContactCount = lnContactCount + 1

                Dimension This.aContacts[ lnContactCount, 9 ]

                This.aContacts[ lnContactCount, 1 ] = .LastName

                This.aContacts[ lnContactCount, 2 ] = .FirstName

                This.aContacts[ lnContactCount, 3 ] = Strtran( .Email1DisplayName, '(E-mail)', '', -1, 1, 1  )

                This.aContacts[ lnContactCount, 4 ] = Strtran( .Email2DisplayName, '(E-mail 2)', '', -1, 1, 1 )

                This.aContacts[ lnContactCount, 5 ] = Strtran( .Email3DisplayName, '(E-mail 3)', '', -1, 1, 1 )

                This.aContacts[ lnContactCount, 6 ] = .Email1Address

                This.aContacts[ lnContactCount, 7 ] = .Email2Address

                This.aContacts[ lnContactCount, 8 ] = .Email3Address

                This.aContacts[ lnContactCount, 9 ] = Upper(.FullName)

                m.lcNaf = .LastName

                m.lcNal = .FirstName

                m.lcNafl = .FullName

                m.lcemail1 = .Email1Address

                m.lcemail2 = .Email2Address

                m.lcemail3 = .Email3Address

     

                Insert Into SaveCont ;

                  (cnaf, cnal, cnafl, cemail1, cemail2, cemail3) ;

                  values ;

                  (m.lcNaf, m.lcNal, m.lcNafl, m.lcemail1, m.lcemail2, m.lcemail3)

     

              Endif

            Endwith

          Endfor

          Asort( This.aContacts )

        Endif

     

    Enddefine

    *** EndDefine: sesOutlook

     



    Marcia G. Akins
    Saturday, January 31, 2009 11:48 AM
  • Hi Marcia,

    Regarding your post: "Andy and I wrote a Kitbox colum in FoxTalk called "Another look at Outlook" that includes complete code to accomplish this task. "

    I could do this myself but I'm in a total time crunch and can't justify spending time on this...

    Question: Is it possible to modify the above to allow a person to pick what folders to look at in Outlook so the above script doesn't waste time retrieving emails from folders that the user of the script does not want?

    Question: Is there any way to tell it to only get emails that are within the time frame of (startdate) , (enddate) or like "only get emails dated today minus 3 days and forward"?

    I am using VFP 9.0 w/ NO service packs.

    Problems: I get a few errors and here's how I handled them:

    At the beginning of the program I put:

    * Because the tables did not exist I had to create them:

    SET EXCLUSIVE OFF
    CREATE TABLE savemail ( omInDate d(8), omSender c(50), omSubject c(50), omBody m, omFolder c(50), omRecip c(50), omSenderEm c(50), omSndrEm2 c(50) )
    CREATE TABLE SaveAttach ( omMailFK c(50), attFname c(50))

    * I had to do a global replace for omSenderEm2 to be omSndrEm2 because the field name was too long

     * COMMENTED OUT - loAttachment.SaveAsFile( lcFileName ) && due to an error that recurred lots of times and I did not have time to fix it or even look into it

    * I had to frame the middle line of code below due to an error lots of times

    * commenting out the middle line of code just made matters worse down the road in the program

                ON ERROR WAIT WINDOW "ERROR" NOWAIT
                  loReply = loItem.Reply()
                ON ERROR

    * A few lines of code down from the above...

              Insert Into SaveMail ( omInDate, omSender, omSubject, omBody, omFolder, omRecip, omSenderEm, omSndrEm2 ) ;
                VALUES ( loItem.ReceivedTime, loItem.SenderName, loItem.Subject, loItem.Body, toFolder.Name, m.lcRecip, m.lcSenderEm, m.lcSenderEm2 )

    * ...generates an error "file already in use" due to us being in a different data session

    * so i need to suspend and manually change data sessions then the program runs fine till the next error

    Thanks in advance for any mods you can make.

    If you don't have time to make these mods that's fine.  I'll figure something else out.  This script has been the most promising so far.



    • Edited by HL987 Saturday, April 5, 2014 3:20 PM
    Saturday, April 5, 2014 2:32 PM