# Importing Excel directly into a new Outlook Dist List



## Chris_E (Feb 16, 2006)

Using Outlook 2007...

This is a project for work. Our marcomms lady has an Outlook email contact list of over 2000 contacts, and these are put into various business distribution lists (by country, by business type etc)...

An new campaign has started and we are about to add another 1300 new contacts and these come from a business application that exports to CSV format. Using Excel, I have created a "Named Range" and can import them with no difficulty into my own Outlook Contact's list as individual contacts. This was where I spotted the problem.

In the case of our marcomms lady with over 2000 existing contacts, if we import another 1300 from Excel, she will then have to trawl through 3300 contacts to find the one's she has imported and then create a distribution list for them, in line with her project. In fact this is what she has done for the past 18 months!! Nightmare!!

So, I want to find a way to import from Excel DIRECTLY into a new or exsiting distribution list in Outlook.

Outlook gives me no view of distribution lists in my contacts area, so does anyone know how to do this?

Chris


----------



## Rollin_Again (Sep 4, 2003)

This can be done via a VBA macro. I wrote a macro to do something similar a couple of years ago. I'll dig around in my code archive and see if I can find it. If I'm not mistaken I think I remember that there was a limit to how many names could be added to a distribution list but I'll have to check to be sure. Will try to post some code for you when I get to work in about 1 hour unless Zack B (Firefytr) beats me to it.

Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

OK...I'm at work and found my old code. Can you post your sample excel workbook so I can see how the data is arranged? If you prefer not to post you can send to Rollin_Again at hotmail dot com.

Regards,
Rollin


----------



## Chris_E (Feb 16, 2006)

Thanks Rollin!

Sadly, I'd be shot, hung, murdered and then killed (for good measure) if I emailed a customer list out, so please forgive if I don't do that.

In place, the arrangement is quite simple:

Col1 Forename 
Col2 Surname 
Col3 EmailAddress

And that's it!

Does this help you?

Sorry about the security issue - I can't risk the data going external!

Thanks

Chris


----------



## Rollin_Again (Sep 4, 2003)

Which row does the first contact record appear in? Also, are there any blank rows of data between contact records?

Regards,
Rollin


----------



## Chris_E (Feb 16, 2006)

Assume first is in Row 1, no blanks

Chris


----------



## Rollin_Again (Sep 4, 2003)

Open the Excel workbook containing the list of records. Make sure the first record is on line 1. Press *ALT + F11* to open the VBA editor and then click *TOOLS --> REFERENCES* and set reference to Microsoft Outlook and then click OK. Next, in the VBA editor, click *INSERT --> MODULE* and copy and past the code below into the blank module. Close the VBA editor, save the workbook and run the code by clicking *TOOLS --> MACROS* and then selecting the macro "DistributionList"

When the distribution list comes up you can manually save it.


```
Public Sub DistributionList()

Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objDistList As Outlook.DistListItem
Dim objMail As Outlook.MailItem
Dim objRecipients As Outlook.Recipients


Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objDistList = objOutlook.CreateItem(olDistributionListItem)
Set objMail = objOutlook.CreateItem(olMailItem)
Set objRecipients = objMail.Recipients
objDistList.DLName = InputBox("Enter name of Distribution List")

For i = 1 To Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
objRecipients.Add (Range("C" & i).Value)
Next i

objDistList.AddMembers objRecipients
objDistList.Display
objRecipients.ResolveAll

Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objDistList = Nothing
Set objMail = Nothing
Set objRecipients = Nothing

End Sub
```
Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

Another option you can consider is to have the user export his/her existing contact list to a backup *.pst* or *.csv* file. Once the existing contact list has been exported out of Outlook you can delete the contacts within the application itself. The contact list within Outlook will now be empty and you can import only the new addresses and names from your Excel named range. Since the new contact list within Outlook will only contain the newly imported records, you can now create the new distribution list save it. Once the new distribution list has been created you can re-import the original contacts from the backup file you created earlier.

Regards,
Rollin


----------



## Charles_Bukowski (May 3, 2007)

I want to do the same process as the guy was explaining earlier but I have more fields than he has listed. I want to import from Excel into Outlook the following information in the following order.
Company Name, Last Name, First Name, Street Address, City, State, Zip, Country, Title, Phone Number, Fax Number, Email Address, and Notes.

Is this possible or do I have to use the layout that you get when you export your contact list from Outlook to Excel?


