Export members of distribution list

K

Kees van Amerongen

I'm trying to get a list with the members of the distribution list from
Outlook 2003.



If found a macro on the internet that gives however the following error



Error: Objectvariabele or blockvariabele With is not assigned Error #91



Can anybody help me to correct this macro?







The macro is :



Public Sub DLToWord()
'This macro requires project references to the Outlook
'and Word object models.
Dim objOutlook As Outlook.Application
Dim objExplorer As Outlook.Explorer
Dim objSelection As Outlook.Selection
Dim objDL As Outlook.DistListItem
Dim objRecipient As Outlook.Recipient

Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim objTable As Word.Table
Dim objRange As Word.Range

Dim lngCount As Long
Dim lngIndex As Long
Dim strName As String
Dim strAddress As String
Dim strListName As String

On Error GoTo ErrorHandler

Set objOutlook = CreateObject("Outlook.Application")

'Get the currently active folder window.
Set objExplorer = objOutlook.ActiveExplorer

'Get the selected item(s) in that folder.
Set objSelection = objExplorer.Selection

'Look for 1 distribution list item to be selected.
If (objSelection.Count = 1) And (objSelection.Item(1).Class =
olDistributionList) Then

'Get the selection.
Set objDL = objSelection.Item(1)

'Get the name of the distribution list.
strListName = objDL.DLName

'Go to next line if there is an error.
On Error Resume Next

'See if Word is already open.
Set objWord = GetObject(, "Word.Application")

'If not, create a new Word application object.
If objWord Is Nothing Then
Set objWord = CreateObject("Word.Application")
End If

'Now back to the normal error handler.
On Error GoTo ErrorHandler

'Add a new document to Word and activate it.
Set objDoc = objWord.Documents.Add
objDoc.Activate

'Set the active range to the document start.
Set objRange = objDoc.Range(0, 0)

'See how many items are in the distribution list.
lngCount = objDL.MemberCount

'Add a Word table, with 1 more row than list members,
'use the first row for the list name. Table has 2 columns.
Set objTable = objDoc.Tables.Add(objRange, lngCount + 1, 2)

'Use With to make the code faster.
With objTable
'Insert the list name in the first row, first column.
objTable.Cell(1, 1).Range.InsertAfter strListName

'Make the list name bold face.
.Cell(1, 1).Range.Bold = True

'Now loop through the members of the list.
For lngIndex = 1 To lngCount
'Get a list member
Set objRecipient = objDL.GetMember(lngIndex)

'Get the name of the list member.
strName = objRecipient.Name

'Get the e-mail address of the list member.
strAddress = objRecipient.Address

'Insert the name and e-mail address.
.Cell(lngIndex + 1, 1).Range.InsertAfter strName
.Cell(lngIndex + 1, 2).Range.InsertAfter strAddress
Next lngIndex
.Columns.AutoFit
End With

'Move the cursor to the end of the document.
objWord.Selection.HomeKey Unit:=wdStory, Extend:=wdMove

'Make the document visible.
objWord.Visible = True
objDoc.ActiveWindow.Visible = True
Else
MsgBox "Not a Distribution List or more than 1 item is selected"
End If

MacroExit:
Set objOutlook = Nothing
Set objExplorer = Nothing
Set objSelection = Nothing
Set objDL = Nothing
Set objRecipient = Nothing

Set objWord = Nothing
Set objDoc = Nothing
Set objTable = Nothing
Set objRange = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Description & vbCrLf & "Error # " _
& Err.Number

Err.Clear
GoTo MacroExit
End Sub
 
S

Sue Mosher [MVP-Outlook]

The symptoms suggest that Outlook is not displaying an Explorer window, a window where the use can make a selection.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


Kees van Amerongen said:
Set objSelection = objExplorer.Selection


"Sue Mosher [MVP-Outlook]" <[email protected]> schreef in bericht
Show the code statement that raises the error.

