Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > accessing Outlook Address book from Excel VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,646
Default accessing Outlook Address book from Excel VBA
Hi All,

I'd like to retrieve some data (name, department, e-mail address) of members
of a group from Outlook Address book from Excel VBA. Please, help me how to
do it!

Thanks,
Stefi

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 421
Default accessing Outlook Address book from Excel VBA
Hi Steffi,

Add a reference to the Outlook library:

Alt-F11 to open the VBE,

Menu | Tools | References |
Find and check: Microsoft Outlook xx Object Library
(xx is the version number.)

Insert a Userfom with a ListBox and
a CommandButton; in the Userform
module post the following code:

'=============
Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oContact As Outlook.ContactItem
Dim oContactFolder As Outlook.MAPIFolder
Dim oContactItems As Outlook.Items
Dim oNS As Outlook.Namespace
Dim i As Long
Dim j As Long
Dim arr()

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
Set oContactItems = oContactFolder.Items

With Me
For i = 1 To oContactItems.Count
If oContactItems.Item(i).Class = olContact Then
Set oContact = oContactItems.Item(i)
j = j + 1
ReDim Preserve arr(0 To 2, 1 To j)
With oContact
arr(0, j) = .FullName
arr(1, j) = .HomeAddress
arr(2, j) = .HomeTelephoneNumber
End With
End If
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oContact = Nothing
Set oContactItems = Nothing
Set oContactFolder = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
With Me.ListBox1
destRng.Value = .List(.ListIndex, 0)
destRng(1, 2).Value = .List(.ListIndex, 1)
destRng(1, 2).Value = .List(.ListIndex, 2)
End With
End Sub
'<<=============



---
Regards.
Norman


"Stefi" wrote in message
...
Hi All,

I'd like to retrieve some data (name, department, e-mail address) of
members
of a group from Outlook Address book from Excel VBA. Please, help me how
to
do it!

Thanks,
Stefi


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,646
Default accessing Outlook Address book from Excel VBA
Thanks, Norman, I'm going to give it a try and let you know the result later!
Stefi


€˛Norman Jones€¯ ezt Ć*rta:

Hi Steffi,

Add a reference to the Outlook library:

Alt-F11 to open the VBE,

Menu | Tools | References |
Find and check: Microsoft Outlook xx Object Library
(xx is the version number.)

Insert a Userfom with a ListBox and
a CommandButton; in the Userform
module post the following code:

'=============
Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oContact As Outlook.ContactItem
Dim oContactFolder As Outlook.MAPIFolder
Dim oContactItems As Outlook.Items
Dim oNS As Outlook.Namespace
Dim i As Long
Dim j As Long
Dim arr()

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
Set oContactItems = oContactFolder.Items

With Me
For i = 1 To oContactItems.Count
If oContactItems.Item(i).Class = olContact Then
Set oContact = oContactItems.Item(i)
j = j + 1
ReDim Preserve arr(0 To 2, 1 To j)
With oContact
arr(0, j) = .FullName
arr(1, j) = .HomeAddress
arr(2, j) = .HomeTelephoneNumber
End With
End If
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oContact = Nothing
Set oContactItems = Nothing
Set oContactFolder = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
With Me.ListBox1
destRng.Value = .List(.ListIndex, 0)
destRng(1, 2).Value = .List(.ListIndex, 1)
destRng(1, 2).Value = .List(.ListIndex, 2)
End With
End Sub
'<<=============



---
Regards.
Norman


"Stefi" wrote in message
...
Hi All,

I'd like to retrieve some data (name, department, e-mail address) of
members
of a group from Outlook Address book from Excel VBA. Please, help me how
to
do it!

Thanks,
Stefi


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,646
Default accessing Outlook Address book from Excel VBA
Hi Norman,

Your code works, but I have still a problem:

Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
returns my personal Address book, but I need a group and its members from
the company's global Address book. I can't find out how to identify and
access it.

Please help!

Regards,
Stefi


€˛Stefi€¯ ezt Ć*rta:

Thanks, Norman, I'm going to give it a try and let you know the result later!
Stefi


€˛Norman Jones€¯ ezt Ć*rta:

Hi Steffi,

Add a reference to the Outlook library:

Alt-F11 to open the VBE,

Menu | Tools | References |
Find and check: Microsoft Outlook xx Object Library
(xx is the version number.)

Insert a Userfom with a ListBox and
a CommandButton; in the Userform
module post the following code:

'=============
Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oContact As Outlook.ContactItem
Dim oContactFolder As Outlook.MAPIFolder
Dim oContactItems As Outlook.Items
Dim oNS As Outlook.Namespace
Dim i As Long
Dim j As Long
Dim arr()

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
Set oContactItems = oContactFolder.Items

With Me
For i = 1 To oContactItems.Count
If oContactItems.Item(i).Class = olContact Then
Set oContact = oContactItems.Item(i)
j = j + 1
ReDim Preserve arr(0 To 2, 1 To j)
With oContact
arr(0, j) = .FullName
arr(1, j) = .HomeAddress
arr(2, j) = .HomeTelephoneNumber
End With
End If
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oContact = Nothing
Set oContactItems = Nothing
Set oContactFolder = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
With Me.ListBox1
destRng.Value = .List(.ListIndex, 0)
destRng(1, 2).Value = .List(.ListIndex, 1)
destRng(1, 2).Value = .List(.ListIndex, 2)
End With
End Sub
'<<=============



---
Regards.
Norman


"Stefi" wrote in message
...
Hi All,

I'd like to retrieve some data (name, department, e-mail address) of
members
of a group from Outlook Address book from Excel VBA. Please, help me how
to
do it!

Thanks,
Stefi


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 421
Default accessing Outlook Address book from Excel VBA
Hi Stefi,

Using Outlook methods, I do not believe
that you interrogate the Global Address
book (GAL) to obtain the department
details.

To return the name and email details, in
the Userform module, try something like:

'=============
Option Explicit
Dim arr() As String

Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oNS As Outlook.Namespace

Dim oAL As AddressList
Dim oAE As AddressEntry
Dim i As Long
Dim j As Long

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oAL = oNS.AddressLists(1)

With Me
For i = 1 To oAL.AddressEntries.Count
Set oAE = oAL.AddressEntries.Item(i)
j = j + 1
ReDim Preserve arr(1 To 3, 1 To j)
With oAE
arr(1, j) = .Name
arr(2, j) = .Address
arr(3, j) = .GetContact
End With
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oAE = Nothing
Set oAL = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Foglio1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
destRng.Resize(UBound(arr, 2), 2).Value = _
Application.Transpose(arr) 'arr2
End Sub
'<<=============

In order to interrogate the GAL further, see
the techniques used by Pavel Nagaev at:

Import Active Directory user data into Outlook address books
http://www.outlookexchange.com/artic...agaev_c1p4.asp

---
Regards.
Norman


"Stefi" wrote in message
...
Hi Norman,

Your code works, but I have still a problem:

Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
returns my personal Address book, but I need a group and its members from
the company's global Address book. I can't find out how to identify and
access it.

Please help!

Regards,
Stefi


€˛Stefi€¯ ezt Ć*rta:

Thanks, Norman, I'm going to give it a try and let you know the result
later!
Stefi


€˛Norman Jones€¯ ezt Ć*rta:

Hi Steffi,

Add a reference to the Outlook library:

Alt-F11 to open the VBE,

Menu | Tools | References |
Find and check: Microsoft Outlook xx Object Library
(xx is the version number.)

Insert a Userfom with a ListBox and
a CommandButton; in the Userform
module post the following code:

'=============
Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oContact As Outlook.ContactItem
Dim oContactFolder As Outlook.MAPIFolder
Dim oContactItems As Outlook.Items
Dim oNS As Outlook.Namespace
Dim i As Long
Dim j As Long
Dim arr()

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
Set oContactItems = oContactFolder.Items

With Me
For i = 1 To oContactItems.Count
If oContactItems.Item(i).Class = olContact Then
Set oContact = oContactItems.Item(i)
j = j + 1
ReDim Preserve arr(0 To 2, 1 To j)
With oContact
arr(0, j) = .FullName
arr(1, j) = .HomeAddress
arr(2, j) = .HomeTelephoneNumber
End With
End If
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oContact = Nothing
Set oContactItems = Nothing
Set oContactFolder = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
With Me.ListBox1
destRng.Value = .List(.ListIndex, 0)
destRng(1, 2).Value = .List(.ListIndex, 1)
destRng(1, 2).Value = .List(.ListIndex, 2)
End With
End Sub
'<<=============



---
Regards.
Norman


"Stefi" wrote in message
...
Hi All,

I'd like to retrieve some data (name, department, e-mail address) of
members
of a group from Outlook Address book from Excel VBA. Please, help me
how
to
do it!

Thanks,
Stefi



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,646
Default accessing Outlook Address book from Excel VBA
Thanks Norman for your guidance, it'll be a nice job to follow it!
Regards,
Stefi


