Copy Contacts from Access to Outlook

With a few lines of code you can copy recordsets from an Access database to Outlook.

Last modified: 2015/08/06 | Accessed: 62.987  | #67
◀ Previous sample Next sample ▶
Reporter Reporter
VBOffice Reporter is an easy to use tool for data analysis and reporting in Outlook. A single click, for instance, allows you to see the number of hours planned for meetings the next month.

This sample reads the fields 'Fullname' and 'EMailAddress' from the table 'Customers' of your Access database, and it creates for every found recordset a new contact in Outlook.

In the procedure 'ConnectDB' change the values for the variables 'File' (file name of your Access database), 'Fields' (list of field names to be read), and 'Table' (name of the database table). Repeat the field names in the procedure 'AddContactsFromAccess' as shown, and assign the values to the appropriate ContactItem properties.

You also need to add a reference to a 'Microsoft ActiveX Data Objects x Library' via Tools/References. If there's more than one library of that type, choose the most current one. For an Access 2007 database (*.accdb file) or higher you need version 6.0 at least.

tip  How to add macros to Outlook
Private m_Cn As ADODB.Connection
Private m_Rs As ADODB.Recordset

Public Sub AddContactsFromAccess()
  Dim Contact As Outlook.ContactItem
  Dim Name As String
  Dim Fields As ADODB.Fields

  Set Fields = m_Rs.Fields

  While Not m_Rs.EOF
    Set Contact = Application.CreateItem(olContactItem)

    Name = "Fullname"
    If IsNull(Fields(Name).Value) = False Then
      Contact.FullName = Fields(Name).Value
    End If

    Name = "EMailAddress"
    If IsNull(Fields(Name).Value) = False Then
      Contact.FullName = Fields(Name).Value
    End If


  m_Rs.Clone: Set m_Rs = Nothing
  m_Cn.Close: Set m_Cn = Nothing
End Sub

Private Sub ConnectDB()
  Dim File As String
  Dim Table As String
  Dim Fields As String

  File = "c://test.accdb"

  Fields = "Fullname, EMailAddress"
  Table = "Customers"

  Set m_Cn = New ADODB.Connection
  With m_Cn
    'Access 2007 or higher (*.accdb file) 
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    'Access 2003 or older (*.mdb file) 
    '.Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = File
    .CursorLocation = adUseClient
    .Mode = adModeShareDenyNone
  End With

  Set m_Rs = New ADODB.Recordset
  With m_Rs
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    Set .ActiveConnection = m_Cn
    .Open ("SELECT " & Fields & " FROM " & Table)
  End With
End Sub
OLKeeper OLKeeper
OLKeeper reliably prevents users from closing their Outlook window and thus possibly missing reminders or e-mails.
email  Send a message