Help!!

E

elsanto

Aspiring to Sainthood...
Local time
Today, 20:31
Joined
Jul 9, 2003
Messages
35
Hey all,

I have a major problem with Access and Outlook at the moment. It's something of a saga, but here's the upshot of it all:

I need to design a basic, basic method to transfer emails from Outlook Inbox to a table in Access and assign a Unique ID to each item.

It's so simple, you say! I agree. But this process is for a team that has trouble entering date and time formats correctly into Excel. Computer literate they ain't.

Ideally, I'd like to create a live link to the Inbox, which I can do. But how do I then go about assigning a Unique ID? I can't modify the linked table. And if I append to another table, how do I restrict duplicates?

Bearing in mind that this needs to be as user-friendly, idiot-proof as possible, I'm open to any suggestions.

Many thanks in advance.
Will sit here with fingers crossed...
:)
 
This is an interesting question because I am in a similar position just now. I have not come up with a solution yet, will be interesting to see what replies you get. If I get any further forward with this myself I will let you know.
 
Just been having a little fiddle about with this and noticed something I didn't know was there before (feel such an idiot now) Anyhow I don't know if you've got this far or not but if you go into your database and choose import > file type Exchange, this will bring up all your folders from your inbox and your archive files. You then get the import wizard as you normally would and there is an option to assign a unique ID by choosing "Let Access define Primary Key" or "Choose your own*

Apologies if you already knew that.
Hay
 
Hayley,

The import wizard isn't an option, since I need this database to be operated by computer illiterate staff. Otherwise it'd be perfect.

In the meantime I have figured out a work around solution.

1. Link Inbox to Access.

2. Append data from live link table to new table. My biggest problem here was appending duplicate information. To get around this, the append query has an extra field that combines the received date/time and the senders name and uses that as a psuedo primary key field. The criteria specifies not to append duplicates of the key. eg Not In (Select Key From Appended)

3. Add an autonumber field to the appended table.

4. I've set up an autoexec macro to run the append query. So now staff open the database, information is automatically appended, and at the click of a button (big button, big font) they can print the information required.

The whole process is not as crisp as I'd like, but at least it is (hopefully) foolproof and easy to use. It'll do until I get a better solution.

Hope this is of interest.
Cheers.
 
Elsanto

Thanks very much for this information. This will really help me cut my time in half on one particular database. I've always known you could import from excel etc just didn't know you could take your emails over too in the same way from outlook. I wish I knew that sooner.

Anyway glad you got something working.

Thx again
Hay
 
This may help folks. This will read the contents of the Outlook Inbox and copy the message of all unread messages into a table.

Code: 
Public Function ReadMail()
'Refs Needed
'===========
'Outlook Library
'DAO 3.51
Dim oOutlook As Outlook.Application
Dim oNs As Outlook.NameSpace
Dim oFldr As Outlook.MAPIFolder
Dim oAttachments As Outlook.Attachments
Dim oAttachment As Outlook.Attachment
Dim oMessage As Outlook.MailItem

Dim iMsgCount As Integer, MyDb As DAO.Database, MyRs As DAO.Recordset

'finish other processes first
DoEvents

'get reference to inbox
Set oOutlook = New Outlook.Application
Set oNs = oOutlook.GetNamespace("MAPI")
Set oFldr = oNs.GetDefaultFolder(olFolderInbox) 'Link to Inbox
'Set oFldr = oNs.GetDefaultFolder(olFolderOutbox) 'Link to Outbox

'set recordset
Set MyDb = CurrentDb()
Set MyRs = MyDb.OpenRecordset("tblToStoreMailIn")

For Each oMessage In oFldr.Items 'Loop through each message in folder
        
    If oMessage.UnRead = True Then 'Only process unread e-mails
        
            MyRs.AddNew
            MyRs("MessageBody") = oMessage.Body create a new record and copy the message body
            MyRs.Update
    End If
    oMessage.UnRead = False 'mark message as read
    iMsgCount = iMsgCount + 1

DoEvents
Next oMessage
    
    Set oAttachment = Nothing
    Set oAttachments = Nothing
    Set oMessage = Nothing
    Set oFldr = Nothing
    Set oNs = Nothing
    Set oOutlook = Nothing
    
