Fetch/Get Outlook GAL email Id details of a particular 'email account domain' (by Exchange

Hi All,

Could you please look into below situation and help me out soon:

Manual Situation: Every week, I get a huge list of User IDs (Alias) on a sheet (Sheet1, Column A), which could be more than 5000 and so on. I have to identify/ validate those users by using their contact details over 'Global Address List' (GAL) in my MS Outlook.
For above particular task, every time I have to copy around 100 User IDs (Alias) and open a new blank mail, paste them under "To..." section, then click on "Check Names" option in the Message Menu.
If we successfully identify 'name' and 'PrimarySmtpAddress' of the searched Alias/ User Id, then we have to pull the record of that user's Manager Name with Alias Id.
If any pasted Alias number does not get converted into User Names or e-mail address or unable to identify over GAL then that Alias will be marked as 'Removed Users'. This is the manual method to identify that who is still with the company and who has left.


Automation: I thought that first, if I pull all the available users' contact details as per GAL on a sheet, then apply a vlookup function from the weekly task sheet to this data, so this idea may work. That's why I have written below VBA codes to pull all the existed users' contact details as per GAL (VBA script file is also attached). Though it works, but not so smartly.

I need help your expertise for below improvement area of my VBA script:
1) How can it run so quickly? My GAL has more than 1 million User IDs/Alias numbers, so pulling every record activity takes 'n' number of hours.

2) How can we assign/ identify the correct "Global Address List" of the desired e-mail account for running the program?
For Example: I have a total of two GAL in my Outlook application due to two different e-mail IDs and their Exchange Servers. One is "[email protected]" and another is "[email protected]".

Set myAddressList = OutSess.Session.AddressLists.Item(9) ' ("Global Address List") for the first 'GAL' of '@gmail.com'
Set myAddressList = OutSess.Session.AddressLists.Item(16) ' ("Global Address List") for the second 'GAL' of '@yahoo.com'

I have to access GAL only for my "[email protected]" account for identify User IDs/ Alias numbers, and I only know programmically that ".AddressLists.Item(16)" is located to this '@yahoo.com' account in my Outlook application, so while running my codes I have to choose the exact Item() number by clicking on many msgbox(es), but how would other employee know their exact AddressList.Item() for their '@yahoo.com' e-mail account.
The order/ sequence of accounts and their ExchangeServers and AddressList.Item() can be differ in the Outlook application of every employee who runs this VBA program. What if someone has more than two e-mail accounts, please help me to make this programmically smart by using the account details.

Please Login or Register  to view this content.

3) Other Logic - Is there any other faster method to get this task done automatically? I don't know if my below idea is valid or not:
I think that if I sort all the User IDs/ Alias in ascending order on the excel sheet, then try to find out their contact details over GAL. If we find a record then store that position sequence/order in a variable, so the next Alias search time we do not have to search from the start in the GAL. I think that it can we save more time, do not know if it can be possible here. (I do not know this kind of programming skills for Outlook; need your help)

Thanks in advance!

...Can anyone please make this possible soon, it's urgent for my assignment.

No one.... :O