#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0

    Automatically get data from Outlook to three different tables


    Hi!

    I'm new to develope databases in Access. I would be grateful if somebody could help me out with a little problem I have. I am using Access 2010 64bit and Outlook 2010.



    The data I want to get in my database (only for use in my computer, no internet etc) has this format in the mail I recive in Outlook:

    Blank row
    X Y 06/11, Z nr
    X Y 06/11, Z nr
    Blank row
    regards etc

    I differes how many rows of data I get in each mail.
    X=1,2 or 3 words
    Y=1 word
    06/11 date (I want to transform it to swedish date format yyyy-mm-dd)
    Z=1 word (always same word)
    nr=1 number

    I want to get:
    X to table one field A
    Y to table two field A
    yyyy-mm-dd to table two field B
    Automatically calculate day of week to table two field C
    Z do nothing
    nr to table three field A
    somehow register that the mail has been stored in the database to avoid duplicates.

    Regards
    MrSweden
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    So this is a single-user db?

    You can set a link to Inbox folder of Outlook. Then query the records like any table. View the Contents field and manually input data wherever. Delete the message or mark ContentUnread field as read (a number value, don't know what number means read).

    Automating with VBA would be complex code utilizing recordset and loops and SQL action statements.

    Before you go manipulating date structure, review http://allenbrowne.com/ser-36.html
    Last edited by June7; November 6th, 2012 at 06:49 PM.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    Thanks for your quick answer and the link.

    Yes it is single user db.

    I know it would be a lot easier for me to manually get the data in the right fields, especially when there is not so much data to input. But I have to solve this problem, no matter how long time it takes.

    I have managed to collect the data from the mail to one Access-field. Now I have to split the text to 3 tables, I think I have to treat them as temporary variables in VB.

    Regards
    MrSweden
  6. #4
  7. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Yes, variables will be useful, as well the other concepts I stated.

    Complication is presented by the inconsistency of X data.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    X is not the problem. The db is about racing horses. I get a mail everytime one of the horses list in my db start.

    X is the name of the horse, it could be for example "WINNER" or "WINNER EVERYTIME" or "GREAT WINNER EVERYTIME" Y=is the name of racing track (always one word) 06/11= date of the race Z=the word "race" (always the same=meaningless) nr=is the number X get in this race. So what I need to do is start splitting from right to left like this: nr=first word from right = text from right to left to first space, ignore next word from right to left = Z, left of "," to next space=date, left to next space =Y, left of next space =X (it does not matter if it is 1, 2 or 3 words) because it is all that remains.

    Regards MrSweden
  10. #6
  11. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Yes, good point, split from right to left using InStrRev.

    Could use Split function to split the string to a dynamic array. If there are more than 5 elements then is apparent the horse name has multiple parts.
    Last edited by June7; November 7th, 2012 at 08:02 PM.
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    Dynamic array is a good idea, thank you for helping meout. I think I'm gonna get it work now.

Similar Threads

  1. MS Access linked tables to sql server automatically
    By jennypretty in forum Microsoft Access Help
    Replies: 5
    Last Post: April 28th, 2010, 07:54 PM
  2. Automatically Updating Yes/No fields by referencing other tables.
    By Narbcake in forum Microsoft Access Help
    Replies: 2
    Last Post: January 10th, 2008, 10:40 PM
  3. Replies: 5
    Last Post: October 26th, 2007, 03:06 PM
  4. Update tables automatically every day
    By SunnyVu in forum Microsoft SQL Server
    Replies: 6
    Last Post: August 23rd, 2007, 05:07 PM
  5. Automatically fill a table from Data in other tables
    By kahooma in forum Microsoft Access Help
    Replies: 2
    Last Post: March 22nd, 2006, 06:23 AM

IMN logo majestic logo threadwatch logo seochat tools logo