Storing addresses into an array and then looking for data within that temp array

Question

I'm trying to automate a way of setting up projects using excel vba but pretty new to coding.

What I have so far is a function that gets peoples names and emails from my outlooks global address list and stores it onto a page of excel ("Email Address") The next thing it does based on the type of positions required in the project, it goes through the "Email Address" page on excel and finds people of that position within the company and provides a drop down list for the user to select. Finally, after the names have been chosen, it provides the email addresses for each team member.

Below is the code i have so far.

Sub emailfromoutlook()

Dim appOL As Object
Dim oGAL As Object
Dim oContact As Object
Dim oUser As Object
Dim arrUsers(1 To 65000, 1 To 3) As String
Dim UserIndex As Long
Dim i As Long

Set appOL = CreateObject("Outlook.Application")
Set oGAL = appOL.GetNamespace("MAPI").AddressLists("Global Address List").AddressEntries


Worksheets("Email Address").Activate

For i = 1 To oGAL.Count
    Set oContact = oGAL.Item(i)
    If oContact.AddressEntryUserType = 0 Then
        Set oUser = oContact.GetExchangeUser
        If Len(oUser.LastName) > 0 Then
            UserIndex = UserIndex + 1
            arrUsers(UserIndex, 1) = oUser.JobTitle
            arrUsers(UserIndex, 2) = oUser.Name
            arrUsers(UserIndex, 3) = oUser.PrimarySmtpAddress

        End If
    End If
Next i

appOL.Quit

If UserIndex > 0 Then
    Range("A2").Resize(UserIndex, UBound(arrUsers, 2)).Value = arrUsers
End If

Set appOL = Nothing
Set oGAL = Nothing
Set oContact = Nothing
Set oUser = Nothing
Erase arrUsers


End Sub

Sub dependent_list()

Dim a As Integer
Dim b As String


Range("C2:C50").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

a = 2
Do Until Worksheets("Sheet1").Cells(a, 2) = 0


Dim find As String
Dim array1(200)
Dim i As Integer
Dim j As Integer
Dim k As String

Worksheets("Email Address").Select
Erase array1
find = Worksheets("Sheet1").Cells(a, 2).Value
For i = 2 To 330
k = Worksheets("Email Address").Cells(i, 1)
If k = find Then

    array1(j) = Worksheets("Email Address").Cells(i, 2)
j = j + 1
Else
    'do it another thing
End If
Next i

Worksheets("Sheet1").Select
    Cells(a, 3).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Join(array1, ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    a = a + 1
Loop
End Sub

Sub email()

Dim c As Integer
Dim d As String
Dim e As String
Dim f As Integer

For c = 2 To 50
d = Worksheets("Sheet1").Cells(c, 3).Value

For f = 2 To 330
e = Worksheets("Email Address").Cells(f, 2)

If d = e Then
Worksheets("Sheet1").Cells(c, 4) = Worksheets("Email Address").Cells(f, 3)
Else
End If
Next f
Next c


End Sub

This works fine so far as it allows me to pick the members on the team based on the positions available in the project and provides me their email addresses but it has a flaw. The global address list in outlook will be updated as time passes on (as people and hired and fired) but it will not update the email addresses page on excel directly.

To fix this, a temporary solution i wrote was to refresh the excel page every time it opens in case of changes but that's not a good long term solution.

  Private Sub Workbook_Open()
    Sheets("Email Address").UsedRange.ClearContents
    Call emailfromoutlook
    Worksheets("Email Address").Range("A1") = "Job Title"
    Worksheets("Email Address").Range("B1") = "Full Name"
    Worksheets("Email Address").Range("C1") = "Email Address"
    Worksheets("Email Address").Range("A1:C1").Font.Bold = True
    MsgBox "Email Address book has been updated"
    End Sub

So for a long term fix, I'm thinking if its possible to store the global address list in a temporary array and then do all these functions out of that instead of putting them on an excel page. However, I dont have the slightest clue on how to accomplish that.

Any help or different ideas to do the same are greatly appreciated. Please feel free to ask for any clarifications as I realize its a very long and complicated problem.

Thank you.