Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 25th, 2003, 06:39 PM
Registered User
 
Join Date: Aug 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Outlook 2000 Contacts into Access

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
 
Old August 26th, 2003, 09:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old August 29th, 2003, 06:06 AM
Registered User
 
Join Date: Aug 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 29th, 2003, 12:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



 
Old August 29th, 2003, 07:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old January 27th, 2005, 02:32 PM
Registered User
 
Join Date: Jan 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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()'.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook contacts to Access lryckman Access 1 January 22nd, 2008 08:11 AM
Update Outlook contacts from Access nosegewa Access 0 August 20th, 2006 04:15 PM
outlook contacts page davesav VB How-To 1 February 18th, 2006 07:57 PM
Outlook 2000 errors byron Pro VB.NET 2002/2003 3 December 1st, 2003 10:17 AM
Access 2000 and Outlook vaheh Access VBA 1 August 13th, 2003 03:18 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.