Copying email addresses into a distribution list

[Lawrence]

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

 

[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.

 

[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

 

[Guest]

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

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