CODEWITHSUNDEEP
excelvbaoutlook
afroakuma
afroakuma

Reputation: 113

How to capture the user's Outlook email address?

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. [email protected]).

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

Upvotes: 0

Views: 617

Answers (2)

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

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.

Upvotes: 1

Charan
Charan

Reputation: 33

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"

Upvotes: 0

Related Questions