Problem: You want to export a distribution list into a comma separated value list of email addresses. You may wish to do this to upload the list into another program, such as mailchimp, or for other purposes.
Note that this is a somewhat arduous process. If anyone knows of a script to automate this task, please let me know. One should exist, but I was unable to find one.
- Go into outlook, and start a new email addressed to your distribution list.
- Click the "+" sign next to the list name to expand it into individual email addresses. Press OK if you are presented with a warning about not being able to go back to the single email address.
- With your text caret (the blinking cusor) in the list of email addresses, press control+A to select all email addresses, then Control+C to copy them.
- Go to http://www.burellodesign.com/CodeExampleDetail.cfm?CodeID=12 and paste these into the "Text String" text area, and hit the EXTRACT button
- Select all of the text in the EMAILS text area by pressing control+A, then copy this into memory by pressing Control+C
- Open up Notepad (it must be notepad, it cannot be Word!), and ensure that Format > Word Wrap is NOT checked
- Paste the contents from memory (Control+V)
- Save the document as emails.txt (or whatever you wish to call it)
- Start a new excel spreadsheet, click on the Data tab, then click "From Text" under the Get External Data toolbar section
- Select the emails.txt file you created in step 8
- Import as a Delimited file. Click Next.
- Select Comma for delimiter types (you can uncheck Tab)
- Click Next, then finish.
- Import it into cell A1 (the default destination location)
- Select all of the records on the spreadsheet, then copy them (Control+C)
- Create a new worksheet, then right click in cell A1 and select Paste > Values.
- Select Values from the Paste Special options, and check the "Transpose" dialog box. Click OK
From here, you might be done, but you may notice that some of the records have a space before them, which may cause problems with importing into a new spreadsheet. To remove spaces, follow these steps:
- In column B1, enter the formula =trim(A1) and copy this formula down to the end of the list (You can double-click the dot in the bottom-right hand corner of the black box that surrounds the cell when it is selected to quickly copy the formula down)
- Select the entire contents of column B, then right-click in cell B1 and choose Paste Special
- Select Values and hit OK
- Delete Column A
- Save the file as a CSV or whatever you need to export it as in order to upload into your new program. You can save the file as a CSV by going to File > Save As... and change the file type by selecting CSV (Comma delimited) (*.csv) from the "Save as type:" field directly beneath the filename field. You must click OK on the first dialog warning about exporting only a single worksheet, and Yes on the second dialog about losing formatting settings/other features.