Excel VBA: Suppress "Check Names" box when sending mails RRS feed

  • Question

  • Hi Forum,

    I am using the following code to send a file to an Outlook contact group I created myself which has a unique name, e.g. "List Europe":

    Public Function Sendout(strRecipients As String, strSubject As String, strPDF As String) As Boolean
        Dim wbCC    As Workbook
        Dim wsMain  As Worksheet
        Dim wsSO    As Worksheet
        Set wbCC = Workbooks("Control-Center.xlsm")
        Set wsMain = wbCC.Worksheets("Main")
        Set wsSO = wbCC.Worksheets("Sendout")
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
        End With
        If strPDF <> "" Then
            With Selection
                ActiveWorkbook.EnvelopeVisible = True
                With ActiveSheet.MailEnvelope
                    .Item.To = strRecipients
                    .Item.Subject = strSubject & strDate
                    .Item.Attachments.Add strPDF                
                End With
                ActiveWorkbook.EnvelopeVisible = False
            End With
            With Application
                .DisplayAlerts = True
                .ScreenUpdating = True
            End With
            Sendout = True
            Else:   Sendout = False
        End If
    End Function

    When running my code, the "Check names" box pops up and offers more than just the list I handed over to my function, but other lists containing the words "List" and/or "Europe", e.g. "Europe second list".

    My question is, how can I suppress this pop-up and force the code to use just exactly what I handed over?


    Thursday, February 15, 2018 7:20 AM

All replies