Thanks Steve. I'm still having trouble with the Add Member function
of the Distribution list. Here is my code:
TT = ActiveCell.Offset(R, C)
Do While TT <> ""
Do While ActiveCell.Offset(R - 2 - i, C + 1) <> "Grand Total"
'Goes through and pulls all the people that are in a certain
committee
If ActiveCell.Offset(R, C + 1) <> "" And ActiveCell.Offset(R - 2
- i, C + 1) <> "Total" Then
If List <> "" Then
List = List & "; " & ActiveCell.Offset(R - 3 - i,
C + 1)
Else
List = ActiveCell.Offset(R - 3 - i, C + 1)
End If
End If
C = C + 1
Loop
Sheets("Mailing Lists").Activate
Range("A1").Select
ActiveCell.Offset(R, 0) = TT
ActiveCell.Offset(R, 1) = List
Set myDistList = olApp.CreateItem(olDistributionListItem)
With myDistList
.DLName = TT
.AddMember (List)
.Save
End With
List = ""
Sheets("Topic Teams").Activate
Range("A5").Select
R = R + 1
i = i + 1
C = 0
TT = ActiveCell.Offset(R, C)
Loop
Set olApp = Nothing
----------------------------------------
I realize that I may be using Add member incorrectly. However, if I
do this manually, this is what I do. I literally copy what is in the
string "List." I then create a new distribution list in Excel. Name
it. Click on "Select Members" and then paste "List" in the "Members"
field.
Thanks!
On Oct 4, 6:18 pm, "Steve Yandl" <syandl_nos...@comcast.net> wrote:
> This is an example of a subroutine that looks across row 1 and creates
> distribution lists for each of the names found in those cells in the Outlook
> Contacts folder. You need to go to 'Tools > References' and set a reference
> to the Outlook library for the version of Office you have.
>
> ________________________________
>
> Sub MakeOLdistrList()
>
> Dim olApp As Outlook.Application
> Dim myDistList As Outlook.DistListItem
>
> Set olApp = New Outlook.Application
>
> colCount = ActiveSheet.UsedRange.Columns.Count
>
> For C = 1 To colCount
> Set myDistList = olApp.CreateItem(olDistributionListItem)
> With myDistList
> .DLName = Cells(1, C).Value
> .Save
> End With
> Next C
>
> Set olApp = Nothing
>
> End Sub
>
> ________________________________
>
> Steve
>
> <christopher.y...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >I manage a list of several committees within my group at work and each
> > group has anywhere from 2 to 40 members. Each person is on 3 to 5
> > committees. I want to create a macro that can easily create Outlook
> > distribution lists since people keep joining my group and people can
> > always change committee preferences.
>
> > I have already created a macro that scrolls through the committee
> > matrix and creates a string for each committee with all the members
> > included. The string is composed of the names (copied from the
> > Outlook directory) seperated by a semi-colon (ie. Summer, Kyle; Lindy,
> > Maria; Sampson, Oscar). This way, I can just copy the cell and
> > manually create a distribution list in outlook, click on add members,
> > and all I have to do is paste the list as opposed to individually
> > adding each member.
>
> > I would like to expand it so it actually creates the distribution
> > lists automatically and either saves them in my Outlook contacts or
> > saves them to the desktop or my documents so you can simply drag them
> > into Outlook. That would probably be the easiest way as it would then
> > prompt you if you want to overwrite existing distribution lists with
> > the same names.
>
> > I've found pieces of code scattered across the internet, but I get
> > errors when the VBA is simply compiling.
>
> > Thanks!!- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
