How to access contact groups in Excel VBA?

excel, outlook, vba

Solution

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

Problem

I am building an Excel add-in that sends the active workbook as an attachment in an Outlook email template to a specific Contact Group. I've gotten the first two parts to work with the code below, but I am not sure how to set the `.TO` field to a 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 ```

Original source