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

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

Hi John
This will create a new subfolder of your Contacts folder. That SEEMS to
be what you are asking for, rather than a distribution list (which I
don't know how to do!)

Public Sub MakeContactsFolders(FolderName As String)
Dim olApplication as OutLook.Application
Dim olNameSpace as OutLook.NameSpace
Dim objFolder As MAPIFolder, objNewFolder As MAPIFolder
Dim objContact As Outlook.ContactItem

Application.ScreenUpdating = False

Set olApplication = CreateObject("Outlook.Application")
Set olNameSpace = olApplication.GetNameSpace("MAPI")
'create a new contacts folder in the Contacts Folder
'you might need to delete it first if it already exists
'****optional code
Set objFolder = olNameSpace.GetDefaultFolder(olFolderContacts)
On Error Resume Next 'see if FolderName exists
Set objNewFolder = objFolder.Folders(FolderName)
On Error GoTo 0
'If folder already exists delete it
If Not objNewFolder Is Nothing Then objNewFolder.Delete
'***end of optional code

Set objNewFolder = objFolder.Folders.Add(FolderName,
olFolderContacts)
'Add some contact info to the created folder
'If using a loop here, need fresh Set objContact for each contact

Set objContact = objNewFolder.Items.Add(olContactItem)
With objContact
'Add some address info
End With
'end loop structure if using one

objNewFolder.ShowAsOutlookAB = True 'see it in addressbook

Set objContact = Nothing
Set objNewFolder = Nothing
Set objFolder = Nothing 'contacts folder
Set olNameSpace = Nothing
Set olApplication = Nothing
End Sub

be careful with line breaks
regards
Paul

John Elink wrote:
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