Macro to send an email to distribution list

[teafan]

Hi all,

I have to send about fifty reports out every morning and want to automate this going forwards. Previously, i have manually amened the vba to contain the distribution list but this isnt really an option in this company as numerous people produce the reports with varying levels of ability and their are usually daily changes to th disto. What i'd like to do is write a code to use one of the distribution list already saved in outlook:

In the contact tab on outlook there is the My Contacts section in the top left which further contains a list of four contact "lists" (specifically, "Contacts in Mailbox - opscentre") which all contain separate distribution lists; for example - Inside the "Contacts in Mailbox - opscentre" list is a distribution list named Example1

How could i approach using this distribution via a macro?

Many many thanks in advance for any help in solving this!

 

[daverunt]

Hi,

You can use the name of the distribution list in the .To field of the macro.

Have a play with this basic macro.
I have entered Example1 as your .To address

Sub SendEmail()
Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String
EmailSubject = "Distribution Test"
 
SendTo = "Example1" ' Use name of distribution list 
 
EmailBody = "Distribution List Test mail"
 
Set App = CreateObject("Outlook.Application")
Set Itm = App.createitem(0)
 
With Itm
.Subject = EmailSubject
.To = SendTo
.Body = EmailBody
'AttachmentResponse = MsgBox("Click OK to Attach a file or Cancel for no attachment", vbOKCancel, "Attachment to Send?")
'If AttachmentResponse = vbOK Then
AttachFileName = Application.GetOpenFilename("Files (*.**)," & _
"*.**", 1, "Select File", "Open", False)
.Attachments.Add (AttachFileName)
'End If
'.Display 
.Send
End With
 
Set App = Nothing
Set Itm = Nothing
End Sub

Note: the .To addresses from the distribution list are not added until the mail is sent. So commenting out .send and uncommenting display to view the mail will give you a .To address of "example1" until you hit send. (Check mail in Outbox)