Results 1 to 10 of 10
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    Parsing web forms received in outlook folder as emails?

    Hi All,
    I am trying to map my access to outlook and parse all the mails submitted from an online form.
    Linked outlook table name Emailforms has the following field- Importance, con, priority, Contents etc.


    I am only interested in parsing the contents field and store into another table called Requests_temp to process. This CONTENT field has about 30 elements that I would like capture as fields in table for 65 records. And will receive 2 to3 new email every week. content field has following ..
    Group_Name=xyz

    Panel_Type=H type

    M_Last_Name =Susan

    Comments=

    Thank you in advance for guiding me in right direction

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So these are just emails in your Outlook inbox?

    If you have a link to the Outlook folder and this link shows a field called Contents, then parsing this text will likely involve string manipulation functions, VBA procedure, and an array.

    Are those 30 elements separated by 2 line feeds? Table field names are in the Contents string?

    Something like:
    Code:
    Sub GetData(strS As Variant)
    Dim aryS As Variant, rs As DAO.Recordset, x As Integer
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM table")
    aryS = Split(strS, Chr(13) & Chr(10))
    rs.AddNew
    For x = 0 To UBound(aryS)
        rs(Left(aryS(x), InStr(aryS(x), "=") - 1)) = Mid(aryS(x), InStr(aryS(x), "=") + 1)
    Next
    rs.Update
    End Sub
    If you can provide a file of these Content values so I could verify the structure then I could refine this code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Can we read the CONTENTS field as long string and parse the text before and after the "=" sign? Text before equal sign as Field name and Text after equal sign as Value
    I did no understand the point of using Chr(13) & Chr(10)
    Thank you
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Chr(13) & Chr(10) produce ASCII code representing non-printing line return characters in the string. These are used to define break between elements. Need something to determine where text after "=" sign ends and next element begins. Since your example shows elements on separate lines, I presumed there are line feed characters. If there aren't, then what separates the elements - a simple space? That won't help because your data has spaces in it - "H type".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you June7 for the explanation. I will work over the week -end on this code. Actually my code was working fine in different language until my team moved to O365 where I am getting MAPI error so I decided to parse the text in Access, which I am still trying to get grip on..

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did a test with the Excel file. Consider this:

    If InStr(aryS(x), "=") > 0 Then rs(Left(aryS(x), InStr(aryS(x), "=") - 1)) = Replace(Replace(Trim(Mid(aryS(x), InStr(aryS(x), "=") + 1)), vbCr, ""), vbLf, "")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    See if my free utility helps: http://forestbyte.com/ms-access-util...ook-companion/

    Feel free to ask any questions if you get stuck.

    Cheers,
    Vlad

  8. #8
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Hello June7 can you send me your code? I would like to compare mine. still cannot make it work and get Not responding error and freezes. Some how this site is not allowing me to copy paste my code

    Thank you in Advance

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    Code:
    Sub GetData()
    Dim aryS As Variant, rsDest As DAO.Recordset, rsSrc As DAO.Recordset, x As Integer, strField As String, strData As String
    Set rsDest = CurrentDb.OpenRecordset("SELECT * FROM Requests_temp")
    Set rsSrc = CurrentDb.OpenRecordset("SELECT Contents FROM EmailForms")
    Do While Not rsSrc.EOF
        aryS = Split(rsSrc!Contents, Chr(13) & Chr(10))
        rsDest.AddNew
        For x = 0 To UBound(aryS)
            If InStr(aryS(x), "=") > 0 Then
                strField = Left(aryS(x), InStr(aryS(x), "=") - 2)
                strData = Replace(Replace(Trim(Mid(aryS(x), InStr(aryS(x), "=") + 1)), vbCr, ""), vbLf, "")
                rsDest(strField) = strData
            End If
        Next
        rsDest.Update
        rsSrc.MoveNext
    Loop
    End Sub
    These new records do not have a relationship with the original records. That would require a unique identifier in the source data and I am not seeing one. There is nothing to prevent the same records to be repeatedly created.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you so much June7. It works perfectly.
    Sorry it took me a while to figure out the issues I had. After migrating to O365, when I opened the linked table it takeso a while to completely download emails. So I made a local copy of the linked table and ran the code.

    Regarding unique id: Each time I delete the records and run the code then append to actual table to process the information.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Linked Outlook folder emails
    By Sparky-1165 in forum Import/Export Data
    Replies: 4
    Last Post: 05-31-2018, 03:57 PM
  2. Replies: 1
    Last Post: 02-11-2016, 12:35 PM
  3. Send Emails from outlook
    By WhiskyLima in forum Access
    Replies: 8
    Last Post: 08-11-2014, 11:02 AM
  4. Replies: 1
    Last Post: 05-10-2013, 08:50 AM
  5. Linked Outlook emails
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 04-15-2012, 11:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums