Solved

Import Company Name from Outlook to a combo box in Excel using VBA

Posted on 2011-09-27
5
453 Views
Last Modified: 2012-05-12
Hello Experts,

I'd like to import all of our contacts (company name) to a combo box via VBA code in Excel.
0
Comment
Question by:Bax060606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 36711196
Here is some code that will get the company names from Outlook, create an array of them, and then paste them to a sheet called Company Names in the current workbook.  You can then use the range of company  names as the row source for your combo box.

If your Office version is older than 2007, replace Folder with MAPIFolder in the declarations.
Public Sub ImportCompanyNames()
'Created by Helen Feddema 27-Sep-2011
'Last modified by Helen Feddema 27-Sep-2011

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Object
   Dim itmContact As Outlook.ContactItem
   Dim fldContacts As Outlook.Folder
   Dim nms As Outlook.Namespace
   Dim strCompanyName As String
   Dim strCompanyNames() As String
   Dim i As Integer
   Dim intUBound As Integer
   Dim rng As Excel.Range
   
   Set nms = appOutlook.GetNamespace("MAPI")
   Set fldContacts = nms.GetDefaultFolder(olFolderContacts)
   intUBound = fldContacts.Items.Count
   Debug.Print "No. of contacts: " & intUBound
   ReDim strCompanyNames(intUBound)
   
   i = 1
   
   For Each itm In fldContacts.Items
      If itm.Class = olContact Then
         Set itmContact = itm
         strCompanyName = itmContact.CompanyName
         If strCompanyName <> "" Then
            Debug.Print "Using company name: " & strCompanyName
            strCompanyNames(i) = strCompanyName
            i = i + 1
         End If
      End If
   Next itm
   
   'Paste array to worksheet
   Set rng = Application.Sheets("Company Names").Range("A1")
   
   For i = 1 To intUBound
      rng.Value = strCompanyNames(i)
      Set rng = Application.ActiveCell.Offset(rowoffset:=i)
   Next i
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in ImportCompanyNames procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:Bax060606
ID: 36713354
Beautiful, would I be able to easily cross reference other pertinant information easily. ie if I loaded this list to a combo box and someone selected company 'ABC' could I autopopulate other fields such as the contact name email etc.

Keep in mind I'm looking for an easy solution for instance, If there is an index number you are keeping track of in your code so I don't have to write a complicated routine for cross referencing. Otherwise no need to answer as I will go about it the long way.
0
 

Author Closing Comment

by:Bax060606
ID: 36892097
No Comments were added to the code
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36892193
You could expand the array to store as many fields as you wish, and then they would be available for selection in the combo box.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36892203
If you wish, you could post another question, mentioning which fields you want to store, and I could do some modified code.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help to break down spreadsheet 3 38
VB script help 23 30
Exchange 2007 Outlook 2010 1 17
Need adjust data counting 17 14
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question