Automatically get data from Outlook to three different tables

[MrSweden]

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

[June7]

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

[MrSweden]

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

[June7]

Yes, variables will be useful, as well the other concepts I stated.

Complication is presented by the inconsistency of X data.

[MrSweden]

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

[June7]

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.

[MrSweden]

Dynamic array is a good idea, thank you for helping meout. I think I'm gonna get it work now.