Copying email addresses into a distribution list

Hello All,

I have a list of email addresses in a spreadsheet, and am
trying to write a macro to copy each email address into a
distribution list in outlook. However, I'm having trouble
trying to get VB to switch over to excel to pick up the
cells.
I guess this is quite a trivial one, but I can't get it to
work!

Cheers

Lawrence

OK, I've starated to write it in excel. However, I'm a
complete novice when it comes to outlook coding, so most
of what I've got here I've just pasted from help pages in
outlook.

Here's the code. It crashes on the first line (great
start, huh?), saying "User-defined type not defined"...

Sub emailcamp()

Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myDistList As Outlook.DistListItem
Dim myTempItem As Outlook.MailItem
Dim myRecipients As Outlook.Recipients
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myDistList = myOlApp.CreateItem(olDistributionListItem)
Set myTempItem = myOlApp.CreateItem(olMailItem)
Set myRecipients = myTempItem.Recipients
myDistList.DLName = "LDQuestionnaire"


Workbooks("ldvip.xls").Activate
Range("b1").Select

For x = 1 To 4000
email = ActiveCell.Value
myOlApp.ActiveWindow
myDistList.AddMembers email
myDistList.Display

Workbooks("ldvip.xls").Activate
ActiveCell.Offset(1, 0).Activate
Next x


End Sub

Hopefully what I'm trying to do here is self-explanatory,
if incorrect!

Thanks for your help,

Lawrence

>-----Original Message-----
>Can you post your code?
>
>Actually, it would probably make more sense to code this
macro in Excel, as this is the contextual source of the
information. For instance, you can prevent the code from
running unless a proper range (containing the e-mail
addresses) has been selected, the worksheet is open, etc.
>
>--
>Eric Legault - B.A, MCP, MCSD, Outlook MVP
>--------------------------------------------------
>{Private e-mails ignored}
>Job: http://www.imaginets.com
>Blog: http://blogs.officezealot.com/legault/
>
>
>
>"Lawrence" wrote:
>
>> Hello All,
>>
>> I have a list of email addresses in a spreadsheet, and
am
>> trying to write a macro to copy each email address into
a
>> distribution list in outlook. However, I'm having
trouble
>> trying to get VB to switch over to excel to pick up the
>> cells.
>> I guess this is quite a trivial one, but I can't get it
to
>> work!
>>
>> Cheers
>>
>> Lawrence
>>
>.
>

Eric,

Thanks for your help. The macro you've edited is now
clearly doing something right, however, none of the emails
are 'resolved'...

How do I sort this?

Cheers,

Lawrence