----------



## Rollin_Again (Sep 4, 2003)

Charles,

What you want to do is possible but you would need to first create a contact item for each records before creating the distribution list. Once each individual contact record is created you can easily create the distribution list and resolve the names automatically to the existing records.

I'll try to whip something up for you if you post me a sample file.

Regards,
Rollin


----------



## Charles_Bukowski (May 3, 2007)

Here is the XLS file that we want to use to manage our customer contact information. If you need something else please let me know. 

Thanks


----------



## Rollin_Again (Sep 4, 2003)

Are you planning on attaching the macro to the Excel workbook or do you want to attach the macro to your Outlook session?

Regards,
Rollin


----------



## Charles_Bukowski (May 3, 2007)

I would rather do which ever is easier for our users... which ever you think is easier is the method that we would like to use. We also have explored using a Mail Merge between excel and word to accomplish this but I am not sure that this method would be easier for the users to figure out.


----------



## Rollin_Again (Sep 4, 2003)

Just for clarification, are you wanting to create distribution lists from the records or simply create a one time mail with the recipients coming from the Excel workbook?

Is this going to be done on a regular basis? If so, will the Excel file always have the same layout and number of columns? In my opinion it would be easiest to add the macro to Outlook and then use the code to browse to the workbook that you would like the code to execute on. Will try to post a sample today.

Regards,
Rollin


----------



## Charles_Bukowski (May 3, 2007)

This will be done on a somewhat regular basis. They plan on using it every month or so for mailings to customers. There might be some rows added and maybe a few rows delted. This is a rough sketch but if you leave some comment with the code we should be able to figure it out. If you believe its easier to do it from outlook than that is fine. Thank you very much.


----------



## Rollin_Again (Sep 4, 2003)

Charles do you still need this macro? Here is some code that will go through each record in your workbook and create a contact and then add to a distribution list. This code should be attached to the Excel workbook and you need to add reference to the Outlook Object Libary. To add the reference just open the workbook and click *ALT + F11* to bring up the VBA editor. Then click *TOOLS --> REFERENCES* and check *MICROSOFT OUTLOOK.*


```
Dim appOutlook As Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objContactFolder As Outlook.MAPIFolder
Dim objContacts As Outlook.ContactItem
Dim myDistList As Outlook.DistListItem

Sub DistList()


Set appOutlook = GetObject(, "Outlook.Application")
Set objNameSpace = appOutlook.GetNamespace("MAPI")
Set objContactFolder = objNameSpace.GetDefaultFolder(olFolderContacts)
Set myMailItem = appOutlook.CreateItem(olMailItem)
Set myRecipients = myMailItem.Recipients
Set myDistList = appOutlook.CreateItem(olDistributionListItem)

For i = 3 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Set objContacts = objContactFolder.Items.Add(olContactItem)

With objContacts
    .CompanyName = Range("B" & i).Value
    .LastName = Range("C" & i).Value
    .FirstName = Range("D" & i).Value
    .BusinessAddress = Range("E" & i).Value
    .BusinessAddressCity = Range("F" & i).Value
    .BusinessAddressState = Range("G" & i).Value
    .BusinessAddressPostalCode = Range("H" & i).Value
    .BusinessAddressCountry = Range("I" & i).Value
    .JobTitle = Range("J" & i).Value
    .BusinessTelephoneNumber = Range("K" & i).Value
    .BusinessFaxNumber = Range("L" & i).Value
    .Email1Address = Range("M" & i).Value
    .Body = Range("N" & i).Value
    .Save
End With

 
    myRecipients.Add (Range("M" & i).Value)
    
   
   Next

 
   myRecipients.ResolveAll
   myDistList.AddMembers myRecipients
   myDistList.Display
   

End Sub
```
Regards,
Rollin


----------



## Chris_E (Feb 16, 2006)

Yet another bonus point to you Rollin!

Thanks again!!

Chris


----------



## Charles_Bukowski (May 3, 2007)

Many Thanks!


----------



## Fletch_newguy (Oct 2, 2007)

Hi,

Found this via a google search today - Rollin's answer is so brilliant I decided to join! Please bare with me as I have never got so interactive with these sorts of things before.

I wanted to ask Rollin if there is a tweak that could be applied to his vba script that would allow excel to create the Distribution List in an alternative Contacts Folder (rather than my default one).

Many thanks,

Fletch


----------

