writing outlook distribution lists through vba

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,534
I am trying to determine all the members of a distribution list - but get an error on the line in bold below - stating that you can't assign to a constant. Can somebody explain what I am doing wrong ? Thks

Kaps

Rich (BB code):
Option Explicit
 
Public olApp As Outlook.Application
Public olNs As Namespace
Public Fldr As Object
Public olCi As Object
Public objRecip As Object

Public Row As Long
Public No_Of_Items As Long
Public objContact As ContactItem
Public objDistList As DistListItem
Public Temp As String
Public Total_No_of_members_in_Dist_List As Long
Public No_Of_Dist_List As Long
Public All_Outlook_data As Range


Public Distribution_List_Names(1000) As String

'Number of member of distribution Lists

Public No_of_Members_Of_Distribution_list(1000) As Long


Public Sub Define_Ranges()

Set All_Outlook_data = Sheets("Front Sheet").Range("Data_Range")

With All_Outlook_data
    .Clear
End With


End Sub


Public Sub Get_Names_And_Size_Of_Distribution_Lists()


Dim i As Long

Total_No_of_members_in_Dist_List = 0

i = 1


Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(10).Items



'Loop through the items




For Each olCi In Fldr
 
'Check if we have a distribution list

 If olCi.Class = olDistributionList Then
 
         'If so get the name of the distribution list
        
         Distribution_List_Names(i) = olCi.DLName
                 
         'Get the number of members of the distribution list
         
      No_of_Members_Of_Distribution_list(i) = olCi.MemberCount
     
    i = i + 1
    
Total_No_of_members_in_Dist_List = Total_No_of_members_in_Dist_List + No_of_Members_Of_Distribution_list(i)
 
 End If
 
 No_Of_Dist_List = i - 1
 
 
Next




End Sub


Public Sub Write_Out_Names_of_Distribution_Lists()
'writes out the names of all the distribution lists

Dim j As Long
Dim k As Long
Dim r As Long

Dim next_free_row As Long

'Loop through each distribution list

r = 1


For j = 1 To No_Of_Dist_List

        MsgBox j & " " & Distribution_List_Names(j) & " " & No_of_Members_Of_Distribution_list(j)
        
        
       
        
        'check if there are any members in the list
        
        'Deal with those distribution lists that have members
        
        
        If No_of_Members_Of_Distribution_list(j) > 0 Then
        
                For k = 1 To No_of_Members_Of_Distribution_list(j)
                
                    next_free_row = Sheets("Front Sheet").Range("B13").End(xlDown).Row
                     
                     'Write out the name of the distribution list
                     
                     
                    With Sheets("Front Sheet").Range("B" & next_free_row + 1).Offset(r, 0)
                        .Value = Distribution_List_Names(j)
                    End With
                          
                          
                    Set olDistList = Fldr(Distribution_List_Names(j))
                    
                    
                    r = r + 1
                    
                Next
       
        
        End If
        
        'Deal with the empty distribution lists
        
        
       If (No_of_Members_Of_Distribution_list(j) = 0) Then
            
            With Sheets("Front Sheet").Range("B" & next_free_row + 1).Offset(r, 0)
                        .Value = Distribution_List_Names(j)
            End With
                    
            r = r + 1
            
       
       End If
       


Next j


End Sub
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,122,535
Messages
5,596,741
Members
414,094
Latest member
dingo_baby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top