>-----Original Message-----
>The first error can be resolved by choosing "Microsoft
Outlook X.0 Object Library" in Tools|References.
>
>I took the liberty of modifying your code - it should
work for you now. See my inline comments to see what I
changed.
>
>Sub emailcamp()
>On Error Resume Next
>
> Dim myOlApp As New Outlook.Application
> Dim myNameSpace As Outlook.Namespace
> Dim myDistList As Outlook.DistListItem
> Dim myTempItem As Outlook.MailItem
> Dim myRecipients As Outlook.Recipients
>
> 'declare these as well
> Dim x As Integer, Email As String
> Dim myRecipient As Outlook.Recipient
>
> Set myNameSpace = myOlApp.GetNamespace("MAPI")
> Set myDistList = myOlApp.CreateItem
(olDistributionListItem)
> Set myTempItem = myOlApp.CreateItem(olMailItem)
> Set myRecipients = myTempItem.Recipients
>
> myDistList.DLName = "LDQuestionnaire"
> Workbooks("ldvip.xls").Activate
> Range("b1").Select
>
> For x = 1 To 4000
> Email = ActiveCell.Value
> 'myOlApp.ActiveWindow - Don't need this
> 'myDistList.AddMembers Email - must add to
Recipients collection instead
> myRecipients.Add Email
> 'myDistList.Display - save for last
> Workbooks("ldvip.xls").Activate
>
> 'ActiveCell.Offset(1, 0).Activate - must use
looping variable to select next cell in range
> ActiveCell.Offset(x, 0).Activate
> Next x
>
> If Not myRecipients.ResolveAll Then
> For Each myRecipient In myRecipients
> If Not myRecipient.Resolved Then
> MsgBox myRecipient.Name & " was not
resolved."
> End If
> Next
> End If
>
> myDistList.AddMembers myRecipients
> myDistList.Save
> myDistList.Display
>
> 'Clean up
> Set myDistList = Nothing
> Set myRecipients = Nothing
> Set myRecipient = Nothing
> Set myTempItem = Nothing
> Set myNameSpace = Nothing
> Set myOlApp = Nothing
>End Sub
>
>--
>Eric Legault - B.A, MCP, MCSD, Outlook MVP
>--------------------------------------------------
>{Private e-mails ignored}
>Job: http://www.imaginets.com
>Blog: http://blogs.officezealot.com/legault/
>
>
>
>"Lawrence" wrote:
>
>> OK, I've starated to write it in excel. However, I'm a
>> complete novice when it comes to outlook coding, so
most
>> of what I've got here I've just pasted from help pages
in
>> outlook.
>>
>> Here's the code. It crashes on the first line (great
>> start, huh?), saying "User-defined type not defined"...
>>
>> Sub emailcamp()
>>
>> Dim myOlApp As New Outlook.Application
>> Dim myNameSpace As Outlook.NameSpace
>> Dim myDistList As Outlook.DistListItem
>> Dim myTempItem As Outlook.MailItem
>> Dim myRecipients As Outlook.Recipients
>> Set myNameSpace = myOlApp.GetNamespace("MAPI")
>> Set myDistList = myOlApp.CreateItem
(olDistributionListItem)
>> Set myTempItem = myOlApp.CreateItem(olMailItem)
>> Set myRecipients = myTempItem.Recipients
>> myDistList.DLName = "LDQuestionnaire"
>>
>>
>> Workbooks("ldvip.xls").Activate
>> Range("b1").Select
>>
>> For x = 1 To 4000
>> email = ActiveCell.Value
>> myOlApp.ActiveWindow
>> myDistList.AddMembers email
>> myDistList.Display
>>
>> Workbooks("ldvip.xls").Activate
>> ActiveCell.Offset(1, 0).Activate
>> Next x
>>
>>
>> End Sub
>>
>> Hopefully what I'm trying to do here is self-
explanatory,
>> if incorrect!
>>
>> Thanks for your help,
>>
>> Lawrence
>>
>> >-----Original Message-----
>> >Can you post your code?
>> >
>> >Actually, it would probably make more sense to code
this
>> macro in Excel, as this is the contextual source of the
>> information. For instance, you can prevent the code
from
>> running unless a proper range (containing the e-mail
>> addresses) has been selected, the worksheet is open,
etc.
>> >
>> >--
>> >Eric Legault - B.A, MCP, MCSD, Outlook MVP
>> >--------------------------------------------------
>> >{Private e-mails ignored}
>> >Job: http://www.imaginets.com
>> >Blog: http://blogs.officezealot.com/legault/
>> >
>> >
>> >
>> >"Lawrence" wrote:
>> >
>> >> Hello All,
>> >>
>> >> I have a list of email addresses in a spreadsheet,
and
>> am
>> >> trying to write a macro to copy each email address
into
>> a
>> >> distribution list in outlook. However, I'm having
>> trouble
>> >> trying to get VB to switch over to excel to pick up
the
>> >> cells.
>> >> I guess this is quite a trivial one, but I can't get
it
>> to
>> >> work!
>> >>
>> >> Cheers
>> >>
>> >> Lawrence
>> >>
>> >.
>> >
>>
>.
>

What format are the e-mail address strings in your cells?

If the string is a valid e-mail address ("joe@mail.com"), it will resolve properly. If it is a name ("Joe Blow"), it will only resolve if that name exists in your address book.

--
Eric Legault - B.A, MCP, MCSD, Outlook MVP
--------------------------------------------------
{Private e-mails ignored}
Job: http://www.imaginets.com
Blog: http://blogs.officezealot.com/legault/

"Lawrence" wrote:

