Outlook 2000 Contacts into Access

MWiseman · August 25th, 2003, 06:39 PM

I need to pull certain fields from Outlook 2000 contacts into an Access 2000 table. I'd like to put the code behind a button in Outlook - or possibly link the fields so that it happens auto-magically.

I have worked with Access VBA, but I'm having trouble finding examples inside Outlook VBA that I can work from. I can't even figure out what the event would be!

Has anyone done this? Would it be easier if I upgraded to Outlook 2002? All help appreciated!

girl with the curly brown hair

Bob Bedell · August 26th, 2003, 09:26 AM

Hi M,

Helen Feddema has done a lot of work with Access/Outlook automation and has a lot of sample code posted on her site. Have a look at http://www.helenfeddema.com/. Follow the 'Code Samples' link and scroll down a bit.

HTH,

Bob

greenconsumerguide · August 29th, 2003, 06:06 AM

I've found Helen's code doesn't work unless you have the database in exactly the same place that she referenced it in her code.

I'm trying to recreate it but am having trouble actually opening a connection to access and then spitting out the data to a table....

Here's what I've done so far...

Code:

Sub export2access()
  Dim appAccess
  Dim olApp As Outlook.Application
  Dim objName As NameSpace
  Dim Folder As MAPIFolder
  Dim objContact As Variant
  Dim objMailItem As Outlook.MailItem
  Dim objAppointment As Outlook.AppointmentItem
  Dim strDBName
  Dim wks
  Dim dbe
  Dim rst
  Dim Item
  Dim strTable

  Set appAccess = CreateObject("Access.Application")
  Set olApp = Outlook.Application
  Set objName = olApp.GetNamespace("MAPI")
  Set Folder = objName.GetDefaultFolder(olFolderContacts)
  strDBName = "c:\outlookdata\contacts.mdb"
  strTable = "contacts"

  appAccess.OpenCurrentDatabase strDBName

  Set dbe = CreateObject("Access.Application")
  Set wks = dbe.Workspaces(0)
  Set dbs = wks.OpenDatabase(strDBName)
  Set rst = dbs.OpenRecordset("contacts")
 
  For Each Item In Folder.Items
    rst.AddNew

    rst.FullName
    rst.FirstName = Item.FirstName
    rst.LastName = Item.LastName
    rst.MiddleName = Item.MiddleName
    rst.Title = Item.Title
    rst.Suffix = Item.Suffix
    rst.NickName = Item.NickName
    rst.CompanyName = Item.CompanyName
    rst.Department = Item.Department
    rst.JobTitle = Item.JobTitle
    rst.BusinessAddress = Item.BusinessAddress
    rst.Categories = Item.Categories
    rst.BusinessAddressStreet = Item.BusinessAddressStreet
    rst.BusinessAddressPostOfficeBox = Item.BusinessPostOfficeBox
    rst.BusinessAddressCity = Item.BusinessAddressCity
    rst.BusinessAddressState = Item.BusinessAddressState
    rst.BusinessAddressPostalCode = Item.BusinessAddressPostalCode
    rst.BusinessAddressCountry = Item.BusinessAddressCountry
    rst.BusinessHomePage = Item.BusinessHomePage
    rst.ComputerNetworkName = Item.ComputerNetworkName
    rst.FTPSite = Item.FTPSite
    rst.HomeAddress = Item.HomeAddress
    rst.HomeAddressStreet = Item.HomeAddressStreet
    rst.HomeAddressPostOfficeBox = Item.HomeAddressPostOfficeBox
    rst.HomeAddressCity = Item.HomeAddressCity
    rst.HomeAddressState = Item.HomeAddressState
    rst.HomeAddressPostalCode = Item.HomeAddressPostalCode
    rst.HomeAddressCountry = Item.HomeAddressCountry
    rst.OtherAddress = Item.OtherAddress
    rst.OtherAddressStreet = Item.OtherAddressStreet
    rst.OtherAddressPostOfficeBox = Item.OtherAddressPostOfficeBox
    rst.OtherAddressCity = Item.OtherAddressCity
    rst.OtherAddressState = Item.OtherAddressState
    rst.OtherAddressPostalCode = Item.OtherAddressPostalCode
    rst.OtherAddressCountry = Item.OtherAddressCountry
    rst.MailingAddress = Item.MailingAddress
    rst.AssistantTelephoneNumber = Item.AssistantTelephoneNumber
    rst.BusinessFaxNumber = Item.BusinessFaxNumber
    rst.BusinessTelephoneNumber = Item.BusinessTelephoneNumber
    rst.Business2TelephoneNumber = Item.Business2TelephoneNumber
    rst.CallbackTelephoneNumber = Item.CallbackTelephoneNumber
    rst.CarTelephoneNumber = Item.CarTelephoneNumber
    rst.CompanyMainTelephoneNumber = Item.CompanyMainTelephoneNumber
    rst.HomeFaxNumber = Item.HomeFaxNumber
    rst.HomeTelephoneNumber = Item.HomeTelephoneNumber
    rst.Home2TelephoneNumber = Item.Home2TelephoneNumber
    rst.ISDNNumber = Item.ISDNNumber
    rst.MobileTelephoneNumber = Item.MobileTelephoneNumber
    rst.OtherFaxNumber = Item.OtherFaxNumber
    rst.OtherTelephoneNumber = Item.OtherTelephoneNumber
    rst.PagerNumber = Item.PagerNumber
    rst.PrimaryTelephoneNumber = Item.PrimaryTelephoneNumber
    rst.RadioTelephoneNumber = Item.RadioTelephoneNumber
    rst.TTYTDDTelephoneNumber = Item.TTYTDDTelephoneNumber
    rst.TelexNumber = Item.TelexNumber
    rst.Account = Item.Account
    rst.Anniversary = Item.Anniversary
    rst.AssistantName = Item.AssistantName
    rst.BillingInformation = Item.BillingInformation
    rst.Birthday = Item.Birthday
    rst.Categories = Item.Categories
    rst.Children = Item.Children
    rst.PersonalHomePage = Item.PersonalHomePage
    rst.Email1Address = Item.Email1Address
    rst.Email1DisplayName = Item.Email1DisplayName
    rst.Email2Address = Item.Email2Address
    rst.Email2DisplayName = Item.Email2DisplayName
    rst.Email3Address = Item.Email3Address
    rst.Email3DisplayName = Item.Email3DisplayName
    rst.Gender = Item.Gender
    rst.GovernmentIDNumber = Item.GovernmentIDNumber
    rst.Hobby = Item.Hobby
    rst.Initials = Item.Initials
    rst.Language = Item.Language
    rst.ManagerName = Item.ManagerName
    rst.Body = Item.Body
    rst.OfficeLocation = Item.OfficeLocation
    rst.OrganizationalIDNumber = Item.OrganizationalIDNumber
    rst.Profession = Item.Profession
    rst.ReferredBy = Item.ReferredBy
    rst.Sensitivity = Item.Sensitivity
    rst.Spouse = Item.Spouse
    rst.User1 = Item.User1
    rst.User2 = Item.User2
    rst.User3 = Item.User3
    rst.User4 = Item.User4
    rst.WebPage = Item.WebPage
    rst.Body = Item.Body

    rst.MoveNext
    rst.Update
  Next
  rst.Close
