VBA Send Email To Outlook Distribution List

[Lewzerrrr]

Hey,

Can send emails fine through VBA but can't work out how to send to an Outlook Distribution List e.g. "Team" = AAA <AAA@AAA.com>; BBB <BBB@AAA.com>; CCC <CCC@AAA.com>; DDD <DDD@AAA.com>

I can input the emails into VBA which is fine but would have to add to every macro if I need to add in extra emails when if I was able to do "Team" and update it in Outlook it should catch it?

However when I do..

.to = "Team" it would just place the word team lol.

Any help is appreciated, if any misunderstanding let me know

 

[Worf]

Hey

' Outlook 2007 module
Sub Email()
Dim list As DistListItem, f As Folder, oItem As MailItem
Set f = Session.GetDefaultFolder(olFolderContacts)
f.ShowAsOutlookAB = True
Set list = f.Items(5)                                   ' the group
Set oItem = CreateItem(olMailItem)
With oItem
    .To = list
    .Body = "Today is Sunday."
    .Display
End With
End Sub

 

[Lewzerrrr]

Hey

' Outlook 2007 module
Sub Email()
Dim list As DistListItem, f As Folder, oItem As MailItem
Set f = Session.GetDefaultFolder(olFolderContacts)
f.ShowAsOutlookAB = True
Set list = f.Items(5)                                   ' the group
Set oItem = CreateItem(olMailItem)
With oItem
    .To = list
    .Body = "Today is Sunday."
    .Display
End With
End Sub

Thanks Worf, this seems to be struggling if I add more than one distribution list. What I tried to do was..

with oItem
.to = list & "; " & list1
.cc = list2

but seems to be a bit random as to whether it expands the group or underlines it with green so I can manually drag and expand it.

Is it possible to swap out the Set list = f.Items(5) for the actual name of the distribution list as other users won't necessarily be that index?

 

[Worf]

Something like this:

' Outlook 2007 module
Sub Email()
Dim list1 As DistListItem, list2 As DistListItem, f As Folder, oItem As MailItem
Set f = Session.GetDefaultFolder(olFolderContacts)
f.ShowAsOutlookAB = True
Set list1 = f.Items("MyList")
Set list2 = f.Items("List2")
Set oItem = CreateItem(olMailItem)
With oItem
    .To = list1 & ";" & list2
    .Recipients.ResolveAll
    .Body = "Today is Tuesday."
    .Display
End With
End Sub

 

[Lewzerrrr]

Something like this:

' Outlook 2007 module
Sub Email()
Dim list1 As DistListItem, list2 As DistListItem, f As Folder, oItem As MailItem
Set f = Session.GetDefaultFolder(olFolderContacts)
f.ShowAsOutlookAB = True
Set list1 = f.Items("MyList")
Set list2 = f.Items("List2")
Set oItem = CreateItem(olMailItem)
With oItem
    .To = list1 & ";" & list2
    .Recipients.ResolveAll
    .Body = "Today is Tuesday."
    .Display
End With
End Sub

Hi Worf, before I could get this to work, I haven't fully coded this in yet but now i'm trying and excel isn't recognising distlistitem as an object? Can you advice?

I tried dim list1 as outlook.distlistitem and dim list1 as distlistitem but neither work?

Edit - my Outlook reference library turned off lol solved

Thanks,