-
1
- #1
Is there any way to link an Excel spreadsheet to the default Outlook contacts folder? I have been searching for a way but have not been able to find one.
The reason I want to do so is because we have been using the following macro to populate a listbox with each contact in the users default contact folder. Using this method takes over 30 seconds to populate the listbox because we have about 1800 contacts.
Private Sub UserForm_Activate()
Dim objOutlook As Outlook.Application
Dim fdrContacts As Outlook.MAPIFolder
Dim itmContacts As Outlook.ContactItem
Dim i As Integer
Dim NameArray() As String
Dim Cnt As Long
Set objOutlook = New Outlook.Application
Set fdrContacts = GetNamespace("MAPI").GetDefaultFolder(olFolderContacts)
For Each itmContacts In fdrContacts.Items
ReDim Preserve NameArray(Cnt)
NameArray(Cnt) = itmContacts.CompanyName & " " & itmContacts.FullName
Cnt = Cnt + 1
Next
WordBasic.SortArray NameArray
For i = 0 To Cnt - 1
lstCompanyList.AddItem NameArray(i)
Next i
End Sub
I have found it much faster to Export all of the Outlook 2003 contacts info to an Excel spreadsheet and the sort by Company, LastName, and FirstName. Once this is done I save the file and use it to populate the listbox which takes less than 1 second to list all 1800 contacts using ADO.
Once the user selects a contact from the listbox I use the index value as the Excel row number. Everything is good except the Excel data becomes out of date requiring the user to again export their contacts over and over in an attempt to keep up to date.
My 1st thought was to try writing a macro that would use Outlook's Import/Export Wizard to export the contacts just before populating the listbox. Assuming that was possible it would still take a considerable amount of time to export the contacts, sort them, save the file, and then populate the listbox.
If there is not a way to link the Excel document to the Contact list is there a way to know the which contacts have been updated, added, and deleted since the last time the Excel file was updated and then open the Excel file and update only the changed information?
The reason I want to do so is because we have been using the following macro to populate a listbox with each contact in the users default contact folder. Using this method takes over 30 seconds to populate the listbox because we have about 1800 contacts.
Private Sub UserForm_Activate()
Dim objOutlook As Outlook.Application
Dim fdrContacts As Outlook.MAPIFolder
Dim itmContacts As Outlook.ContactItem
Dim i As Integer
Dim NameArray() As String
Dim Cnt As Long
Set objOutlook = New Outlook.Application
Set fdrContacts = GetNamespace("MAPI").GetDefaultFolder(olFolderContacts)
For Each itmContacts In fdrContacts.Items
ReDim Preserve NameArray(Cnt)
NameArray(Cnt) = itmContacts.CompanyName & " " & itmContacts.FullName
Cnt = Cnt + 1
Next
WordBasic.SortArray NameArray
For i = 0 To Cnt - 1
lstCompanyList.AddItem NameArray(i)
Next i
End Sub
I have found it much faster to Export all of the Outlook 2003 contacts info to an Excel spreadsheet and the sort by Company, LastName, and FirstName. Once this is done I save the file and use it to populate the listbox which takes less than 1 second to list all 1800 contacts using ADO.
Once the user selects a contact from the listbox I use the index value as the Excel row number. Everything is good except the Excel data becomes out of date requiring the user to again export their contacts over and over in an attempt to keep up to date.
My 1st thought was to try writing a macro that would use Outlook's Import/Export Wizard to export the contacts just before populating the listbox. Assuming that was possible it would still take a considerable amount of time to export the contacts, sort them, save the file, and then populate the listbox.
If there is not a way to link the Excel document to the Contact list is there a way to know the which contacts have been updated, added, and deleted since the last time the Excel file was updated and then open the Excel file and update only the changed information?