Kees van Amerongen said:
I'm trying to get a list with the members of the distribution list from
Outlook 2003.

If found a macro on the internet that gives however the following error

Error: Objectvariabele or blockvariabele With is not assigned Error
#91

Can anybody help me to correct this macro?

The macro is :

Public Sub DLToWord()
'This macro requires project references to the Outlook
'and Word object models.
Dim objOutlook As Outlook.Application
Dim objExplorer As Outlook.Explorer
Dim objSelection As Outlook.Selection
Dim objDL As Outlook.DistListItem
Dim objRecipient As Outlook.Recipient

Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim objTable As Word.Table
Dim objRange As Word.Range

Dim lngCount As Long
Dim lngIndex As Long
Dim strName As String
Dim strAddress As String
Dim strListName As String

On Error GoTo ErrorHandler

Set objOutlook = CreateObject("Outlook.Application")

'Get the currently active folder window.
Set objExplorer = objOutlook.ActiveExplorer

'Get the selected item(s) in that folder.
Set objSelection = objExplorer.Selection

'Look for 1 distribution list item to be selected.
If (objSelection.Count = 1) And (objSelection.Item(1).Class =
olDistributionList) Then

'Get the selection.
Set objDL = objSelection.Item(1)

'Get the name of the distribution list.
strListName = objDL.DLName

'Go to next line if there is an error.
On Error Resume Next

'See if Word is already open.
Set objWord = GetObject(, "Word.Application")

'If not, create a new Word application object.
If objWord Is Nothing Then
Set objWord = CreateObject("Word.Application")
End If

'Now back to the normal error handler.
On Error GoTo ErrorHandler

'Add a new document to Word and activate it.
Set objDoc = objWord.Documents.Add
objDoc.Activate

'Set the active range to the document start.
Set objRange = objDoc.Range(0, 0)

'See how many items are in the distribution list.
lngCount = objDL.MemberCount

'Add a Word table, with 1 more row than list members,
'use the first row for the list name. Table has 2 columns.
Set objTable = objDoc.Tables.Add(objRange, lngCount + 1, 2)

'Use With to make the code faster.
With objTable
'Insert the list name in the first row, first column.
objTable.Cell(1, 1).Range.InsertAfter strListName

'Make the list name bold face.
.Cell(1, 1).Range.Bold = True

'Now loop through the members of the list.
For lngIndex = 1 To lngCount
'Get a list member
Set objRecipient = objDL.GetMember(lngIndex)

'Get the name of the list member.
strName = objRecipient.Name

'Get the e-mail address of the list member.
strAddress = objRecipient.Address

'Insert the name and e-mail address.
.Cell(lngIndex + 1, 1).Range.InsertAfter strName
.Cell(lngIndex + 1, 2).Range.InsertAfter strAddress
Next lngIndex
.Columns.AutoFit
End With

'Move the cursor to the end of the document.
objWord.Selection.HomeKey Unit:=wdStory, Extend:=wdMove

'Make the document visible.
objWord.Visible = True
objDoc.ActiveWindow.Visible = True
Else
MsgBox "Not a Distribution List or more than 1 item is selected"
End If

MacroExit:
Set objOutlook = Nothing
Set objExplorer = Nothing
Set objSelection = Nothing
Set objDL = Nothing
Set objRecipient = Nothing

Set objWord = Nothing
Set objDoc = Nothing
Set objTable = Nothing
Set objRange = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Description & vbCrLf & "Error # " _
& Err.Number

Err.Clear
GoTo MacroExit
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

add to existing distribution list 3
Accessing Global Address List details with VBA 1
Extract Text in braquet and list of terms from 2 Docs 3
Can't even capture if Outlook is running 7
Getting SMTP from Distribution List Members 5
Create macros in Outlook 2007 5
Retrieve Meeting Response Text Associated with an Appointment 1
VBA works in orignal but fails in copy of database 1

Top