How to access contact groups in Excel VBA?

Question

I am creating an Excel add-in that sends the active workbook as an attachment to an Outlook email template to a specific contact group.

I got the first two parts for working with the code below, but I'm not sure how to set the .TO field to the contact group.

 Public Sub Mail_Reports() Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With On Error Resume Next Set OutApp = CreateObject("Outlook.Application") 'Set this line to the path and file name of your template Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\moses\AppData\Roaming\Microsoft\Templates\test.oft") On Error Resume Next With OutMail '.TO field should be set to the contact group .BCC = "" .Attachments.Add ActiveWorkbook.FullName .HTMLBody = Replace(OutMail.HTMLBody, strOldPeriod, strNewPeriod) .Subject = Replace(OutMail.Subject, strOldPeriod, strNewPeriod) 'To display the email leave as is; to send the Email, change to .Send .Display 'or Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub 

Answer 1

Just use the name of the contact group (formerly called "mailing lists"). I just tried it, as suggested on the Ron de Bruin website , and it works.

Answer 2

To resolve email addresses or recipient names (so they don’t only display plain text), you can do the following.

 With OutMail '.TO field should be set to the contact group .BCC = "" .Attachments.Add ActiveWorkbook.FullName .HTMLBody = Replace(OutMail.HTMLBody, strOldPeriod, strNewPeriod) .Subject = Replace(OutMail.Subject, strOldPeriod, strNewPeriod) 'To display the email leave as is; to send the Email, change to .Send .Display 'or Send If Not .Recipients.ResolveAll Then For Each Recipient In .Recipients If Not Recipient.Resolved Then MsgBox Recipient.Name & " could not be resolved" End If Next End If End With