如何根据 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).

我不知道该怎么做.首先，我不知道 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

我收到错误消息:对象不支持此属性或方法.我不知道该属性将被称为什么 - 我在网上找不到任何显示属性如何命名的文档(即使我搜索了 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).

所以，我的问题是 - 如何使用此代码按 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 对象的属性.

Homestate is not a property of an AddressEntry object.

当我提供一个名字(名字或姓氏)时，它会在地址簿中查找它并返回它找到的人的名字和姓氏.

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 如何知道只搜索别名，据我所知，它只在局部变量中声明.

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

我将您的代码修改如下:

I modify your code as follows:

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屋！