Extract Members of an Outlook Dynamic Distribution List to excel worksheet

[CBrine]

I'm wondering if anyone has done this? And if so, what VBA code you used to do it? I've done some searching, but only see power script as an option. Might not be possible?

Thanks in advance.

Cal

[Logit]

https://answers.microsoft.com/en-us/outlook_com/forum/all/copying-a-distribution-list-from-outlook-into/684f5efd-a91e-4c28-8bc4-7e622e01e354

Another method :

https://techniclee.wordpress.com/2013/05/13/exporting-an-outlook-distribution-list-to-excel/

Instructions.Follow these instructions to add the code to Outlook.


Start Outlook
Press ALT + F11 to open the Visual Basic Editor
If not already expanded, expand Microsoft Office Outlook Objects
If not already expanded, expand Modules
Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert → Module.
Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
Click the diskette icon on the toolbar to save the changes
Close the VB Editor




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

Option Explicit

Sub ExportDistributionListToExcel()

'--> Create some constants
Const SCRIPT_NAME = "Export Distribution List to Excel"

'--> Create some variables
Dim olkLst As Object, _
olkRcp As Outlook.RECIPIENT, _
excApp As Object, _
excWkb As Object, _
excWks As Object, _
intCount As Integer, _
lngRow As Long, _
strFilename As String

'--> Initialize variables
lngRow = 2

'--> Main routine
'Turn error handling off
On Error Resume Next
'What type of window is open?
Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
Set olkLst = Application.ActiveExplorer.Selection(1)
Case "Inspector"
Set olkLst = Application.ActiveInspector.CurrentItem
Case Else
Set olkLst = Nothing
End Select
'Was a list open or selected?
If TypeName(olkLst) = "Nothing" Then
'No
MsgBox "You must select or open an item for this macro to work.", vbCritical + vbOKOnly, SCRIPT_NAME
Else
'Yes
'Is the open/selected item a dist list?
If olkLst.Class = olDistributionList Then
'Yes
'Connect to Excel
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Add
Set excWks = excWkb.Worksheets(1)
'Write headers
With excWks
.Cells(1, 1) = "Name"
.Cells(1, 2) = "Address"
End With
'Read the list members and write them to the spreadsheet
For intCount = 1 To olkLst.MemberCount
Set olkRcp = olkLst.GetMember(intCount)
excWks.Cells(lngRow, 1) = olkRcp.Name
excWks.Cells(lngRow, 2) = olkRcp.Address
lngRow = lngRow + 1
Next
'Autofit the columns
excWks.Columns("A:B").AutoFit
'Get a file path/name to save the spreadsheet to
strFilename = InputBox("Enter a path and file name for this export", SCRIPT_NAME, Environ("UserProfile") & "\My Documents" & olkLst.Subject & ".xlsx")
'Did we get a file path/name?
If strFilename = "" Then
'No
'Set the file path to your Documents folder and the file name to the name of the list.
strFilename = Environ("UserProfile") & "\My Documents" & olkLst.Subject & ".xlsx"
Else
'Yes
'If the file extension isn't .xlsx
If Right(LCase(strFilename), 5) <> ".xlsx" Then
'Set the extention so .xlsx
strFilename = strFilename & ".xlsx"
End If
End If
'Close and save the spreadsheet
excWkb.Close True, strFilename
'Did the file save okay?
If Err.Number = 0 Then
'Yes
MsgBox "Export complete.", vbInformation + vbOKOnly, SCRIPT_NAME
Else
'No
'Make Excel visible so the user cansave the file
excApp.Visible = True
End If
Else
'No
MsgBox "The item you selected is not a distribution list. Export cancelled.", vbCritical + vbOKOnly, SCRIPT_NAME
End If
End If

'--> Clean-up
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
Set olkRcp = Nothing
Set olkLst = Nothing
'Turn error handling back on
On Error GoTo 0
End Sub

[CBrine]

Thanks Logit, Do you know if this works with a "Dynamic" distribution list? It's a bit of a different beast from a regular distribution list. It's made up on the fly based on AD attributes assigned to each AD member and some scripting done in the background of AD.

Thanks
Cal

[Logit]

Neither of the resources were tested here. As for dynamic ... you'll need to test it.