MsgBox iMsgCount & " Messages were processed", , "Messages Processed"
End Function

I have added a few bits onto this for example
1. Screen for a particular subject line
2. Screen for a particular sender
etc

Is this helpful?
 
Last edited:
That looks good Fiz, we can always rely on you;) Can I ask one more thing, do you know how to update a field in access when an email comes through?

For example I have a database where we email all of our contacts on a monthly basis and I just so happen to get all the replies and there are many of them. The ideal situation would be that when an email comes into my inbox, this can then link to a field in the db ie contact replied = yes and it would be really great if the email content could be copied over to the field that holds this info in my table and overwrite the previous months information.

I know this is a lot to ask, thanks for any help you can give.

Hay
 
No problem Hay, see you've managed to sneak on again!

How I would do this personally is to use a timer event to check the inbox periodically and see if you have had a reply - do you mean an actual reply or a read receipt?

As I described, you can extend this code to filter any number of variables eg Subject of message, To field, From field etc.

The easiest way is to filter for the subject message then using the senders e-mail address, set the recieved reply flag to yes (simple yes/no field) and copy the message to the respective field in the table.

I'll have a stab at the modifications!

This code will rely on a new field in the contact's table identifying the Subject of the mail you sent (and that all the Subjects are the same for all contacts - not individualised to each contact) but also that they do not change the subject of the reply
Here we go!

Code: 
Public Function ReadReply()
'Refs Needed
'===========
'Outlook Library
'DAO 3.51
Dim oOutlook As Outlook.Application
Dim oNs As Outlook.NameSpace
Dim oFldr As Outlook.MAPIFolder
Dim oAttachments As Outlook.Attachments
Dim oAttachment As Outlook.Attachment
Dim oMessage As Outlook.MailItem

Dim MyDb As DAO.Database, MyRs As DAO.Recordset, strSentSubject as String

'finish other processes first
DoEvents

'get reference to inbox
Set oOutlook = New Outlook.Application
Set oNs = oOutlook.GetNamespace("MAPI")
Set oFldr = oNs.GetDefaultFolder(olFolderInbox) 'Link to Inbox
'Set oFldr = oNs.GetDefaultFolder(olFolderOutbox) 'Link to Outbox

'set recordset
Set MyDb = CurrentDb()
Set MyRs = MyDb.OpenRecordset("tblContacts")

strSubject = myRs("LastEmail") 'Where LastEmail is the field that stores the subject line of the last monthly e-mail

'Loop through each message in folder
For Each oMessage In oFldr.Items 

'Only process unread e-mails with a matching Subject line
If oMessage.UnRead = True And oMessage.Subject = "Re: " & strSentSubject Then 
 
            MyRs.movefirst
            MyRs.FindFirst "ContactEMail = '" & oMessage.From & "'" 'Look for the Contact who has sent the e-mail by e-mail address
            If not MyRs.NoMatch then
            MyRs.Edit
            MyRs("EmailBody") = oMessage.Body
            MyRs("ReplyReceived") = true
            MyRs.Update
            End If
End If
    oMessage.UnRead = False 'mark message as read

DoEvents
Next oMessage
    
    Set oAttachment = Nothing
    Set oAttachments = Nothing
    Set oMessage = Nothing
    Set oFldr = Nothing
    Set oNs = Nothing
    Set oOutlook = Nothing
    
End Function

I haven't managed to test this but hey, can't do all the work eh?;)
 
Last edited:
Why do I need to sneak on? It's for "educational purposes";)

Thanks for giving me a start on this, I will have a little bash at it and see what I can get to work, knowing me not very much:p

Thanks for your time Fiz
Hay
 
You must log in or register to reply here.

Similar threads

C
querying outlook
Replies
2
Views
449
CJ_London
C
S
Incremental Sequential Numbering by Year via Calculated Field
Replies
17
Views
1,173
CJ_London
C
nhorton79
Multi Value Combobox
Replies
4
Views
439
arnelgp
arnelgp
A
Calendar events but not in Outlook
Replies
13
Views
1,056
Pat Hartman
P
isladogs
Access Europe User Group - Wed 4 June: Orchestrating Office: Amplifying Access with Multi-Application Automation (Domenico Ruggiero)
Replies
2
Views
471
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom