Here is what I use to Get E-mail addresses. You can modify it to get the
other address info you need. You will need to reference the Microsoft CDO
Library.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("Jim Thomlinson")
End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function




"Jeff" wrote:

Hello,

I am sure there is a way to get Outlook Address Book info into an Excel
Worksheet. How can this be done ?

Basically I would like all the info from all the people in my "Global
Address Book".

In the Properties of each adderess, there is the General Tab, Organization
Tab, Phone/Notes Tab, Member Of Tab, E-mail Address Tab.

I would like all this information in Excel.

Again how can this be done with VBA code or is their some way to export all
that info directly to Excel.

Any help would be greatly appreciated.

Thank you,
Jeff

Hi Jim,

Thank you for your reply.

But I have a couple of questions..

1) when I run this a Msgbox pops up saying
"The profile name is Not Valid. Enter a Valid Profile Name"
OK Cancel

If Click OK, then another Form pops up with a Combo Box to choose a Profile
Name. Its only Item is "Default Outlook Profile". When I click OK then it
runs. Is their a way to eliminate this ?


2) More importantly how do you get the other info ?
You have this Constant

Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E

& then later
Set objField = Entry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

What do I need to change it to to reference the other info in the Address
book ? Again I would like all of it.

Thank you,
Jeff

"Jim Thomlinson" wrote:

Here is what I use to Get E-mail addresses. You can modify it to get the
other address info you need. You will need to reference the Microsoft CDO
Library.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("Jim Thomlinson")
End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function




"Jeff" wrote:

Hello,

I am sure there is a way to get Outlook Address Book info into an Excel
Worksheet. How can this be done ?

Basically I would like all the info from all the people in my "Global
Address Book".

In the Properties of each adderess, there is the General Tab, Organization
Tab, Phone/Notes Tab, Member Of Tab, E-mail Address Tab.

I would like all this information in Excel.

Again how can this be done with VBA code or is their some way to export all
that info directly to Excel.

Any help would be greatly appreciated.

Thank you,
Jeff

if you go to the microsoft website you can get more info by searching for
CdoPR_EMS_AB_PROXY_ADDRESSES or for more info on the CDO Library.

"Jeff" wrote:

Hi Jim,

Thank you for your reply.

But I have a couple of questions..

1) when I run this a Msgbox pops up saying
"The profile name is Not Valid. Enter a Valid Profile Name"
OK Cancel

If Click OK, then another Form pops up with a Combo Box to choose a Profile
Name. Its only Item is "Default Outlook Profile". When I click OK then it
runs. Is their a way to eliminate this ?


2) More importantly how do you get the other info ?
You have this Constant

Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E

& then later
Set objField = Entry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

What do I need to change it to to reference the other info in the Address
book ? Again I would like all of it.

Thank you,
Jeff

"Jim Thomlinson" wrote:

Here is what I use to Get E-mail addresses. You can modify it to get the
other address info you need. You will need to reference the Microsoft CDO
Library.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("Jim Thomlinson")
End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function




"Jeff" wrote:

Hello,

I am sure there is a way to get Outlook Address Book info into an Excel
Worksheet. How can this be done ?

Basically I would like all the info from all the people in my "Global
Address Book".

In the Properties of each adderess, there is the General Tab, Organization
Tab, Phone/Notes Tab, Member Of Tab, E-mail Address Tab.

I would like all this information in Excel.

Again how can this be done with VBA code or is their some way to export all
that info directly to Excel.

Any help would be greatly appreciated.

Thank you,
Jeff

Hi Jim,

When I searched on Micorsoft's Website & even searched through Google, for
both CdoPR_EMS_AB_PROXY_ADDRESSES & CDO there is no information on
retrieving the other info this is just for Email Address info.

Can you be of more assitance ? Is it even possible ? Can you provide the
code ?

Though more research, in Word VBA, there is a GetAddress Method & from there
you can get most of the fields. But again how can you do it in Excel or even
Access ?

Thank you,
Jeff

"Jim Thomlinson" wrote:

if you go to the microsoft website you can get more info by searching for
CdoPR_EMS_AB_PROXY_ADDRESSES or for more info on the CDO Library.

"Jeff" wrote:

Hi Jim,

Thank you for your reply.

But I have a couple of questions..

1) when I run this a Msgbox pops up saying
"The profile name is Not Valid. Enter a Valid Profile Name"
OK Cancel

If Click OK, then another Form pops up with a Combo Box to choose a Profile
Name. Its only Item is "Default Outlook Profile". When I click OK then it
runs. Is their a way to eliminate this ?


2) More importantly how do you get the other info ?
You have this Constant

Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E

& then later
Set objField = Entry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

What do I need to change it to to reference the other info in the Address
book ? Again I would like all of it.

Thank you,
Jeff

"Jim Thomlinson" wrote:

Here is what I use to Get E-mail addresses. You can modify it to get the
other address info you need. You will need to reference the Microsoft CDO
Library.

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"


Private Sub Test()

MsgBox GetEMailAddress("Jim Thomlinson")
End Sub


Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function




"Jeff" wrote:

Hello,

I am sure there is a way to get Outlook Address Book info into an Excel
Worksheet. How can this be done ?

Basically I would like all the info from all the people in my "Global
Address Book".

In the Properties of each adderess, there is the General Tab, Organization
Tab, Phone/Notes Tab, Member Of Tab, E-mail Address Tab.

I would like all this information in Excel.

Again how can this be done with VBA code or is their some way to export all
that info directly to Excel.

Any help would be greatly appreciated.

Thank you,
Jeff