Excel 2007:来自Outlook 2007的电子邮件地址格式 [英] Excel 2007: Format of email address from Outlook 2007

问题描述

我在Excel中有一个名字和姓氏列表，我想利用该列表在Visual Basic中使用Outlook查找电子邮件地址.

I have a list of first and last names in Excel and I want to utilize that list to look up email address in Outlook using visual basic.

我正在使用以下VB代码:

I'm using the following VB code:

    Private Sub GetAddresses()
    Dim o, AddressList, AddressEntry
    Dim c As Range, r As Range, AddressName As String
    Set o = CreateObject("Outlook.Application")
    Set AddressList = o.Session.AddressLists("Global Address List")
    Set r = Range("a1:a3")
    For Each c In r
        AddressName = Trim(c.Value) & ", " & Trim(c.Offset(0, 1).Value)
        For Each AddressEntry In AddressList.AddressEntries
            If AddressEntry.Name = AddressName Then
                c.Offset(0, 2).Value = AddressEntry.Address
                Exit For
            End If
        Next AddressEntry
    Next c
    End Sub

在实际检索电子邮件地址之前，该代码似乎可以正常工作.匹配名称后，将返回以下内容而不是地址.有谁知道我在做什么错.

The code seems to be working fine up until the point of actually retrieving the email address. After it matches a name its returning the following instead of the address. Does anyone have an idea of what I'm doing wrong.

/O=Compnay/OU=Company/cn=Recipients/cn=shs

在此先感谢您的帮助.

Thanks in advance for you help.

推荐答案

我假设这些用户是域用户.您要从exchangeUser对象获取SMTP地址.我已经更新了您的代码以显示此信息.

I am assuming that these are domain users. You want to get the SMTP address from the exchangeUser object. I have updated your code to show this.

Private Sub GetAddresses()
    Dim o, AddressList, AddressEntry
    Dim c As Range, r As Range, AddressName As String
    'added variable for exchange user object
    Dim exchangeUser As Outlook.exchangeUser

    Set o = CreateObject("Outlook.Application")
    Set AddressList = o.Session.AddressLists("Global Address List")
    Set r = Range("a1:a3")
    For Each c In r
        AddressName = Trim(c.Value) ' & ", " & Trim(c.Offset(0, 1).Value)
        For Each AddressEntry In AddressList.AddressEntries
            If AddressEntry.Name = AddressName Then
            'set the exchange user object
            Set exchangeUser = AddressEntry.GetExchangeUser
            'get the smtp addresss
            c.Offset(0, 2).Value = exchangeUser.PrimarySmtpAddress
            'release
            Set exchangeUser = Nothing
                Exit For
            End If
        Next AddressEntry
    Next c
End Sub

这篇关于Excel 2007:来自Outlook 2007的电子邮件地址格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！