创建“检查名称"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屋!