Consulting

Results 1 to 4 of 4

Thread: Extract Members of an Outlook Dynamic Distribution List to excel worksheet

  1. #1
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    389
    Location

    Extract Members of an Outlook Dynamic Distribution List to excel worksheet

    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
    The most difficult errors to resolve are the one's you know you didn't make.


  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    612
    Location
    https://answers.microsoft.com/en-us/...4-7e622e01e354

    Another method :

    https://techniclee.wordpress.com/201...list-to-excel/

    Instructions.

    Follow these instructions to add the code to Outlook.

    1. Start Outlook
    2. Press ALT + F11 to open the Visual Basic Editor
    3. If not already expanded, expand Microsoft Office Outlook Objects
    4. If not already expanded, expand Modules
    5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting InsertModule.
    6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
    7. Click the diskette icon on the toolbar to save the changes
    8. 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


  3. #3
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    389
    Location
    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
    The most difficult errors to resolve are the one's you know you didn't make.


  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    612
    Location
    Neither of the resources were tested here. As for dynamic ... you'll need to test it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •