Unfiltered HTML
Unfiltered HTML

Integrating Access Contacts with SharePoint and Outlook

by
on August 28

There was a question on our post about getting Access to recognize tables as specific WSS table templates around what else you need to do to get the new Add From Outlook feature to work on a custom contacts table.  This feature uses the same mechanism to determine that a table is a contact table, but more than that it needs to know how to map fields in your table to those in the Outlook contact store.  There are a bunch of fields, so easier than building this out yourself, here's a quick snippet of VBA that will take any table, and make it into a contacts table.  Just plop this into your solution, then update the table name and field names to reflect the fields in your custom contacts implementation, and run it.

' -----------------------------------------------
' READ THIS
' Make edits to the string values in the
' MakeContacts Sub below to match up
' with your table and field names.
'
' You shouldn't need to change the rest of
' this code.
' -----------------------------------------------

' CI is for ContactInfo
Enum CI
First
Last
Email
Company
JobTitle
WorkPhone
HomePhone
CellPhone
WorkFax
WorkAddress
WorkCity
WorkState
WorkZip
WorkCountry
WebPage
Comments
End Enum

' Each field map item maps between a field
' name in the Access table and a property
' name Access maps to Outlook and SharePoint
' contact field info.
Type FieldMap
prop As String
Field As String
End Type

' This Varible lists all the proeprties that
' can be mapped to Contact info. You fill
' it with the corresponding field names from
' your table.
Dim Map(CI.Comments) As FieldMap

Public Sub MakeContacts()

Dim strTable As String
Dim fm As FieldMap
Dim td As TableDef
Dim db As Database

' -----------------------------------------------
' UPDATE THESE STRINGS
' to match the table and field names in
' your app. It is okay not to set some if you
' don't have an equivalent.

' Set this to your table name
strTable = "Table1"

' Set these to your field names
Map(CI.First).Field = "First"
Map(CI.Last).Field = "Last"
Map(CI.Email).Field = "Email"
Map(CI.Company).Field = "Org"
Map(CI.JobTitle).Field = "Job"
Map(CI.WorkPhone).Field = "Work"
Map(CI.HomePhone).Field = "Home"
Map(CI.CellPhone).Field = "Cell"
Map(CI.WorkFax).Field = "Fax"
Map(CI.WorkAddress).Field = "Addr"
Map(CI.WorkCity).Field = "City"
Map(CI.WorkState).Field = "State"
Map(CI.WorkZip).Field = "Zip"
Map(CI.WorkCountry).Field = "Country"
Map(CI.WebPage).Field = "WWW"
Map(CI.Comments).Field = "Notes"

' END OF STRINGS TO UPDATE
' -----------------------------------------------

' This the code to mark fields in
' your local table with the correct
' Outlook and SharePoint field names.
'
' You shouldn't need to change this.
SetupContactProps
Set db = CurrentDb
Set td = db.TableDefs(strTable)

' Set the table level property that tells Access
' this is a Contact table.
SetProp td, "WSSTemplateID", dbInteger, 105

' For each mapped field, set the correct
' contacts property.
For i = 0 To CI.Comments
fm = Map(i)
If Len(fm.Field) > 0 Then
SetProp td.Fields(fm.Field), "WSSFieldID", dbText, fm.prop
End If
Next

End Sub

' This code initializes the contact property
' names that Access uses to map contact info
' to SharePoint or Outlook.
'
' You shouldn't need to change this.
Sub SetupContactProps()

Map(CI.First).prop = "FirstName"
Map(CI.Last).prop = "Title"
Map(CI.Email).prop = "Email"
Map(CI.Company).prop = "Company"
Map(CI.JobTitle).prop = "JobTitle"
Map(CI.WorkPhone).prop = "WorkPhone"
Map(CI.HomePhone).prop = "HomePhone"
Map(CI.CellPhone).prop = "CellPhone"
Map(CI.WorkFax).prop = "WorkFax"
Map(CI.WorkAddress).prop = "WorkAddress"
Map(CI.WorkCity).prop = "WorkCity"
Map(CI.WorkState).prop = "WorkState"
Map(CI.WorkZip).prop = "WorkZip"
Map(CI.WorkCountry).prop = "WorkCountry"
Map(CI.WebPage).prop = "WebPage"
Map(CI.Comments).prop = "Comments"

End Sub

' This is a helper routine which sets a property
' value first checking to see whether one already
' exists.
Sub SetProp(o As Object, strProp As String, dbType As DataTypeEnum, oValue As Variant)

Dim p As Property

On Error GoTo NotFound
Set p = o.Properties(strProp)
GoTo Found

