Outlook Address Book contact import from Excel.
HOW TO IMPORT OUTLOOK GLOBAL LIST FROM WITHIN EXCEL
In one of my projects, I needed to import information from outlook into excel. To take on this task, i needed a intro lesson to macros.
What will this macros code do?
The macros code will be activated before right click activity. Once the macros is activated, it will pull up the Global Address Book, there you can search your directory of contacts then select add. Your selection will import that contact (in my case i would just the name, but you can do email and other attributes) .
The following is what i would like it to do:
Here you have the excel sheet, the red area is where i would like to fill a name.
I will select that cell, then right-click. With the right click the Address Book will pop up.
Once you have the address book up, you can select the name you would like to fill.
Then click add.
In this screen shot you can see that the selected cell has been filled.
How To:
Step 1:Once in excel, you will need the developers tab.
Note: To activate developers tab, go to FILE, then Options, select Customize Ribbon, then on the right you will see Developers check box, check it, then press ok.
Step 2: Once you have selected the developers tab, select Visual Basic in the top left corner.
Step 3: Another window will pop up, called Microsoft Visual Basic for Applications. In this window you will need the following code:
Dim cdoSession, cdoAddressBook, olkRecipients, objAE
On Error Resume Next
Set cdoSession = CreateObject("MAPI.Session")
' Change the name of your Outlook profile as needed.
cdoSession.Logon "", "", False, False
Set olkRecipients = cdoSession.AddressBook(, "Global Address List", 0, False)
For Each objAE In olkRecipients
ActiveCell.Value = objAE.Name
Next
Set olkRecipients = Nothing
cdoSession.Logoff
Set cdoSession = Nothing
On Error Resume Next
Set cdoSession = CreateObject("MAPI.Session")
' Change the name of your Outlook profile as needed.
cdoSession.Logon "", "", False, False
Set olkRecipients = cdoSession.AddressBook(, "Global Address List", 0, False)
For Each objAE In olkRecipients
ActiveCell.Value = objAE.Name
Next
Set olkRecipients = Nothing
cdoSession.Logoff
Set cdoSession = Nothing
My code is embedded within the before right click action.
You can chose another action in the same window, upper right corner, drop down.
Note: Make sure you save this file as: Excel Macro-Enabled Workbook.
Note: For this macro to work, outlook has to be already open.
By Dalibor Labudovic
Code credits go to the online community and other forum members.
thanks, was a bit struggle before i read it..
ReplyDelete:)
excel address book
No Problem, i hope it was helpful.
Delete