Results 1 to 9 of 9

Extracting Data From Imported Email

  1. #1
    woodjl1650 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4

    Extracting Data From Imported Email

    I have my inbox linked to my database, and it is able to pull the emails in and place in a table. What I am trying to do now, is pull just a small portion of the contents and then link to the corresponding table. I can't seem to figure out how to pull the data from the content field.

    Table = OutLookRedCross
    qryARCEmailCerts - is the trimmed down data
    Contents Needed = Each line that has the Name and hyperlink


    Subject = Course Record Number
    Received = Date

    All other data can be disregarded. If I could extract the info above from the table, I can then link to my other tables.

    Any ideas?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,885
    How can line with name and hyperlink be identified? Is there an identifier word that will always be present? Post example of text.
    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
    woodjl1650 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    Yes, the email is a system generated one, so everything is virtually the same. The line I need is: 1) FIRST NAME LAST NAME https://classes.redcross.org/Saba/We...ficate?certid= that part of hyperlink/text will always be constant. There's a code after the "=" which will change per certificate, and obviously the First Name and Last Name will change as well.

  4. #4
    woodjl1650 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4
    https://classes.redcross.org/Saba/We...ficate?certId= Is the constant, there is some additional coding after the "=", but the front portion of link will remain the same.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,406
    Post 3 and 4 were moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,122
    I suggest you post a copy of the database with only a few anonymized records. It is difficult to offer more focused advice since we do not have any details of your code nor a "mocked/concocted" example.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,885
    Consistency of structure is critical in string manipulation. Example:
    Code:
    Sub GetInfo()
    Dim strS As String
    strS = "The line I need is: 1) FirstName LastName https://classes.redcross.org/Saba/We...ficate?certid=something part of hyperlink/text before = will always be constant."
    strS = Mid(strS, InStr(strS, ")") + 2)
    Debug.Print Left(strS, InStr(strS, "http") - 2)
    strS = Mid(strS, InStr(strS, "http"))
    Debug.Print Left(strS, InStr(strS, " ") - 1)
    End Sub
    The output:
    FirstName LastName
    https://classes.redcross.org/Saba/We...ficate?certid=something
    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.

  8. #8
    woodjl1650 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    4

    Question

    Here is a clean copy of my database. End goal is to have my email confirmation imported to a table, then the certificate links paired with the individual who attended the course (course # is within the email as well as the date). I just don't know how to extract the data from the email body.

    DB v10.1 - CLEAN.accdb.zip

  9. #9
    Gicu's Avatar
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    850
    Feel free to download and use my free utility that does just that:http://forestbyte.com/ms-access-util...ook-companion/
    You can post back if you need help setting it up.

    Cheers,
    Vlad

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

Similar Threads

  1. Replies: 2
    Last Post: 11-12-2014, 04:57 PM
  2. Extracting data from reports
    By Dhivya in forum Reports
    Replies: 3
    Last Post: 07-04-2014, 08:50 AM
  3. extracting data from field
    By focosi in forum Queries
    Replies: 6
    Last Post: 02-11-2012, 03:12 AM
  4. Extracting text from XML data
    By rob4465 in forum Access
    Replies: 1
    Last Post: 03-18-2010, 06:41 PM
  5. Extracting data after a keyword
    By KerryA in forum Queries
    Replies: 1
    Last Post: 02-11-2010, 09:56 PM

Tags for this Thread

Posting Permissions

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