0

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: Mail@address.com"

Is there a way how to extract it with VBA?

Michal Palko
  • 601
  • 1
  • 4
  • 14

2 Answers2

2

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

Michal Palko
  • 601
  • 1
  • 4
  • 14
1

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.

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • 1
    *(NOT using Outlook)* ^^ • But anyway I think this is the only possibility. – Pᴇʜ Aug 03 '20 at 09:21
  • 1
    @Pᴇʜ: I wasn't clearly emphasized that this is the only possibility... I will try doing this. Thanks! – FaneDuru Aug 03 '20 at 09:23
  • The code above will probably not give the information the OP is looking for. The code will give as stated the first (default) account and this might be a different one than the one used in Excel. On the other hand I also do not know how to retrieve the email account stated in Excel-> File .... I found the e-mail account in the registry but I am not sure if that is reliable. – Storax Aug 03 '20 at 09:29
  • There is some information available here: https://techcommunity.microsoft.com/t5/access/username-from-office-account-with-vba-access2016/m-p/14297 but I do not know which reference to add so `System.DirectoryServices.AccountManagement.UserPrincipal` can be used? – Michael Wycisk Aug 03 '20 at 09:36
  • Or could it be somewhere in registry? – Michal Palko Aug 03 '20 at 09:41
  • @Michael Wycisk: I am afraid that the returned account is not a mail account. It looks to be an Access account... I physically do not imagine how to register such a mail account without Outlook. It should be associated to an application, but which to be it? – FaneDuru Aug 03 '20 at 09:41
  • @Michal Palko: How to be it registered in Registry, without being associated to any application? How will 'Registry' know which will be your mail account if you use Yahoo account in browser? Can you share what application you/your user use/s to send mails from its account? We will maybe find a way to extract it from there... – FaneDuru Aug 03 '20 at 09:44
  • @Michal Palko: The registry key could be `HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Identity\Identities`. But again it is possible that you find more than one entry there. – Storax Aug 03 '20 at 10:16
  • @Storax: I am afraid that without Outlook being installed **and configured for a specific account**, this key will not even exist... – FaneDuru Aug 03 '20 at 10:28
  • @FaneDuru: I would object. This is the key where Office (even with Outlook not installed and configured) will store the identites for the account resp. the email address you find in Excel/File/Account and which the OP IMO refers to. But, of course, on the other hand, I can and will not test that ;-) – Storax Aug 03 '20 at 10:51
  • @Storax: I did not say that it cannot exist. I only was afraid about it. And I think that an account appears in Excel/File/Account, it is there only if you are using the account in discussion to online login, or to be taken from Outlook customization. But I will not uninstall Outlook only to test that... And I also do not say that it certainly cannot exist in the way you suggest. :) – FaneDuru Aug 03 '20 at 11:14
  • @Storax That's great. Just wondering between ADUserName and ConnectedADALIdentity - in my case it's only capitals in my mail. – Michal Palko Aug 03 '20 at 16:50