, 2 min read

Exporting Exchange/Outlook GAL to vCard

Original post is here eklausmeier.goip.de/blog/2016/06-05-exporting-exchangeoutlook-gal-to-vcard.


This post is about the Microsoft Exchange GAL, i.e., the global address list. The task is to export the data in the GAL to vCard format.

Microsoft Outlook stores local caches of the GAL in %userprofile%\Local Settings\Application Data\Microsoft\Outlook, see Administering the offline address book in Outlook. On my computer they look like this

 Listing of D:\Users\...\AppData\Local\Microsoft\Outlook\Offline Address Books\...

21.05.2016  20:44    <DIR>          .
21.05.2016  20:44    <DIR>          ..
21.05.2016  20:44         3.818.260 uanrdex.oab
21.05.2016  20:44           686.956 ubrowse.oab
21.05.2016  20:44        56.310.184 udetails.oab
21.05.2016  20:44                20 updndex.oab
21.05.2016  20:44         1.373.676 urdndex.oab
21.05.2016  20:44            25.915 utmplts.oab
               6 Files,      62.215.011 Bytes
One could now read these files and extract its information using the file format definition given in [Offline Address Book (OAB) File Format and Schema](https://download.microsoft.com/download/5/D/D/5DD33FDF-91F5-496D-9884-0A0B0EE698BB/%5BMS-OXOAB%5D.pdf), local copy is [here](**/pdf/ms-oxoab.pdf). I found this link in [Stackoverflow](https://stackoverflow.com/questions/2318655/what-api-can-be-used-to-read-oab-files). This file format is quite unwieldy.

It is quite easy to use Outlook VBA to read the GAL, using a code snippet found in AddressEntry.GetExchangeUser Method and then extending it to write vCard format. The snippet is

    For Each oAL In colAL
        'Address list is an Exchange Global Address List
        If oAL.AddressListType = olExchangeGlobalAddressList Then
            Set colAE = oAL.AddressEntries
            For Each oAE In colAE
                If oAE.AddressEntryUserType = _
                    olExchangeUserAddressEntry _
                    Or oAE.AddressEntryUserType = _
                    olExchangeRemoteUserAddressEntry Then
                    Set oExUser = oAE.GetExchangeUser
                    Debug.Print (oExUser.JobTitle)
                    Debug.Print (oExUser.OfficeLocation)
                    Debug.Print (oExUser.BusinessTelephoneNumber)
                End If
            Next
        End If
    Next

My code in GitHub replaces Debug.Print with corresponding Print #1 to a file and vCard keywords. I added a number of safeguards (i,j,k) so that the program just in case does not run overly long. Use Alt-F11 to fire up VBA in Outlook.

Extracting 30.000 entries from GAL takes about 1-2 minutes. As this takes some time I preferred to massage the final output with Perl to clean the data afterwards. e.g., delete contact entries without phone numbers.

#!/usr/bin/perl -W
# Read vCard file and delete entries which contain no telephone numbers, etc.

use strict;
my ($n,@v) = (0,());

while (<>) {
    chomp;
    s/\s+$//;	# rtrim
    s/CATEGORIES:Business/CATEGORIES:Companyname/;
    s/ADR;TYPE=work:/ADR;TYPE=work:;;/;
    next if (length($_) < 1);
    push(@v,$_);
    ++$n;
    #print "$#v, i=$i\n";

    if (/END:VCARD/) {
        # entry 6 is TEL;TYPE=WORK, 7 is TEL;TYPE=cell
        # length("TEL;TYPE=cell:") = 14
        # entry 10 is ADR;TYPE=work:
        if (length($v[6]) != 14 || length($v[7]) != 14) {
            my ($i,$s) = (0,"");
            for (; $i<$n; ++$i) {
                next if (($i == 6 || $i == 7) && length($v[$i]) == 14);
                if ($i <= 8) { print $v[$i] . "\n"; }
                elsif ($i >= 9 && !($v[$i] =~ /END:VCARD/)) {
                    $s .= $v[$i] . "\\n";
                } else { print $s . "\n" . $v[$i] ."\n"; }
            }
        }
        @v = ();	# clear stack
        $n = 0;
    }
}