\[Editor's Note: Email your Exchange Server and Outlook solutions (400 words maximum) to R2R at [email protected] Please include your phone number. We edit submissions for style, grammar, and length. If we print your contribution, you'll get $100.\]
You can use two methods to export addresses from the Exchange directory. One method is to use the Save Window Contents option in the Exchange Administrator, which lets you display each type of email address in a separate column. However, sometimes this option doesn't give you the columns you want or the specific mailboxes or custom recipients (e.g., hidden recipients) you're looking for. Therefore, a second method you can use is the Directory Export command in Exchange Administrator.
A disadvantage of this method is that when you specify the column header email addresses in your output file, Exchange dumps all the addresses for each entry in no particular order. One row has the X.400 address followed by the SMTP address, and the next row has the addresses in reverse, for no apparent reason.
You can use Microsoft Excel to easily filter out each address type into an individual column. Follow these steps:
- Open the exported Comma Separated Values (.csv) file in Excel. Each spreadsheet has a limit of 65,536 lines.
- Copy the email addresses column for each type of address. For example, if you're exporting just X.400 and SMTP addresses, make one extra copy of each column.
- Perform two wildcard search-and-replace functions on each column, based on the embedded address type prefixes. For example, for the SMTP column, select the column, then search for *SMTP: and replace with SMTP. Now search for %* and replace with <nothing>. This step leaves you with just one SMTP address per cell, with the prefix SMTP:. If you don't intend to reimport the file into Exchange, you can use another search-and-replace command to delete the SMTP: prefix.
- Label each column appropriately. If you reimport the file, you'll need to perform multiple imports or use the concatenate function (the & symbol) in Excel to rebuild the email addresses column. Remember to separate each address entry with the necessary embedded % symbol.
For example, to rebuild the SMTP and X.400 columns into one address column, you use
=A2&"%"&B2where A2 contains the SMTP: prefixed address and B2 contains the X400: prefixed address.
Note that this approach doesn't work on the secondary-proxy-addresses column because you might have multiple additional SMTP: or X.400: addresses in that column.