Solved: Importing Outlook Mailbox contents using TransferDatabase

[sorrowsong]

I am currently accessing mail items in Outlook/Exchange in my Access DB by linking directly to the mailbox in which the information is contained and working with the linked table. However, this process is very time consuming; it can take up to two hours for all of the processes I'm working with to run.

I was able to run the same processes in under 5 minutes by importing the data through File -> Get External Data -> Import. Upon selecting "Outlook" from the "Files of type" dropdown I am taken to the "Import Exchange/Outlook Wizard" and by following the steps in the wizard I am able to import the data to a local table which speeds things up signifigantly.

I would like to perform the same import programatically using the TransferDatabase command; however, all my attempts at doing so have failed. I don't have a clear enough understanding of ODBC to figure out where I'm dropping the ball, but my best attempt so far is returning run time error 3010, "MSysAccessObjects already exists".

The following is the code I have so far. I believe the problem has to do with strOutlook; it looks like it should be split into multiple parts, but I don't know which part should be the "name" and which the "source" when calling TransferDatabase. In the real code, username and mailboxname are replaced by my LAN ID and the name of the outlook folder which contains the information I need to process.


Sub test()
Dim strOutlook As String
strOutlook = "Outlook 9.0;MAPILEVEL=Mailbox - West Customer Srv Communications|Inbox\;PROFILE=username;TABLETYPE=0;TABLENAME=mailboxname;DA TABASE=C:\DOCUME~1\username\LOCALS~1\Temp\;TABLE=mailboxname"
DoCmd.TransferDatabase acImport, "ODBC", strOutlook, acTable, strOutlook, tblTemp
End Sub

Any assistance would be greatly appreciated.

[geekgirlau]

Found the following on the Microsoft support website. This adds the Outlook data to an existing table, but could be modified for your purposes. You need to make sure you have a reference to both the DAO and Outlook object libraries.

[VBA]
Sub ImportContactsFromOutlook()
Dim ol As New Outlook.Application
Dim olns As Outlook.NameSpace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.ContactItem
Dim objItems As Outlook.Items
Dim rst As DAO.Recordset
Dim iNumContacts As Integer
Dim i As Integer


Set rst = CurrentDb.OpenRecordset("tblContacts")
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(olFolderContacts)
Set objItems = cf.Items

iNumContacts = objItems.Count

If iNumContacts <> 0 Then
For i = 1 To iNumContacts
If TypeName(objItems(i)) = "ContactItem" Then
Set c = objItems(i)

With rst
.AddNew
!FirstName = c.FiName
!LastName = c.LastName
!Address = c.BusinessAddressStreet
!City = c.BusinessAddressCity
!State = c.BusinessAddressState
!Zip_Code = c.BusinessAddressPostalCode
.Update
End With
End If
Next i

rst.Close
MsgBox "Finished"

Else
MsgBox "No contacts to import"
End If
End Sub
[/VBA]

I haven't found an programatic method of performing the import, but will keep looking!

[sorrowsong]

Thank you for your response. It looks like noone has an answer on how to do this using TransferDatabase, so I'll mark it as 'Solved' and continue with the manual process.