如何根据Excel（VBA）中的值从Outlook通讯录中拉出 [英] How to pull from Outlook Address book based on values in Excel (VBA)

问题描述

我有以下代码（我在论坛上发现）：

I have the following code that works (I found it on a forum):

Public Sub GetUsers()
Dim myolApp As Outlook.Application
Dim myNameSpace As Namespace
Dim myAddrList As AddressList
Dim myAddrEntries As addressEntry
Dim AliasName As String
Dim i As Integer, r As Integer
Dim EndRow As Integer, n As Integer
Dim myStr As String, c As Range
Dim myPhone As String
'Dim propertyAccessor As Outlook.propertyAccessor  'This only works with 2007 and may help you out

Set myolApp = CreateObject("Outlook.Application")
Set myNameSpace = myolApp.GetNamespace("MAPI")
Set myAddrList = myNameSpace.addressLists("Global Address List")

Dim FullName As String, LastName As String, FirstName As String
Dim StartRow As Integer

EndRow = Cells(Rows.Count, 3).End(xlUp).Row

StartRow = InputBox("At which row should this start?", "Start Row", 4)

For Each c In Range("A" & StartRow & ":A" & CStr(EndRow))
    AliasName = LCase(Trim(c))
    c = AliasName
    Set myAddrEntries = myAddrList.addressEntries(AliasName)

    FullName = myAddrEntries.Name
    FirstName = Trim(Mid(FullName, InStr(FullName, "(") + 1, _
                    InStrRev(FullName, " ") - InStr(FullName, "(")))
    LastName = Right(FullName, Len(FullName) - InStrRev(FullName, " "))
    LastName = Left(LastName, Len(LastName) - 1)

    c.Offset(0, 1) = FirstName
    c.Offset(0, 2) = LastName
    c.Offset(0, 3) = FirstName & " " & LastName
Next c
End Sub

当我提供单一名称（第一个或最后一个）时，它在通讯录中查找它，返回找到的人的名字和姓氏。

When I provide a single name (first or last) it looks for it in the address book and returns the first and last names of the person it found.

我想提供该人的企业ID，查找该人员，然后返回其他信息（位置，电话号码等）。

I want to provide the enterprise ID of the person, have it look for that and then return other information (location, phone number etc).

我不知道如何d那个。首先，我不知道outlook如何知道只搜索别名，据我所知，这只是在局部变量中声明。另外，当我尝试提取其他信息时，例如：

I can't figure out how to do that. First of all, I don't know how outlook knows to search only Alias, as far as I can tell that's only declared in local variables. Also, when I try to pull out other information, for example:

HomeState = myAddrEntries.HomeState

我收到一个错误：Object不支持此属性或方法。我不知道该属性将被称为 - 我找不到任何文档在线显示属性如何命名（即使我搜索MAPI文件）。

I get an error: Object doesn't support this property or method. I don't know what that property would be called - I couldn't find any doc online that showed how properties are named (even when I searched for MAPI docuemntation).

SO，我的问题是 - 如何使用此代码按ID搜索并返回其他属性，如位置，数字等。另外 - 我如何推广该过程 - 有没有列出这些字段名称的名字，有没有办法生成列表？

SO, my question is - how can I use this code to search by ID and return other properties such as location, number etc. ALso - how can I generalize that process - is there a list of what those field names are called, is there a way to generate a list?

谢谢！

推荐答案

我们来看看这可以帮助你吗？我不是Outlook VBA的专家，但它大体上是一样的，只是一个找到文档的问题。

Let's see if this can help you out. I am not an expert with Outlook VBA but it is mostly the same, and just a matter of finding the documentation.

收藏此页面：

http：// msdn。 microsoft.com/en-us/library/office/ff870566(v=office.14).aspx

具体来说，您可以查看条目 AddressEntry 对象：

Specifically then you could look at the entry for AddressEntry object:

http://msdn.microsoft.com/en-us/library/office/ff870588（v = office.14）的.aspx

从那里可以看到可用属性/方法的列表。我相信应该回答你的第二个问题，我发现一个错误：对象不支持这个属性或方法。我不知道该物业将被称为。

And from there you can see the list of available properties/methods. I believe that should answer your second question, I get an error: Object doesn't support this property or method. I don't know what that property would be called.

Homestate 不属于一个 AddressEntry 对象。

当我提供单一名称（第一个或最后一个）时，它在地址簿中查找它，并返回

When I provide a single name (first or last) it looks for it in the address book and returns the first and last names of the person it found.

不要指望这是100％可靠

