用Excel VBA捕获Outlook地址

Question

我在Excel中有一个自定义VBA函数，该函数用于获取相关Office用户的电子邮件地址。它对一些用户任意触发——有时它有效，有时它不起作用。

我的最终用户并不都使用最新版本的Excel，但这是唯一一个引起麻烦的VBA函数。对于受影响的用户来说，第一次打开文件时，它往往更有效，然后在随后的打开中变为惰性。

Function UserName() As String
Dim OL As Object, olAllUsers As Object, oExchUser As Object, oentry As Object, myitem As Object
Dim User As String

Set OL = CreateObject("outlook.application")
Set olAllUsers = OL.Session.AddressLists.Item("All Users").AddressEntries

User = OL.Session.CurrentUser.Name

Set oentry = olAllUsers.Item(User)

Set oExchUser = oentry.GetExchangeUser()

UserName = oExchUser.PrimarySmtpAddress

End Function

我在网上尝试了一些解决方案，但都无济于事，还试图强制单元格重新计算，但似乎对于受影响的用户来说，一旦函数决定什么都不做，重新计算就无关紧要了。

Answer 1

代码可以简化一点：

Function UserName() As String
Dim OL As Object, oExchUser As Object, oentry As Object, myitem As Object
Dim User As String
Set OL = CreateObject("outlook.application")
'
' use NameSpace.Logon if required
'
Set oentry = OL.Session.CurrentUser.AddressEntry
'
' here you can also check whether it is an Exchange account
'
Set oExchUser = oentry.GetExchangeUser()
UserName = oExchUser.PrimarySmtpAddress
End Function