NotFound:
Set p = CurrentDb.CreateProperty(strProp, dbType, oValue)
o.Properties.Append p

Found:
If p.Type = dbType Then
p.value = oValue
Else
o.Properties.Delete (strProp)
Set p = CurrentDb.CreateProperty(strProp, dbType, oValue)
End If

End Sub
Tags:
, ,

Office Blogs Comments

Comments: (7) Collapse

  • michelebkraus
    14,927 Posts

    zac, I'm not a developer but I want to put my contacts that are in a Public folder currently into SharePoint. I've spent several days exporting and importing data between Access and SharePoint and I'm not getting the data in the format I want. I want all Outlook fields (title, categories, user1, etc) to be in SharePoint AND I want the list I've created to then be a "Conact" content type so I can 1) Add back to Outlook and 2) Add a new contact giving me all the fields to populate. Is no one moving ALL their contact information to sharepoint????

    29 Aug 2007
  • Bill Forney
    14,927 Posts

    I am getting: Run-time error '13':

    Type mismatch from this code... Suggestions?

    29 Aug 2007
  • Tom Wickerath
    14,927 Posts

    Hi Bill, Make the following change in the helper routine: Change:

    Dim p As Property To:

    Dim p As DAO.Property Also, make sure that you have a reference set to the DAO Object Library (Tools > References listing). As an alternative, you can deselect the checked reference to the ADO object library ("Microsoft ActiveX Data Objects {version} Library"), however, I recommend that you use the explicit declaration instead. This code suffers from a reference priority problem. To read more about this, please see the following article: ADO and DAO Library References in Access Databases www.access.qbuilt.com/.../ado_and_dao.html While you are at it, add Option Explicit as the second line of code at the top of the module, if this most important line of code is missing, ie: Option Compare Database

    Option Explicit and then add the following declaration in the Public Sub MakeContacts() subroutine: Dim strTable As String

    Dim fm As FieldMap

    Dim td As TableDef

    Dim db As Database

    Dim i As Integer '<---Add this To read more about why Option Explicit is so important, please see the following article: Always Use Option Explicit www.access.qbuilt.com/.../gem_tips.html I don't have SharePoint set up to actually test this code, but I was able to duplicate the error you reported. Tom

    30 Aug 2007
  • Zac Woodall
    14,927 Posts

    Bill, are you using Option Explicit? If so, you'll need to "Dim i as Integer" at the top of MakeContacts. Michele, you might be able to do it this way: 1) import your Outlook contacts into Access using the Outlook/Exchange import wizard (External Data-->Import-->More-->Outlook Folder)

    2) run the code from this post on it (fixing up field names correctly)

    3) export the table from Access to Sharepoint

    4) go to the sharepoint list and choose open with outlook HTH!

    30 Aug 2007
  • Seth Bacon
    14,927 Posts

    Thanks for the post! Great example code. - Seth

    30 Aug 2007
  • Tom Wickerath
    14,927 Posts

    Hi Bill, I posted the following reply last night, but for some reason unknown to me, it did not show up. This is a second attempt.

    ==============================

    Hi Bill, Make the following change in the helper routine: Change:

    Dim p As Property To:

    Dim p As DAO.Property Also, make sure that you have a reference set to the DAO Object Library (Tools > References listing). As an alternative, you can deselect the checked reference to the ADO object library ("Microsoft ActiveX Data Objects {version} Library"), however, I recommend that you use the explicit declaration instead. This code suffers from a reference priority problem. To read more about this, please see the following article: ADO and DAO Library References in Access Databases www.access.qbuilt.com/.../ado_and_dao.html While you are at it, add Option Explicit as the second line of code at the top of the module, if this most important line of code is missing, ie: Option Compare Database

    Option Explicit and then add the following declaration in the Public Sub MakeContacts() subroutine: Dim strTable As String

    Dim fm As FieldMap

    Dim td As TableDef

    Dim db As Database

    Dim i As Integer '<---Add this To read more about why Option Explicit is so important, please see the following article: Always Use Option Explicit www.access.qbuilt.com/.../gem_tips.html I don't have SharePoint set up to actually test this code, but I was able to duplicate the error you reported. Tom

    31 Aug 2007
  • Lorenzo
    14,927 Posts

    Well, good morning to all from italy

    My problem is this.

    With Outlook 2007 and BCM i want to import information in BCM from a database standard downloaded from OfficeOnline as Attività.accdb

    How i can make , if the SQL Express 2005 engine not recognize this format of database?

    thanks to all for the repaly and sorry for my bad english

    hi

    31 Aug 2007
Comments

Comments: (loading) Collapse