populating DL item with entries from excel.

G

Guest

Hi All,

Does anybody have any knowledge about creating and populating distribution
lists for ms outlook, using the emails stored in Excel files ? Or generally,
how would you programmatically create distribution lists, with 100s of
entries ?

I have been trying my hand at it and have come across some problems. I have
got it down to this now :

Code:
Sub test()

Dim oExc As Excel.Application
Set oExc = New Excel.Application

Dim oWBook As Excel.Workbook
'Replace with location of your excel file.
Set oWBook = oExc.Workbooks.Open("C:\Documents and
Settings\SAM\Desktop\email.xls")


Dim oA As Outlook.Application
Set oA = New Outlook.Application
Dim oNs As Outlook.NameSpace
Set oNs = oA.GetNamespace("MAPI")
oNs.Logon "Microsoft Outlook", , False, True

Dim oContactsFolder As Outlook.MAPIFolder
Set oContactsFolder = oNs.GetDefaultFolder(olFolderContacts)

Dim oContactItem As Outlook.ContactItem
Dim oMess As Outlook.MailItem
Set oMess = oA.CreateItem(olMailItem)

Dim oRecipients As Outlook.Recipients
Set oRecipients = oMess.Recipients

Dim oDistList As Outlook.DistListItem
Set oDistList = oA.CreateItem(olDistributionListItem)
oDistList.DLName = "email_test"
oDistList.Move oContactsFolder

Dim cnt As Integer
'Dim totalRows As Integer
cnt = 1
'get the total rows in the excel file ....there may be a lib function
that already does this.
While oExc.Cells(cnt, 1) <> ""
totalRows = totalRows + 1
cnt = cnt + 1
Wend


'collect all email addresses from excel file and put them in the
recipients collection
'Col 1 in excel file is full name and col 2 is email address

For cnt = 2 To totalRows
Set oContactItem = oA.CreateItem(olContactItem)
Set oRecipients = oMess.Recipients

oContactItem.FullName = oExc.Cells(cnt, 1)
oContactItem.Email1Address = oExc.Cells(cnt, 2)
oContactItem.Save
oRecipients.Add oExc.Cells(cnt, 2)

If oRecipients.ResolveAll Then
oDistList.AddMembers oRecipients
End If

Set oContactItem = Nothing
Set oRecipients = Nothing
Next cnt

'save DL.
oDistList.Save

'clean up
Set wBook = Nothing
Set oExc = Nothing
Set oDistList = Nothing
Set oContactsFolder = Nothing
Set oMess = Nothing
oNs.Logoff
Set oNs = Nothing
oA.Quit
Set oA = Nothing
End Sub

Above, I pick a name-email address pair fro the excel file and in the for
loop, for each pair, create a contact item, save it, add it to Recipients,
resolve it and add it to the DL.

The above seems to work OK for about 145 records from the excel file and I
have about 600 to add. Anything above 145 causes it to hang.

Can anybody please hlp refine/improve the above ?

Thank you.

-AS.
 
G

Guest

One thing I noticed is that you are continually setting and destroying the
Recipients collection:

Set oRecipients = oMess.Recipients

You should move the above line before your "For cnt = 2 To totalRows"
section, and move "Set oRecipients = Nothing" after the "Next" statement.

Also, you should call wBook.Close, oExc.Quit before the procedure is finished.

Other than that, try doing it without creating Contacts. You can create
Recipient objects on the fly (see the code sample for the AddMembers method
in the Outlook VBA Reference Help file).

Furthermore, if you are NOT using Outlook 2003, there is a limit you may be
reaching when creating a Distribution List based on Contacts:

You receive a "This distribution list has reached the maximum size for your
network e-mail server" error message when you add a large number of items to
a contact distribution list (DL) in Outlook 2000 or in Outlook 2002:
http://support.microsoft.com/default.aspx?scid=kb;en-us;238569


--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard - http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


AS said:
Hi All,

Does anybody have any knowledge about creating and populating distribution
lists for ms outlook, using the emails stored in Excel files ? Or generally,
how would you programmatically create distribution lists, with 100s of
entries ?

I have been trying my hand at it and have come across some problems. I have
got it down to this now :

Code:
Sub test()

Dim oExc As Excel.Application
Set oExc = New Excel.Application

Dim oWBook As Excel.Workbook
'Replace with location of your excel file.
Set oWBook = oExc.Workbooks.Open("C:\Documents and
Settings\SAM\Desktop\email.xls")


Dim oA As Outlook.Application
Set oA = New Outlook.Application
Dim oNs As Outlook.NameSpace
Set oNs = oA.GetNamespace("MAPI")
oNs.Logon "Microsoft Outlook", , False, True

Dim oContactsFolder As Outlook.MAPIFolder
Set oContactsFolder = oNs.GetDefaultFolder(olFolderContacts)

Dim oContactItem As Outlook.ContactItem
Dim oMess As Outlook.MailItem
Set oMess = oA.CreateItem(olMailItem)

Dim oRecipients As Outlook.Recipients
Set oRecipients = oMess.Recipients

Dim oDistList As Outlook.DistListItem
Set oDistList = oA.CreateItem(olDistributionListItem)
oDistList.DLName = "email_test"
oDistList.Move oContactsFolder

Dim cnt As Integer
'Dim totalRows As Integer
cnt = 1
'get the total rows in the excel file ....there may be a lib function
that already does this.
While oExc.Cells(cnt, 1) <> ""
totalRows = totalRows + 1
cnt = cnt + 1
Wend


'collect all email addresses from excel file and put them in the
recipients collection
'Col 1 in excel file is full name and col 2 is email address

For cnt = 2 To totalRows
Set oContactItem = oA.CreateItem(olContactItem)
Set oRecipients = oMess.Recipients

oContactItem.FullName = oExc.Cells(cnt, 1)
oContactItem.Email1Address = oExc.Cells(cnt, 2)
oContactItem.Save
oRecipients.Add oExc.Cells(cnt, 2)

If oRecipients.ResolveAll Then
oDistList.AddMembers oRecipients
End If

Set oContactItem = Nothing
Set oRecipients = Nothing
Next cnt

'save DL.
oDistList.Save

'clean up
Set wBook = Nothing
Set oExc = Nothing
Set oDistList = Nothing
Set oContactsFolder = Nothing
Set oMess = Nothing
oNs.Logoff
Set oNs = Nothing
oA.Quit
Set oA = Nothing
End Sub

Above, I pick a name-email address pair fro the excel file and in the for
loop, for each pair, create a contact item, save it, add it to Recipients,
resolve it and add it to the DL.

The above seems to work OK for about 145 records from the excel file and I
have about 600 to add. Anything above 145 causes it to hang.

Can anybody please hlp refine/improve the above ?

Thank you.

-AS.
 
G

Guest

Thanks Eric.

I read in one of the KB articles that to add an entry to DL, it needs to be
a contact item first.

http://support.microsoft.com/?kbid=269861

If I simply add a name using recipient.Add (user name) and resolve it, I
dont think, it will map it to the correct email address, since these
name-email address pairs are in my excel file and not my contacts folder.
Hence I create a contact for each item first in the the for loop.(instead of
jsut using Recipient)

Also, when I tried to move the distListItem.AddMembers outside for loop, a
list was getting created without any entries, and when I moved it in and
added one contact each time, it worked.

There must be a way out, I can only imagine the number of big companies and
organizations who would have dist lists like 'all' or 'staff' that go out to
1000s of employees. Wonder how that works.

Btw, I am on Outlook 2002, SP2.

Thanks.

AS.




Eric Legault said:
One thing I noticed is that you are continually setting and destroying the
Recipients collection:

Set oRecipients = oMess.Recipients

You should move the above line before your "For cnt = 2 To totalRows"
section, and move "Set oRecipients = Nothing" after the "Next" statement.

Also, you should call wBook.Close, oExc.Quit before the procedure is finished.

Other than that, try doing it without creating Contacts. You can create
Recipient objects on the fly (see the code sample for the AddMembers method
in the Outlook VBA Reference Help file).

Furthermore, if you are NOT using Outlook 2003, there is a limit you may be
reaching when creating a Distribution List based on Contacts:

You receive a "This distribution list has reached the maximum size for your
network e-mail server" error message when you add a large number of items to
a contact distribution list (DL) in Outlook 2000 or in Outlook 2002:
http://support.microsoft.com/default.aspx?scid=kb;en-us;238569


--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard - http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


AS said:
Hi All,

Does anybody have any knowledge about creating and populating distribution
lists for ms outlook, using the emails stored in Excel files ? Or generally,
how would you programmatically create distribution lists, with 100s of
entries ?

I have been trying my hand at it and have come across some problems. I have
got it down to this now :

Code:
Sub test()

Dim oExc As Excel.Application
Set oExc = New Excel.Application

Dim oWBook As Excel.Workbook
'Replace with location of your excel file.
Set oWBook = oExc.Workbooks.Open("C:\Documents and
Settings\SAM\Desktop\email.xls")


Dim oA As Outlook.Application
Set oA = New Outlook.Application
Dim oNs As Outlook.NameSpace
Set oNs = oA.GetNamespace("MAPI")
oNs.Logon "Microsoft Outlook", , False, True

Dim oContactsFolder As Outlook.MAPIFolder
Set oContactsFolder = oNs.GetDefaultFolder(olFolderContacts)

Dim oContactItem As Outlook.ContactItem
Dim oMess As Outlook.MailItem
Set oMess = oA.CreateItem(olMailItem)

Dim oRecipients As Outlook.Recipients
Set oRecipients = oMess.Recipients

Dim oDistList As Outlook.DistListItem
Set oDistList = oA.CreateItem(olDistributionListItem)
oDistList.DLName = "email_test"
oDistList.Move oContactsFolder

Dim cnt As Integer
'Dim totalRows As Integer
cnt = 1
'get the total rows in the excel file ....there may be a lib function
that already does this.
While oExc.Cells(cnt, 1) <> ""
totalRows = totalRows + 1
cnt = cnt + 1
Wend


'collect all email addresses from excel file and put them in the
recipients collection
'Col 1 in excel file is full name and col 2 is email address

For cnt = 2 To totalRows
Set oContactItem = oA.CreateItem(olContactItem)
Set oRecipients = oMess.Recipients

oContactItem.FullName = oExc.Cells(cnt, 1)
oContactItem.Email1Address = oExc.Cells(cnt, 2)
oContactItem.Save
oRecipients.Add oExc.Cells(cnt, 2)

If oRecipients.ResolveAll Then
oDistList.AddMembers oRecipients
End If

Set oContactItem = Nothing
Set oRecipients = Nothing
Next cnt

'save DL.
oDistList.Save

'clean up
Set wBook = Nothing
Set oExc = Nothing
Set oDistList = Nothing
Set oContactsFolder = Nothing
Set oMess = Nothing
oNs.Logoff
Set oNs = Nothing
oA.Quit
Set oA = Nothing
End Sub

Above, I pick a name-email address pair fro the excel file and in the for
loop, for each pair, create a contact item, save it, add it to Recipients,
resolve it and add it to the DL.

The above seems to work OK for about 145 records from the excel file and I
have about 600 to add. Anything above 145 causes it to hang.

Can anybody please hlp refine/improve the above ?

Thank you.

-AS.
 
S

Sue Mosher [MVP-Outlook]

PMJI, but I'd first ask, why? If these are external recipients, e.g. for a newsletter or sales mailing, a mail merge using the Excel sheet as the data source would be more effective than a personal DL.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top