Create Outlook 2007 Distribution List in Excel 2007 - Automate Open of Outl

By zorrodidit ·
Hello to all.

I am voluntering with a charitable group and assisting with an Access 2007 Database and other processes.

So far the Access side of thigs is fine.

I am using Excel 2007 and Outlook 2007 as well, and this is where I need some help.

I have a Macro in an Excel 2007 spreadsheet which creates an Outllook 2007 Distribution List from the three columns of data in the Excel 2007 spreadsheet.

The three columns have headings, from left to right "Company", "Contrubutor" and "E-mail Address" (the cells below these headings are populated with data)

The problem is that when I run the Macro, it creates the Outlook Distribution List, but the list has the email address in both the Name and the Email columns of the Outlook Distribution List with no names showing.

I hope someone can help with this, so that I get the names to appear in the Outlook 2007 Distribution List.

Here is the code I am using in the Macro/Module in case there is a remedy that can be achieved by changing the code.

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

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

End Sub

I am also seeking find help with getting the code changed so that it will automate opening of Outlook 2007, as the way it presently is, I must have Outlook 2007 already open for the macro to work.

Any assistance would be greatly appreciated.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums