VB to send email via outlook to a distrobution list

[Paul Lambson]

I am trying to send an email from excel to a distrobution list in
outlook. Any nifty code out there?

I found this on a group but can't get it to work.

Const olFolderContacts = 10
sDistName = "Friends" 'set the distribution list name here

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")

Set colContacts = objNamespace.GetDefaultFolder
(olFolderContacts).Items
intCount = colContacts.Count

For i = 1 To intCount
If TypeName(colContacts.Item(i)) = "DistListItem" Then
Set objDistList = colContacts.Item(i)
sEmails = "";
If objDistList.DLName = sDistName Then
For j = 1 To objDistList.MemberCount
sEmails = sEmails & ";" & objDistList.GetMember
(j).Address
Next

'send the mail using the emails u got! ;-) enjoy
End If
End If
Next

Any help will be appreciated.

Thanks,
paul

 

[Ron de Bruin]

Hi Paul

Use

..Recipients.Add "GroupName"

See more tips here
http://www.rondebruin.nl/mail/tips2.htm

And code examples here
http://www.rondebruin.nl/sendmail.htm

 

[Paul Lambson]

Ron, thanks! the websites you listed are a great resource.

 

[Paul Lambson]

I'm unable to bypass the security feature. I am using this line of
code
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys ("%s"), False
rather than a .send but the sendkeys are not working.

and advise?

 

[Ron de Bruin]

Yep

Text from the page

Note: SendKeys is not always reliable and this will not work on every computer
The S is from Send, if you not use a English version you must change this letter.
This tip is not working for the SendMail examples in the first section on my mail page.

Maybe it is working if you close the VBA editor

See this page for other options
http://www.rondebruin.nl/mail/prevent.htm