> Eric,
>
> Thanks for your help. The macro you've edited is now
> clearly doing something right, however, none of the emails
> are 'resolved'...
>
> How do I sort this?
>
> Cheers,
>
> Lawrence
>
> >-----Original Message-----
> >The first error can be resolved by choosing "Microsoft
> Outlook X.0 Object Library" in Tools|References.
> >
> >I took the liberty of modifying your code - it should
> work for you now. See my inline comments to see what I
> changed.
> >
> >Sub emailcamp()
> >On Error Resume Next
> >
> > Dim myOlApp As New Outlook.Application
> > Dim myNameSpace As Outlook.Namespace
> > Dim myDistList As Outlook.DistListItem
> > Dim myTempItem As Outlook.MailItem
> > Dim myRecipients As Outlook.Recipients
> >
> > 'declare these as well
> > Dim x As Integer, Email As String
> > Dim myRecipient As Outlook.Recipient
> >
> > Set myNameSpace = myOlApp.GetNamespace("MAPI")
> > Set myDistList = myOlApp.CreateItem
> (olDistributionListItem)
> > Set myTempItem = myOlApp.CreateItem(olMailItem)
> > Set myRecipients = myTempItem.Recipients
> >
> > myDistList.DLName = "LDQuestionnaire"
> > Workbooks("ldvip.xls").Activate
> > Range("b1").Select
> >
> > For x = 1 To 4000
> > Email = ActiveCell.Value
> > 'myOlApp.ActiveWindow - Don't need this
> > 'myDistList.AddMembers Email - must add to
> Recipients collection instead
> > myRecipients.Add Email
> > 'myDistList.Display - save for last
> > Workbooks("ldvip.xls").Activate
> >
> > 'ActiveCell.Offset(1, 0).Activate - must use
> looping variable to select next cell in range
> > ActiveCell.Offset(x, 0).Activate
> > Next x
> >
> > If Not myRecipients.ResolveAll Then
> > For Each myRecipient In myRecipients
> > If Not myRecipient.Resolved Then
> > MsgBox myRecipient.Name & " was not
> resolved."
> > End If
> > Next
> > End If
> >
> > myDistList.AddMembers myRecipients
> > myDistList.Save
> > myDistList.Display
> >
> > 'Clean up
> > Set myDistList = Nothing
> > Set myRecipients = Nothing
> > Set myRecipient = Nothing
> > Set myTempItem = Nothing
> > Set myNameSpace = Nothing
> > Set myOlApp = Nothing
> >End Sub
> >
> >--
> >Eric Legault - B.A, MCP, MCSD, Outlook MVP
> >--------------------------------------------------
> >{Private e-mails ignored}
> >Job: http://www.imaginets.com
> >Blog: http://blogs.officezealot.com/legault/
> >
> >
> >
> >"Lawrence" wrote:
> >
> >> OK, I've starated to write it in excel. However, I'm a
> >> complete novice when it comes to outlook coding, so
> most
> >> of what I've got here I've just pasted from help pages
> in
> >> outlook.
> >>
> >> Here's the code. It crashes on the first line (great
> >> start, huh?), saying "User-defined type not defined"...
> >>
> >> Sub emailcamp()
> >>
> >> Dim myOlApp As New Outlook.Application
> >> Dim myNameSpace As Outlook.NameSpace
> >> Dim myDistList As Outlook.DistListItem
> >> Dim myTempItem As Outlook.MailItem
> >> Dim myRecipients As Outlook.Recipients
> >> Set myNameSpace = myOlApp.GetNamespace("MAPI")
> >> Set myDistList = myOlApp.CreateItem
> (olDistributionListItem)
> >> Set myTempItem = myOlApp.CreateItem(olMailItem)
> >> Set myRecipients = myTempItem.Recipients
> >> myDistList.DLName = "LDQuestionnaire"
> >>
> >>
> >> Workbooks("ldvip.xls").Activate
> >> Range("b1").Select
> >>
> >> For x = 1 To 4000
> >> email = ActiveCell.Value
> >> myOlApp.ActiveWindow
> >> myDistList.AddMembers email
> >> myDistList.Display
> >>
> >> Workbooks("ldvip.xls").Activate
> >> ActiveCell.Offset(1, 0).Activate
> >> Next x
> >>
> >>
> >> End Sub
> >>
> >> Hopefully what I'm trying to do here is self-
> explanatory,
> >> if incorrect!
> >>
> >> Thanks for your help,
> >>
> >> Lawrence
> >>
> >> >-----Original Message-----
> >> >Can you post your code?
> >> >
> >> >Actually, it would probably make more sense to code
> this
> >> macro in Excel, as this is the contextual source of the
> >> information. For instance, you can prevent the code
> from
> >> running unless a proper range (containing the e-mail
> >> addresses) has been selected, the worksheet is open,
> etc.
> >> >
> >> >--
> >> >Eric Legault - B.A, MCP, MCSD, Outlook MVP
> >> >--------------------------------------------------
> >> >{Private e-mails ignored}
> >> >Job: http://www.imaginets.com
> >> >Blog: http://blogs.officezealot.com/legault/
> >> >
> >> >
> >> >
> >> >"Lawrence" wrote:
> >> >
> >> >> Hello All,
> >> >>
> >> >> I have a list of email addresses in a spreadsheet,
> and
> >> am
> >> >> trying to write a macro to copy each email address
> into
> >> a
> >> >> distribution list in outlook. However, I'm having
> >> trouble
> >> >> trying to get VB to switch over to excel to pick up
> the
> >> >> cells.
> >> >> I guess this is quite a trivial one, but I can't get
> it
> >> to
> >> >> work!
> >> >>
> >> >> Cheers
> >> >>
> >> >> Lawrence
> >> >>
> >> >.
> >> >
> >>
> >.
> >
>