Email Workfolw Database

[Mohanss82]

Hi,

I am trying to link my outlook with ms-access where the emails received to common inbox is assigned separate users based on another table. I am able to link the folder to an access table and have it refreshed periodically and below are my questions:



1. I see the subject line not similar always, hence I would create a fields such as Type, Folder name Units based on the Subject Line which has 3 parts starting Type, Event Name & Unit Size
Ex 1: AA – Steve Austin – 10 Units
Ex 2: BB – Steve John
· Type – Extract this based on Subject line of the email
· Folder Name – This is a concatenation of the Subject Line & Email Received Time
· Unit Size – I would require the Numeric figure mentioned in the subject line else the field should be set with default value as 1

2. There could be emails received with attachments which should be saved in the 2 different network path based on the Type extracted from the Email subject line and the field Folder Name as per database

3. Users Table is created with their status as Yes or No. I would require every email to be assigned to the users who are marked Yes. This User status will be captured everyday where a User might be available today may not be available tomorrow, so in such case we would not want the old emails assigned to him need not be changed.

Please advise.

[rpeare]

What are the questions specifically, you've stated your goal.

How do you parse the subject line into the 3 parts you want?
How you copy an attachment to a specific folder?

what?

[Mohanss82]

Yup. I am stuck with the 3 points as mentioned above

1. I want to parse the subj line in to 3 parts, what is the access fx should I look for ?
2. How to make the attachments save in the specific folder accordingly and
3. how to assign them to users marked available ?

[rpeare]

if the subject line is

AAA - BBB - CCC and you want to extract the various portions you could use instr and instrrev to get the positions of the - marks then extract the portions you want using left(), mid() and right() functions.

example of saving an attachment

http://stackoverflow.com/questions/5...ing-access-vba

last part depends on your table structure, I would assume you'd have to have some method of evaluating 'available' (simple yes/no field or dependent on current 'load' i.e. someone can't have more than 5 assigned and active tasks)

if you want the tasks assigned to a random individual who also has room in their 'active' load that's one thing, if you don't care, you just want to pick someone with available space that's another.

[Mohanss82]

For the Subj line Parsing, this Left or Mid or Right may not work as the Characters are not defnite. Below are the examples. where the Characters lenght for the 2nd or 3rd part cannot be identified.
Ex 1: AA – Steve Austin – 10 Units
Ex 2: BB – Steve Johnson

With regards to Assigning part, I want continuous assigning for whom marked as Yes irrespective of their existing emails....

I am trying to replicate the Linked inbox table to a new with specific fields alone which will be my workflow... I am not able to link or create a data connections.. pls advise

[rpeare]

as long as the hyphen is there you can logically figure it out

i.e. if instr(subject, "-") and instrrev(subject, "-") are the same value then you just look for 2 values


alternately you can also use

split(subject, "-") to load the subject as an array split up by the - marks then test the upper bound of the array (ubound(arrayname)) to see how many values are in it.

for the second part you will have to have some sort of prioritizing algorithm or you may get the same person being assigned 5 or 10 or x many items in a row until their queue is full.


I have no clue what you are talking about with your last sentence, if you've got a screen shot or a sample database or something that might help but you seem to be referencing a user interface which I have no access to.