CODEWITHSUNDEEP
vbaexceloutlook
Moses
Moses

Reputation: 9183

How to access contact groups in Excel VBA?

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

Upvotes: 9

Views: 12741

Answers (3)

Jim
Jim

Reputation: 23

Expanding on the accepted answer to simply use the name, ensure that the Contact Group name is not ambiguous.

For example, if I have two groups called "My List" and "My List 2". When I try to manually send an email and only type "My List" in the 'To' box, Outlook shows a pop-up asking which list to resolve. This works kind of like and autofill suggestion in Excel. If instead I typed "My List 2", Outlook will know exactly which list I want.

Similarly, Outlook gets confused when trying the same things via VBA, and the error message is not very clear: "Outlook does not recognize one or more names".

The simplest workaround I'm aware of is simple to change the name of "My List" to "My List 1" or anything else completely unique, where no other list shares that exact base name.

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27488

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.

Upvotes: 4

Drew Chapin
Drew Chapin

Reputation: 8009

In order to have the recipient's email addresses or names resolved (so they don't display just 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

Upvotes: 0

Related Questions