我用6个名字测试了它，它有4个正确。 3名是罕见的姓氏。一个是一个全名，令人惊讶地返回错误的结果。您的里程可能会有所不同。

I tested this with 6 names and it got 4 of them right. 3 were rare last names. One was a full name which surprisingly returned wrong results. Your mileage may vary.

这对于任何大型组织都无效。如果您有一个小的地址列表，那么也许根据一个简单的第一个/最后一个字符串来简单的解析。否则，这不可靠。

This will not work for any large organization. If you have a small address list, then perhaps it is easy to uniquely resolve based on a simple first/last name string. But otherwise, this is not reliable.

您有几个问题：

我想提供该人的企业ID，查找该人员，然后返回其他信息（位置，电话号码等）。

I want to provide the enterprise ID of the person, have it look for that and then return other information (location, phone number etc).

我不认为这是Outlook解析别名的电子邮件地址的方式。您将需要引用一些外部数据库来执行这样的查询。

I do not think this is how Outlook resolves email addresses from an alias. You will need to reference some external database to perform a query like that.

我不知道Outlook如何知道只搜索Alias，据我所知，这只是在局部变量中声明。

I don't know how outlook knows to search only Alias, as far as I can tell that's only declared in local variables.

AliasName 是示例代码中的局部变量，但被分配来自用户输入的值（例如，Excel电子表格中的单元格）。因此，宏正在读取某些值，并尝试根据地址簿进行解析。

AliasName was a local variable in the example code, but it is assigned a value from user-input (cells in an Excel spreadsheet, for example). So the macro is reading in some values and attempting to resolve them against the address book.

如上所述，这只是一个简单的字符串唯一地解析为正确个人的可能性。

As I mentioned above, this is only as good as the likelihood that a simple string will uniquely resolve to the correct individual.

此外，当我尝试提取其他信息时，例如：

Also, when I try to pull out other information, for example:

HomeState = myAddrEntries.HomeState

我收到错误：对象不支持此属性或方法。我不知道该属性将被称为什么 - 我找不到任何文档在线显示属性如何命名（即使我搜索MAPI文档）。

I get an error: Object doesn't support this property or method. I don't know what that property would be called - I couldn't find any doc online that showed how properties are named (even when I searched for MAPI docuemntation).

可以有更好的解决方案吗？

是的。是的，可以。

如果你在对象模型中挖掘，你会发现两个看起来很有前途的项目， GetContact 方法返回一个 ContactItem （不幸的是这不是我们想要的），而 GetExchangeUser 返回一个 ExchangeUser 。我认为这是最接近你想要的，因为它包含你正在寻找的大部分信息。

If you dig around in the object model, you will find two items that look promising, GetContact method which returns a ContactItem (unfortunately this is not what we want), and GetExchangeUser which returns an ExchangeUser. I think this is the closest to what you want, since it contains much of the information you are looking for.

http://msdn.microsoft.com/en-us/library/office/ff870767（v = office.14） .aspx

我修改代码如下：

Option Explicit

Public Sub GetUsers()

Dim myolApp As Outlook.Application
Dim myNameSpace As Namespace
Dim myAddrList As AddressList
Dim myAddrEntry As addressEntry   'I changed this variable to avoid ambiguity
Dim AliasName As String
Dim i As Integer, r As Integer
Dim c As Range
Dim EndRow As Integer, n As Integer
Dim exchUser As Outlook.ExchangeUser

Set myolApp = CreateObject("Outlook.Application")
Set myNameSpace = myolApp.GetNamespace("MAPI")
Set myAddrList = myNameSpace.addressLists("Global Address List")

Dim FullName As String, LastName As String, FirstName As String
Dim HomeState As String, PhoneNum As String
Dim StartRow As Integer

EndRow = Cells(Rows.Count, 3).End(xlUp).Row

StartRow = InputBox("At which row should this start?", "Start Row", 4)

For Each c In Range("A" & StartRow & ":A" & CStr(EndRow))
    AliasName = LCase(Trim(c))
    c = AliasName
    Set myAddrEntry = myAddrList.addressEntries(AliasName)
    Set exchUser = myAddrEntry.GetExchangeUser

    If Not exchUser Is Nothing Then
        FirstName = exchUser.FirstName
        LastName = exchUser.LastName
        HomeState = exchUser.StateOrProvince
        PhoneNum = exchUser.BusinessTelephoneNumber
        'etc...
    End If

Next c
End Sub

这篇关于如何根据Excel（VBA）中的值从Outlook通讯录中拉出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！