创建“检查名称"； Excel中的按钮 [英] Creating a "Check Names" button in Excel

问题描述

我不熟悉使用VBA和宏，并且想知道是否有一种方法可以在Excel中添加检查名称"功能(类似于Outlook中的功能).我正在处理的表格的一部分要求我输入员工的姓名，并且我希望能够单击一个按钮以确保我正确拼写了他们的姓名并且他们在我们的电子邮件系统中.任何对正确方向的帮助或指导，将不胜感激！

I am new to using VBA and Macros and am wondering if there is a way to add a "check names" function in Excel (similar to the function in Outlook). Part of the form I am working on requires me to enter names of employees and I would like to be able to click a button to make sure I have spelled their names correctly and they are in our email system. Any help or pointers in the right direction would be greatly appreciated!

推荐答案

此处有两个答案:

在Excel 2010中创建(不知道它是否可以在2003年运行).

Created in Excel 2010 (no idea if it'll work in 2003).

如果该名称可以在Outlook中解析，则第一个将返回TRUE或FALSE.

The first will return TRUE or FALSE if the name can be resolved in Outlook.

'----------------------------------------------------------------------------------
' Procedure : ResolveDisplayNameToSMTP
' Author    : Sue Mosher - updated by D.Bartrup-Cook to work in Excel late binding.
'-----------------------------------------------------------------------------------
Public Function ResolveDisplayName(sFromName) As Boolean

    Dim OLApp As Object 'Outlook.Application
    Dim oRecip As Object 'Outlook.Recipient
    Dim oEU As Object 'Outlook.ExchangeUser
    Dim oEDL As Object 'Outlook.ExchangeDistributionList

    Set OLApp = CreateObject("Outlook.Application")
    Set oRecip = OLApp.Session.CreateRecipient(sFromName)
    oRecip.Resolve
    If oRecip.Resolved Then
        ResolveDisplayName = True
    Else
        ResolveDisplayName = False
    End If

End Function

第二个将解析名称并返回电子邮件地址:

The second will resolve the name and return the email address:

'----------------------------------------------------------------------------------
' Procedure : ResolveDisplayNameToSMTP
' Author    : Sue Mosher - updated by D.Bartrup-Cook to work in Excel late binding.
'-----------------------------------------------------------------------------------
Public Function ResolveDisplayNameToSMTP(sFromName) As String
    Dim OLApp As Object 'Outlook.Application
    Dim oRecip As Object 'Outlook.Recipient
    Dim oEU As Object 'Outlook.ExchangeUser
    Dim oEDL As Object 'Outlook.ExchangeDistributionList

    Set OLApp = CreateObject("Outlook.Application")
    Set oRecip = OLApp.Session.CreateRecipient(sFromName)
    oRecip.Resolve
    If oRecip.Resolved Then
        Select Case oRecip.AddressEntry.AddressEntryUserType
            Case 0, 5 'olExchangeUserAddressEntry & olExchangeRemoteUserAddressEntry
                Set oEU = oRecip.AddressEntry.GetExchangeUser
                If Not (oEU Is Nothing) Then
                    ResolveDisplayNameToSMTP = oEU.PrimarySmtpAddress
                End If
            Case 10, 30 'olOutlookContactAddressEntry & 'olSmtpAddressEntry
                    ResolveDisplayNameToSMTP = oRecip.AddressEntry.Address
        End Select
    End If
End Function

下面是一个测试过程，展示了如何使用这两个功能:

Here's a test procedure showing how to use both functions:

Sub Test()

    MsgBox ResolveDisplayName("Marty Moesta")
    MsgBox ResolveDisplayNameToSMTP("Marty Moesta")

End Sub

这篇关于创建“检查名称"； Excel中的按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！