Solved

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

Posted on 2011-09-27
5
443 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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