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
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.