Tag: GAL

83: Exporting contact info at scale

Posted on by ewand

Every email or productivity application which deals with contact info has some attempt at being able to sync, export or import contacts from elsewhere. “Attempt” being the operative word as you’re often left with a flat text file (or CSV) which might need some form of manipulation before it can be imported. Using Export & Import could be the simplest way of copying contacts from one account to another, or even cleaning up duplicates by exporting / fixing / deleting from the source / re-importing.

GMail supports importing CSV data or individual contacts in the form of VCF files, if you have them. Export to CSV is the norm too.

New Outlook and Outlook Web App have a simple mechanism for CSV import/export …

Article content

… while Outlook (classic) has a UI which hasn’t changed much in the last 20 years:

Export from company address book

What if you want to batch-export a load of contacts for colleagues from your company’s address book? Let’s say you’re a group going away on a conference and you want everyone’s number so you can keep in touch? If you’re using Exchange Server on-premises or Microsoft 365 for email, then there’s a “default Global Address List” which has everyone in it. It might also have phone numbers, job title, department and other info, besides just a name and an email address.

Using New Outlook or Outlook Web app, you can only really operate on a single entry at a time, so it could be a drawn-out exercise to pick everyone you want and copy them to your own contacts.

It’s pretty easy using classic Outlook to add multiple contacts from the GAL. Open the Address Book (another piece of UI which is largely unchanged since the original Exchange client released over 30 years ago); SHIFT+CTRL+B is the fastest way to fire it up.

Hold the CTRL key down while you click on multiple names, then right-click on one of the selected ones and choose Add to Contacts. So far, so good. But what if you wanted to add dozens of contacts from Aaron to Zebedee, and you had thousands of entries in the GAL? It could be a bit of a faff to scroll, multi-select then Add to Contacts. If only there was another way.

Exporting the whole Offline Address Book

Speaking of faff, here is one technique which will export everything to a CSV file and then let you filter, sort and ultimately export just the stuff you want. You might be able to do other things like take a snapshot at quarterly intervals, then use Excel to compare the CSVs and see who has joined, left or moved department, changed job titles and so on. Quite Interesting, no?

The source of this goldmine is the Offline Address Book which Outlook (classic) keeps on every PC that’s connected to an Exchange/M365 mailbox, so the user can still see the address list when they’re offline. Now this technique isn’t necessarily for the faint hearted, but at least you only need to do the bulk of it once and then run a simple script whenever you want to extract the data from the latest OAB. It’s not exactly rocket science.

The OAB is held in a bunch of files on your PC’s disk; the format is uncompressed so if you’re foolish enough to open in, say, Notepad, you’ll recognize some text but there’s a lot of other stuff in there. Fortunately, some enterprising techies have pulled together a script that quickly rips through an OAB and delivers a neat CSV of users, and another of groups or mailing lists.

Step One – Install Python

OK, this would send most people running for the hills, but on a Windows PC it’s reasonably straightforward (and for the purposes of the rest of this example, we’re assuming you’re running Windows – if you’re a Mac or Linux user then you’ll need to figure it out on your own). As said, this is a one-off activity, to install both the Python scripting language, and the oab script that we’re going to run later.

Unless you’re already a Python developer, start by going to Python 3.13 – Free download and install on Windows from the Microsoft Store, hit the Install button and sit back for a bit.

Once the install has finished, we need to use a package manager called pip (no, not him) to find and install the oab script.

Start a command prompt by pressing Start and entering cmd, then in the command window, simply enter:

pip install oab

You’ll see a bunch of semi-scary looking warnings; none are really important other than one which is likely saying:

WARNING: the script <name> is installed in ‘<long directory name>’ which is not on PATH.

It will be easier to run these scripts if you add that folder to PATH. Carefully select all the text of the long directory name between the ‘ ’ marks, and right-click on it. This will copy that text to the clipboard.

Now enter, in the command prompt:

Set PATH=%PATH%;<right-click to paste the text copied>

eg.

This will mean in future, you can run the “oab” script from anywhere. Test that it works by just entering oab in the command window, and you should get a list of all the available options to run that command.

Step Two – Find your Offline Address Book files

Once you have Python and the script installed, you’ll only need to run steps two and three if you want to subsequently go back and re-extract data from the latest Offline Address Book.

The Offline Address Book (OAB) is built on the Exchange Server or M365 service, usually every day. Outlook (classic) can download on demand, or it tends to pick the latest files up when it feels like it. You probably want to force it, by going into Send/Receive and choosing the option Download Address Book. Keep an eye on the status text in the bottom right of Outlook to see if it’s still downloading stuff, and when it looks like it has finished then proceed.

Now, the trick is to find not only the most recent OAB files, but the ones which correspond to the account you’re interested in; if you have Outlook set up (as in the case above) to connect to several M365 accounts, you may have to try a few times to find the right one – but if you’re in a megacorporation with 500,000 entries and the others are your M365 family subscription etc, then just look for the biggest file. Of all the different files that comprise the OAB, the one we want is udetails.oab.

The OAB files are stored in a deeply buried location which can quickly be found by pressing the Start button or Windows Key, and entering:

%localappdata%\Microsoft\Outlook\Offline Address Books

You’ll end up with one or more folders with a GUID for a name; open each one in turn and look for a file called udetails.oab in the most recent folder(s).