End Sub

Your help in building a better Outlook Contacts -> Access would be greatly appreciated

Bob Bedell · August 29th, 2003, 12:36 PM

Hi g,

Quote:

quote:I've found Helen's code doesn't work unless you have the database in exactly the same place that she referenced it in her code.

I'm trying to recreate it but am having trouble actually opening a connection to access

Are you referring to:

strDBName = "c:\outlookdata\contacts.mdb"

Since she has the path hardcoded, can't you just re-write the path in your code to the db location on your file system?

I havn't done much work with Outlook/Access automation, but would like to play with it a bit. I'll see what I can come up with. My understanding, though, is that working around MS's security dialogs can be a pain in this kind of setup.

Regards,

Bob

Bob Bedell · August 29th, 2003, 07:47 PM

Hi g,

I just installed Helen's add-in under code sample 38, "The Outlook Automation Access 2002 add-in." Selecting the add-in from the add-in menu creates a macro in any Access 2K database (there is also a 97 and 2000 version) that pulls all the contacts info from outlook into an Access table (table created auto-magically). It worked without a hitch. It'll at least get all the contact fields and data into an Access table which you could then query if you want just selected fields. Obviously, its run from Access, not Outlook.

The .mda file gets installed (on XP) at:

C:\Documents and Settings\Bob Bedell\Application Data\Microsoft\AddIns

You can find the add-in folder path for Access 2000 by checking the Add-In Manager. The Access 2000 version of the add-in is under code sample #18.

Not exactly what you want, but it can get the job done. I've been meaning to get up to speed on the Outlook object model and Outlook automation, so thanks for the push.

HTH,

Bob

Nick Sumner · January 27th, 2005, 02:32 PM

You can link an Access table directly to your Outlook pst. Go to 'Link Tables', then in the dialog box, in the 'files of Type' dropdown box, select 'Outlook()'.