CODEWITHSUNDEEP
excelvba
Michal Palko
Michal Palko

Reputation: 661

How to get logged user's email address in Excel

I try to get mail address through Excel VBA of currently logged user (NOT using Outlook).

I can get

Application.UserName

but this is not enough.

There is mail address under Excel -> File -> Account under "Belongs to: [email protected]"

Is there a way how to extract it with VBA?

Upvotes: 1

Views: 4249

Answers (3)

marciel.deg
marciel.deg

Reputation: 512

On my machine I couldn't find the "ADUserName" key in the registry. But the code below works:

Sub Button1_Click()
    Set WShell = CreateObject("WScript.Shell")
    ConnectedAccountWamAad = WShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity\ConnectedAccountWamAad")
    Email = WShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity\Identities\" & ConnectedAccountWamAad & "_ADAL\EmailAddress")
    
    MsgBox "Email: " & Email
End Sub

Upvotes: 0

Michal Palko
Michal Palko

Reputation: 661

I found elegant solution. Might not guarantee excel user but suits for me perfectly.

Sub getUserMail()
    Debug.Print CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity\ADUserName")
End Sub

Credit to @Storax for registry key and this answer how to read registry easily. Read and write from/to registry in VBA

Upvotes: 4

FaneDuru
FaneDuru

Reputation: 42236

I am afraid that without Outlook, it is not possible... If the user in discussion has Outlook installed and configured you can obtain it using the next code. The code needs a reference to 'Microsoft Outlook ... Object Library':

Sub ActiveUserMailAddress()
  Dim objOutlook As New Outlook.Application
  Debug.Print objOutlook.GetNamespace("MAPI").CurrentUser.Address
End Sub

In order to add the reference: Being in VBE, go Tools -> References... -> scroll down until see the above recommended reference, check it and press OK.

Upvotes: 1

Related Questions