Copy that file – assuming it has a recent date/time and looks sufficiently large (a 1,000 user company is going to be in the 1MB-2MB size, probably; Microsoft or Amazon will be more like 1GB) – to somewhere that’s easily accessible; why not try c:\users\<yourprofile>. You can get to that location quickly by pressing Start again and entering

%userprofile%

To prevent getting in the way of all the other stuff that’s in your user profile, you might want to create a folder (let’s call it oab) and drop the udetails.oab file in there for later perusal.

Step Three – Extraction

Now we have the latest OAB data file, it’s a simple matter of pointing the script at it.

Start by dropping to a command prompt (press Start and enter cmd) then changing directory to wherever you put the file; if you dropped it into %userprofile% then the command prompt will probably start there. If you put it in a subfolder, or somewhere else, then you’ll need to use cd to change directory (and possibly dir to check it’s there):

Now, from the command prompt, enter the following command to invoke the script to do the work (it is case sensitive so take care):

oab -C -o oab udetails.oab

If your OAB file is 100MBs in size, this might take a few minutes, but if more modest it’ll be a snap:

Now open the oab.users.csv file in Excel, select the whole thing, select Format as Table from the toolbar, tell it that your data has headers, and you should be easily able to filter out the rows you want to keep, delete the rest, then import them back into Outlook as personal contacts. Or do whatever else you have in mind.

As described earlier, to repeat the process in future, just update the OAB, grab the latest udetails.oab file again and re-run the script as per steps 2 and 3. Whatever you do with the resulting files, just make sure you do it responsibly.

#639 – Macros, Ghosts and GALs

Posted on by ewand

VB and MacrosSince the early days, Microsoft always kept an eye on what its competitors were doing. It was once de rigeur to produce “battlecards” which would show feature-by-feature how one product is better than its competitor, thus assuring the customer they should buy this one. Thankfully, times have mostly moved on to just building as good a product as possible and then let customers and the markets decide – sometimes, they get improved and honed over time to be the best out there, and sometimes they get dispatched to the boneyard as times move on.

Exchange Server boxIn the late 1990s, Office and Exchange (and later, SharePoint) Server were seen as Microsoft’s entrants into the burgeoning “Groupware” market, which became subsumed into “Knowledge Management” c2000. Key competitors to Exchange & Outlook were Lotus Notes and Novell GroupWise, both of which came from being collab tools and gained email functions. Notes was arguably much more mature and feature rich even if the UI was sometimes clunky, GroupWise was much leaner but found a niche in several industries. Amazingly, GroupWise is still a thing and Notes evolved first into IBM Notes/Domino and was eventually sold off to now be HCL Notes and HCL Domino.

One of the early moves Microsoft made to elevate Office apps to more than just writing documents, was to try to make the docs more capable through adding Macros, and later, Visual Basic for Applications. This allowed a moderately skilful user to dabble in programming to make smarter applications centered around documents; what seemed like a good idea at the time unwittingly unleashed a wave of malware, where bad actors wrote macros to do undesirable things. Following the “Melissa” worm in 1999, Office stopped Macros running without asking the user for permission. Using Macros for anything more than tinkering never really took off.

Blocked Macro warning

Macros disabled entirely

Microsoft announced in February 2022 that all Office Macros in content received online would be disabled completely; this was temporarily rolled back in some test builds for some changes to be made in how it works, but for many, the warning will still be there if you open a Macro-enabled file that you’ve downloaded or been sent.

Unblocking MacroThere are still some very useful Office macros out there, and if you do need to run one that you know is from a trustworthy source, there is a workaround – save the file to your PC locally, then right-click on it to look at the file’s properties, tick the “Unblock” option and apply that. You’ll now be able to choose to run the macro unencumbered.

One such handy macro was discussed back in December 2021 in ToW 611, and is used to find Ghost meetings – ie ones you have arranged but everyone has declined (or at least not accepted). The macro spins through all future meetings in your calendar and lists the ones you’ve organised but where you’re likely to be the only attendee who shows up. Particularly useful at this time of year if lots of people are about to take time off over the summer, and may have declined a few recurring meetings but you – as organiser – still have them in your calendar.

Ghost Meetings

For the latest version of the macro, download the ZIP file to your machine and expand it (or just copy the XLSM file that’s within and put it somewhere else), do the property Unblock thing as above, open in Excel, click the button to allow content, then the Scan Calendar button and you’re all set. You still need to go into Outlook and look at the appropriate date then decide if you want to cancel those meetings or not.

Another more powerful macro – though a little more esoteric – is one which does bulk resolution against the Global Address List, so if you give it a list of display names and/or alias names, it will show the full name, title, department, office, email, and alias name of that person. Handy if you want to get the full details of everyone who is going to attend a meeting, but if you just have a longish list of names then you could just paste them in and see how it goes. This was covered back in ToW 575. One usage scenario recently was to estimate the number of people who were attending a group meeting, but were based at other offices and would therefore need accommodation.

Here’s an example output of over 500 names who were invited to a large meeting; by just providing their display names in column A, it took the sheet about 30 seconds to complete, with 10 identified as distribution lists and 50 unknowns who couldn’t be resolved, either due to no longer being in the GAL or because there were more than one possible name listed.

GAL resolving

If you can manually find the unknown person/people in the GAL, then get their alias name and paste that into column 1 instead of the ambiguous display name, then try to run it again.