Problem sending Word document through Outlook with VBA

[gahill]

Hello:

The code below works and will send my document to all contacts in the contacts folder.

What I'm really trying to do is send the document to only those people in my distribution list.

The distribution list is called marketing.

Public Sub SendDocToEmailList()
Dim olapp
Dim mynamespace
Dim mycontactlist
Dim mycontactsentries
Dim messagesubject
Dim newmessage
Dim i As Integer

Set olapp = CreateObject("Outlook.Application")
Set mynamespace = olapp.getnamespace("Mapi")
Set mycontactlist = mynamespace.addresslists("Contacts")
Set mycontactsentries = mycontactlist.addressentries

messagesubject = InputBox("Message subject (Blank to cancel)?")
If messagesubject = "" Then Exit Sub

For i = 1 To mycontactsentries.Count
Set newmessage = olapp.createitem(olmailitem)
newmessage.Subject = messagesubject
newmessage.body = ActiveDocument.Content.Text
newmessage.to = mycontactsentries(1).Address
newmessage.send
Set newmessage = Nothing
Next i

MsgBox "Your messages have been sent."
Set olapp = Nothing
Set mynamespace = Nothing
Set mycontactslist = Nothing
Set mycontactsentries = Nothing
End Sub


Thank you
Gary W. Hill

 

[SkipVought]

Hi,

Just a guess -- recipients are usually assigned by an array

newmessage.to = [b]Array(mycontactsentries(1).Address)[/b]

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TonyJollans]

Hi Gary,

What distribution list? And how does it work anyway?

Your loop goes round once for each address entry in "Contacts", each time setting the "to" address to entry number 1. Am I mssing something?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

Was I asleep, Tony?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TonyJollans]

LOL!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[gahill]

Tony:
I don't understand your response.
You create a distribution list in outlook.
I stated in my post the distribution name is MARKETING.
Manually, if you want to send mail to only the marketing group it shows up in your contact list and you click MARKETING.
Then the E-mail goes to only that group not everyone in the contacts folder.
Thanks
Gary

 

[TonyJollans]

Hi Gary,

My apologies - I missed that in your post but you are not referencing that list in your code.

Have you tried using:

newmessage.to = mycontactsentries("Marketing").Address

You won't need the loop of course (or you'll send the e-mail lots of times) - and the mycontactsentries variable becomes redundant.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[