€˛Norman Jones€¯ ezt Ć*rta:

Hi Stefi,

Using Outlook methods, I do not believe
that you interrogate the Global Address
book (GAL) to obtain the department
details.

To return the name and email details, in
the Userform module, try something like:

'=============
Option Explicit
Dim arr() As String

Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oNS As Outlook.Namespace

Dim oAL As AddressList
Dim oAE As AddressEntry
Dim i As Long
Dim j As Long

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oAL = oNS.AddressLists(1)

With Me
For i = 1 To oAL.AddressEntries.Count
Set oAE = oAL.AddressEntries.Item(i)
j = j + 1
ReDim Preserve arr(1 To 3, 1 To j)
With oAE
arr(1, j) = .Name
arr(2, j) = .Address
arr(3, j) = .GetContact
End With
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oAE = Nothing
Set oAL = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Foglio1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
destRng.Resize(UBound(arr, 2), 2).Value = _
Application.Transpose(arr) 'arr2
End Sub
'<<=============

In order to interrogate the GAL further, see
the techniques used by Pavel Nagaev at:

Import Active Directory user data into Outlook address books
http://www.outlookexchange.com/artic...agaev_c1p4.asp

---
Regards.
Norman


"Stefi" wrote in message
...
Hi Norman,

Your code works, but I have still a problem:

Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
returns my personal Address book, but I need a group and its members from
the company's global Address book. I can't find out how to identify and
access it.

Please help!

Regards,
Stefi


€˛Stefi€¯ ezt Ć*rta:

Thanks, Norman, I'm going to give it a try and let you know the result
later!
Stefi


€˛Norman Jones€¯ ezt Ć*rta:

Hi Steffi,

Add a reference to the Outlook library:

Alt-F11 to open the VBE,

Menu | Tools | References |
Find and check: Microsoft Outlook xx Object Library
(xx is the version number.)

Insert a Userfom with a ListBox and
a CommandButton; in the Userform
module post the following code:

'=============
Private Sub UserForm_Initialize()
Dim olApp As Outlook.Application
Dim oContact As Outlook.ContactItem
Dim oContactFolder As Outlook.MAPIFolder
Dim oContactItems As Outlook.Items
Dim oNS As Outlook.Namespace
Dim i As Long
Dim j As Long
Dim arr()

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "90 pt;72 pt;90 pt"
.TextColumn = -1
End With

On Error GoTo XIT
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Set oContactFolder = oNS.GetDefaultFolder(olFolderContacts)
Set oContactItems = oContactFolder.Items

With Me
For i = 1 To oContactItems.Count
If oContactItems.Item(i).Class = olContact Then
Set oContact = oContactItems.Item(i)
j = j + 1
ReDim Preserve arr(0 To 2, 1 To j)
With oContact
arr(0, j) = .FullName
arr(1, j) = .HomeAddress
arr(2, j) = .HomeTelephoneNumber
End With
End If
Next i
Me.ListBox1.List() = Application.Transpose(arr)
End With

XIT:
Set oContact = Nothing
Set oContactItems = Nothing
Set oContactFolder = Nothing
Set oNS = Nothing
Set olApp = Nothing
End Sub

'-----------------
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim destRng As Range

Set SH = ThisWorkbook.Sheets("Sheet1") '<<=== CHANGE

Set destRng = SH.Range("A" & Rows.Count).End(xlUp)(2)
With Me.ListBox1
destRng.Value = .List(.ListIndex, 0)
destRng(1, 2).Value = .List(.ListIndex, 1)
destRng(1, 2).Value = .List(.ListIndex, 2)
End With
End Sub
'<<=============



---
Regards.
Norman


"Stefi" wrote in message
...
Hi All,

I'd like to retrieve some data (name, department, e-mail address) of
members
of a group from Outlook Address book from Excel VBA. Please, help me
how
to
do it!

Thanks,
Stefi



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Accessing Outlooks address book via VBA embedded in Excel Phil Martin Excel Programming 5 January 30th 08 02:56 PM
control Outlook Address book from Excel VBA PhilM[_2_] Excel Programming 0 October 17th 07 02:02 PM
control Outlook Address book from Excel VBA PhilM[_2_] Excel Programming 0 October 17th 07 01:59 PM
Using Outlook address book in Excel Diane Hazen Excel Discussion (Misc queries) 1 April 19th 05 08:39 AM
displaying the outlook address book in excel mahesh Excel Programming 1 February 10th 04 10:27 AM


All times are GMT +1. The time now is 04:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement