0

I created a VBA function in Excel to identify the current user's email address.

Public Function UserName()

Dim OL, olAllUsers, oExchUser, oentry, 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

For most users, this spits out their email address (e.g. john_doe@generic.com).

For some it doesn't update away from the default, which is my own email as the code executed successfully when I tried it.

Community
  • 1
  • 1
afroakuma
  • 113
  • 5
  • If the Address Entry is either already an SMTP address or a distribution list then the GetExchangeUser won't fetch properly. Some of the answers here may help although it would need to be ported to Excel code from Outlook: https://stackoverflow.com/questions/12641704/how-do-you-extract-email-addresses-from-the-to-field-in-outlook/66484483#66484483 – Tragamor Apr 06 '21 at 16:50
  • One of the links seem to have an Excel based answer: https://stackoverflow.com/questions/31161726/creating-a-check-names-button-in-excel – Tragamor Apr 06 '21 at 16:58
  • Yes, it looks like that was exactly the issue. I tried a model using the Account.SmtpAddress protocol in one of the comments and it appears to be working perfectly for all impacted users. Much obliged! – afroakuma Apr 06 '21 at 18:22

2 Answers2

1

You can use the Account.SmtpAddress property for getting the SMTP email address. It returns a string representing the Simple Mail Transfer Protocol (SMTP) address for the Account.

Dim UserMailAddress As String
Set UserMailAddress = OL.Session.CurrentUser.SmtpAddress

In case if you get an Exchange-like email address you may convert it to the SMTP one. See HowTo: Convert Exchange-based email address into SMTP email address for more information.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
0

If the email address is constant for all the users, lets say gmail.com is constant. And user_name of system is the user_id then you can try something like this.

Dim User As String
user = environ("Username")
email = user & "gmail.com"
Charan
  • 33
  • 3