Store Emails within Database.

[travisdh]

Hi All.

I was wondering if anyone has any suggestions or comments on how I might achieve a certian task.

I work for a scientific consultancy company, who do both internal and external analysis, and have often many jobs running at the one time assigned to one, or multiple people. What I want to be able to do is store all the information in a database, and use that to manage the jobs, tasks and the likes.

I have the client table set up already, and the 'projects' table, where a client can be selected, and from within the form samples can be generated and selected for analysis, or have an analysis type selected for them.

Essentially I have a database that will store lots of information about the clients, the various jobs that are running at the moment but I want to be able to use this database to track just about everything. So what I would like to do is link it into email, so that if an email comes from, or goes to a client the database picks it up stores it in the database in a corrospondance table for that client, and also if there is a job number in the email it goes into the corrospondance table for the project to which the job relates.

For example I have two projects CV100821 and F1008-24 that are to client example1 and example2, and the email address is example1.com and example2.com The macro checks the email addresses (recieved and sent) and if an email has come from example2.com then it gets put in the corrospondance table for that client, but if it has the F1008-24 term in it, the email system picks it up, and stores it in the corrospondance for that table.

If no client existed, it should just create a client based on the example2.com name.

Can anyone give me some help, or have they played around with macro's that scan emails and store them in a to/from, subject, message, attachements table?

Thanks for any help!

 

[darbid]

Does every user have their own front end Access which is locally stored on their computer?
Does every user have their own Outlook and email address or are you using a shared email address?

If User 1 sends an email to client1 should user 2 be able to see this email?

 

[travisdh]

So the database will be a shared database, so all users can access it from the server, but each user will have their own email accounts. With the exception of emails to and from the accounts@companyname.com.au address, all emails can be seen by anyone, the accounts emails can be stored, but unless the user is accounts no one can see the content of them.

all users should be able to see all emails for each client and job. And i will make a setting so the jobs themselves and the clients themselves are assigned to each user so they may not be visible unless access is granted.

 

[darbid]

First i need to say I am not an expert, I am only telling you what i have done. I have a feeling there might be specific software or active X controls that might make your life easier but I do not know exactly about these. For example I know there is an inbox active x control for a form which I have 0 experience with.

You have to understand that Outlook is a database. The items in that database are mailitems, calendaritems, taskitems etc and each item has many properties or fields eg the body and subject. Then of course Outlook allows for a connection to and from STMP (Your email account etc).

As a result of this it is my personal opinion that it is doing things twice to show emails in Outlook and then again in Access. Just my personal opinion. You will get other differing opinions. Plus I find outlook coding strange and very hard to get along with.

In your case my advice is not good

So this is just my solution... you would need to put some code in each outlook client which listens for new mails that come in and listens for new mails that are sent.

You will need to decide on the criteria of what emails should be saved to your database.

Then you would need either for your Outlook code to open the DB and save directly to the tables the information - or to send the information to Access via a public routine and then let your coding in access add it to your database.

Here is an example of a listener for your inbox. There is another thread of mine here from which I go this.

The public with events goes to the top. before an routines.

Public WithEvents olInbox As Outlook.Items


Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

'here is where you decide IF the email is for your database or not and then save its contents in the database.

MsgBox "A new Email with subject " & Item.subject & " has arrived."
   

Exit_olInbox_ItemAdd:

    Exit Sub

Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olSentItems_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
End Sub

Private Sub Application_Quit()
    Set olInbox = Nothing
End Sub

Private Sub Application_Startup()

Set olInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub

It is s similar concept for sent items. You must watch the sent items folder as this is where the mailItem goes after it is sent.

 

[Lightwave]

My understanding is that sending e-mails from Access is no problem but receiving e-mails is a different thing.

Note the version of access is very important as later versions 2007 and 2010 seem to have some kind of support for e-mail

The following is a thread that says similar and gives code that lives in an outlook module and will "post" a record everytime e-mails are sent.

http://www.access-programmers.co.uk/forums/showthread.php?t=191587&highlight=import+e-mail

 

[darbid]

I had not read that one.

Yes important is this http://www.access-programmers.co.uk/forums/showpost.php?p=955690&postcount=4

and this

http://www.access-programmers.co.uk/forums/showpost.php?p=955752&postcount=5

If you have the access client on the same computer as the outlook client.

 

[travisdh]

So how do I then get the system to parse the email inbox, or outbox for a specific jobID for example FYYMM-ID or CVYYMMID /CNYYMMID and put it into the table based on those values? so it would store all information including attachments into the table.