John,
This is taken out of an old file of mine. I haven't looked at it for a
couple of years, so you might have to work on it a bit, and you'll have to
tweak it to get the desired cells etc, but it should show you how it can be
done.
Sub CREATE_OUTLOOK_CONTACTS()
Dim MyOlApp As Outlook.Application
Dim OLF As Outlook.MAPIFolder
Dim objContact As Outlook.ContactItem
Dim objDist As Outlook.DistListItem
Dim objRec As Outlook.Recipients
Dim objMAIL As MailItem
Dim COUNTER As Integer
Set MyOlApp = CreateObject("Outlook.Application")
Set OLF = MyOlApp.GetNamespace("MAPI") _
.GetDefaultFolder(olFolderContacts)
Set objMAIL = MyOlApp.CreateItem(olMailItem)
Set objDist = MyOlApp.CreateItem(olDistributionListItem)
Set objRec = objMAIL.Recipients
objDist.Categories = "New Category"
objDist.DLName = "New Distribution List"
COUNTER = 4
Do While Cells(COUNTER, 1) < ""
Set objContact = OLF.Items.Add
With objContact
.FirstName = Cells(COUNTER, 4)
.LastName = Cells(COUNTER, 1)
.Children = Cells(COUNTER, 2)
.HomeAddress = Cells(COUNTER, 6)
.Spouse = Cells(COUNTER, 5)
.HomeTelephoneNumber = Cells(COUNTER, 8)
.MobileTelephoneNumber = Cells(COUNTER, 9)
.OtherTelephoneNumber = Cells(COUNTER, 10)
.HomeFaxNumber = Cells(COUNTER, 11)
.Email1Address = Cells(COUNTER, 12)
.FullName = Cells(COUNTER, 4) & " " & Cells(COUNTER, 1)
.Categories = "New Category"
.Save
objRec.Add .Email1Address
End With
COUNTER = COUNTER + 1
Loop
objDist.AddMembers objRec
objDist.Save
Set objContact = Nothing
Set OLF = Nothing
Set objDist = Nothing
Set objRec = Nothing
End Sub
Robin Hammond
www.enhanceddatasystems.com
"John Elink" wrote in message
...
Hi there
I have Excel file with names (1st column) and e-mail addresses (2nd
column). If I want to add all these names into the Outlook's Contact
list, there is no problem. I use something like as follows
*********************
Set olApplication = CreateObject("Outlook.Application")
Set olNameSpace = olApplication.GetNameSpace("MAPI")
Set olContactFolder =
olNameSpace.GetDefaultFolder(olFolderContacts)
Set olContactItem = olContactFolder.Items.Add
With olContactItem
.LastName = *****here I read from file
.Email1Address = *****here I read from file
.Save
End With
**********************
it creates new item in Contact.
My question is - is it possible to create NEW DISTRIBUTION LIST using
macro? I mean all names/e-mail addresses from the Excel file will be
stored in the new distribution list in Outlook, so these new contacts
will not be mixed with my "normal" contacts
Thank you very much for all comments.
Cheers
Johny