CODEWITHSUNDEEP
excelvbagal
wh3resmycar2
wh3resmycar2

Reputation: 305

Excel VBA, return GAL value

I have a very simple small app in mind which will help me save time from alt tabbing from excel to outlook. I want to create a small userform that will have a textbox for an exchange user alias and returns the exchange user's full name.

The issue I have here is that the guide in msdn is a little vague for a userform and I'm getting some error messages (some got fixed by activating some references) and the code is quite complicated.

So basically I have 2 textboxes and a button. textbox1 will accept the alias, textbox2 will return the username after clicking the button.

There are several examples but most of them will result in dumping the GAL to an excel file which I don't need.

Upvotes: -1

Views: 354

Answers (1)

L42
L42

Reputation: 19737

This will give you what you want.

Private Function GetFullName(inAlias As String) As String
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olAdd As Outlook.AddressEntries
    Dim olMem As Outlook.AddressEntry
    Dim olLst As Outlook.AddressList
    Dim olAlias As String

    On Error Resume Next
    Set olApp = New Outlook.Application
    On Error GoTo 0

    If olApp Is Nothing Then
        GetFullName = "Source not available"
        Exit Function
    End If

    Set olNS = olApp.GetNamespace("MAPI")
    Set olLst = olNS.GetGlobalAddressList
    Set olAdd = olLst.AddressEntries

    For Each olMem In olAdd
        On Error Resume Next
        olAlias = olMem.GetExchangeUser.Alias
        On Error GoTo 0
        If olAlias = inAlias Then
            GetFullName = olMem.GetExchangeUser.Name
            Exit For
        Else
            GetFullName = "Invalid Alias"
        End If
    Next
    Set olApp = Nothing: Set olNS = Nothing: Set olAdd = Nothing
End Function

The draw back is this may take a while if your GAL is quite large.
I'll check if I can dump the list to an array first and then manipulate from there.
Or if there is another way to get to the name via alias using other method.
But for now, try learning from this first.

This is a function, so to get it to your textbox, you can simply:

TextBox2 = GetFullname(TextBox1)

Note:
I intentionally declared all the objects, you need to know what type of object you're working on. Also I used On Error Resume Next because there are AddressEntry without Alias and that gives error. That's the easiest work around I can think of for now.

Requirement:
You need to reference Microsoft Outlook xx.x Object Library.
xx.x vary depending on the Outlook version installed in your machine.

Upvotes: 0

Related Questions