在Excel中显示GAL以获取别名或电子邮件地址 [英] Display GAL in Excel to get Alias or Email Adress

问题描述

我正在尝试利用在 THIS中找到的信息POST . 我有2个问题:

I am trying to make use of information found in THIS POST. I have 2 issues:

1. 下面的行不确定地挂起.固定---它只是隐藏的，没有任务栏项目，简单的搜索告诉我如何将其显示在最前面 strAddress = objWordApp.GetAddress(，strCode，False，1，，，True，True).GetProperty(" http://schemas.microsoft.com/mapi/proptag/0x3A00001E )

1. the following line hangs indefinitly. FIXED--- its just hidden and has no task bar item, simple search told me how to bring to to the front strAddress = objWordApp.GetAddress(, strCode, False, 1, , , True, True).GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3A00001E")

我需要返回可以在TO行中使用的内容，例如ALIAS或完整的电子邮件地址.我已经在WORD中测试了此代码，并且可以正常运行(删除对Word的引用)，但无法获得所需的正确信息.当我拉出时，我得到一个交换专有名称.我需要以某种方式将其转换为别名或电子邮件地址

I need to return something I can use in a TO line, so an ALIAS or a full email address. I have tested this code in WORD and it works perfrectly (remvoing the references to word) except I cannot get the correct information I need. When I pull I get an exchange distinguished name.. I need to convert this somehow to an alias or email address

/o=corperation/ou=administration/cn=my.name

/o=corperation/ou=administration/cn=my.name

背景:前面提到的帖子中的代码显示了OUTLOOK GAL，因此用户可以从中搜索/选择联系人.我想使用GAL，因为它可以处理200,000多个记录，并包括发行列表.

BACKGROUND: the code in the previously mentioned post displayed the OUTLOOK GAL so a user can search/select a contact from it. I want to use the GAL because it can handle the 200,000+ records and includes distrobution lists.

软件:该软件必须在OFffice 2010套件中起作用.我不需要任何Backword兼容性，并且目前对将来的证明是一个最小的问题.

SOFTWARE: This has to function within the OFffice 2010 suite. I don't need any backwords compatibility and future proofing is a minimal concern at the moment.

结果:我基本上只是希望用户能够搜索收件人，并将该地址存储在单元格中.

END RESULT: I basically just want a user to be able to search for a recipient and have that address end up in a cell.

任何提示将不胜感激.

推荐答案

方法一:使用GetAddress函数

下面的代码是否仍然无限期地为您挂起?

Method One: Using the GetAddress function

Does the following code still hang indefinitely for you?

Set objWordApp = CreateObject("Word.Application")
InputBox "", "", objWordApp.GetAddress(, "<PR_EMAIL_ADDRESS>", False, 1, , , True, True)

方法二:如果您知道用户名，直接获取它

您也许可以使用 LDAP 直接获取此信息:

Method Two: If you know the username grab it directly

You can maybe use the LDAP directly to get this information:

Public Function GetUserEmail(ByVal userName As String) As String

    Const ADS_SCOPE_SUBTREE = 2

    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand = CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection

    objCommand.Properties("Page Size") = 1
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

    objCommand.CommandText = "SELECT mail FROM 'LDAP://DC=something,DC=co,DC=uk' WHERE objectCategory='user' AND sAMAccountName='" & userName & "'"
    Set objRecordSet = objCommand.Execute
    objRecordSet.MoveFirst

    If Not objRecordSet.EOF Then
        GetUserEmail = objRecordSet.Fields("mail").Value
    Else
        GetUserEmail = vbNull
    End If

End Function

方法三:创建自己的可搜索表单

您可以创建自己的UserForm，以从LDAP带回用户列表.您可以选择要搜索的字段，然后允许用户单击该项目以获取电子邮件地址.有点杂乱，但加载速度会更快，因为它只会搜索长度超过3个字符的名称.

Method Three: Create your own searchable form

You could create your own UserForm to bring back a list of users from the LDAP. You could choose the fields you want to search on and then allow the user to click that item to grab the email address. It's a little messy, but it should load a bit faster, since it'll only search on a name more than 3 characters long.

在上面的示例中，我创建了一个查询，该查询在LDAP的givenName或sn字段中进行搜索:

In this example above I created a query which searches on the givenName or sn field of the LDAP:

Private Sub txtSearch_Change()
    If Len(txtSearch) > 3 Then

        queryString = txtSearch

        Const ADS_SCOPE_SUBTREE = 2

        Set objConnection = CreateObject("ADODB.Connection")
        Set objCommand = CreateObject("ADODB.Command")
        objConnection.Provider = "ADsDSOObject"
        objConnection.Open "Active Directory Provider"
        Set objCommand.ActiveConnection = objConnection

        objCommand.Properties("Page Size") = 1
        objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

        objCommand.CommandText = "SELECT givenName, sn, mail FROM 'LDAP://DC=something,DC=co,DC=uk' WHERE objectCategory='user' AND (givenName = '*" & queryString & "*' Or sn = '*" & queryString & "*')"
        Set objRecordset = objCommand.Execute

        lvResults.ListItems.Clear
        Do Until objRecordset.EOF
            Set li = lvResults.ListItems.Add(, , objRecordset.Fields("givenName").Value)

            li.SubItems(1) = objRecordset.Fields("sn").Value
            If Not IsNull(objRecordset.Fields("mail")) Then
                li.SubItems(2) = objRecordset.Fields("mail").Value
            End If

            objRecordset.MoveNext
        Loop

    End If
End Sub

注释

需要注意的一点是，您将需要将LDAP字符串更改为公司域控制器.例如LDAP://DC=something,DC=co,DC=uk.

如果您不知道这一点，可以通过以下方法找出来:

If you don't know this you can find it out by doing:

Set sysinfo = CreateObject("ADSystemInfo")
MsgBox sysinfo.userName

• 请注意，您只想购买DC =零件.
• 可以在这里找到所有属性的列表: http://www.computerperformance .co.uk/Logon/LDAP_attributes_active_directory.htm
• Note you only want to take the DC= parts.
• A list of all attributes can be found here: http://www.computerperformance.co.uk/Logon/LDAP_attributes_active_directory.htm

这篇关于在Excel中显示GAL以获取别名或电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！