Fetch domain name with vba in Excel

[CatherineH]

Goal: Prevent exited employees from using certain company spreadsheets.

I've searched for this question, but most of the questions refer to allowing only a specific list of users access. I have another workbook that only a few people need access to, so I put their ID numbers on a hidden sheet and used a vlookup to check if the ID they typed matched the list and that today() returns a date before the expiry date, it works perfectly. Thank you to the forum posters that generously shared the different pieces of vba script that made it work.

In this case, I can't maintain a list of authorized users as many employees will need to be able to use copies of this workbook. Our Windows logon credentials are our email addresses and network passwords: "Name.Surname@companyname.co.za". As the company name is the domain name I wanted to fetch either in order to confirm that the Excel user is logged onto the domain (therefore a current employee).

I've tried various macros from this and other forums, but they all just give me different versions of the user's name. I've put only the lines of the macros that seem relevant.

 Range("A1:D5").Value = Application.UserName

gives me: Jennifer Smith

 UserName = Environ("username")

gives me: JenniferS (Environ$ was the same)

 ComputerName = Environ("computername")

gives me: LT-JennySmith

  MsgBox CreateObject("Outlook.Application").GetNamespace("MAPI") _                    .CurrentUser.AddressEntry.GetExchangeUser

gives me: Jennifer Smith

 Set objInfo = CreateObject("ADSystemInfo")
strLDAP = objInfo.UserName
Set objInfo = Nothing
strFullName = GetUserName(strLDAP)

gives me: Jennifer Smith ('originally coded as VB script by A.Vials, converted to VBA by Sly)

Can someone please assist me with vba that will allow Excel to fetch either the domain name or email address of the currently logged on user. If I can just get this info into a cell I can manage the rest.

 

[steve the fish]

See if this produces what you would expect:

myDomain = CreateObject("WScript.Network").UserDomain

 

[CatherineH]

Thank you Steve,

I got a result that seems to be the abbreviation of our company name two mergers ago. I'm off to IT to find out how many different results I'm likely to get, an OR formula will do the rest. Thanks again.