How do I attach specific sheets as a csv to an email?

Question

I'm trying to attach three sheets to an email to be sent to a certain email address with a certain subject and content.

I currently attach each sheet in the workbook to an email each.

The two problems I'm looking to solve -

• It currently cycles through all sheets, I want to attach sheets labeled "Account", "Subscription", "Users" so I can have another sheet for instructions.
• Can I get attach all three to a single email? My research so far has come up blank.

I tried using something like the below, but that created errors in other areas that I don't know.

For Each ws In Sheets(Array("Account", "Subscription", "Users"))

Sub COMEON()
    Dim onePublishObject As PublishObject
    Dim oneSheet As Worksheet
    Dim scriptingObject As Object
    Dim outlookApplication As Object
    Dim outlookMail As Object
    Dim htmlBody As String
    Dim htmlFile As String
    Dim textStream, fil As String
    Dim dummy As Workbook
    Dim var As String
  
    var = Range("A1").Value
    Today = Format(Now(), "dd-mm-yyyy")

    Set dummy = ActiveWorkbook
    Set scriptingObject = CreateObject("Scripting.FileSystemObject")
    Set outlookApplication = CreateObject("Outlook.Application")
    For Each oneSheet In ActiveWorkbook.Worksheets

        Dim StrBody As String
        StrBody = " THIS IS A TEST" & " " & UCase(oneSheet.Name) & " " & "XYZ," & vbNewLine & _
          vbNewLine & _
          "Please FIND ATTACHED <B>'XYZ REPORT'<B>"

        Application.DisplayAlerts = False
        Sheets(oneSheet.Name).Copy
        ActiveWorkbook.SaveAs dummy.Path & "\" & oneSheet.Name & ".csv"
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
        Set outlookMail = outlookApplication.CreateItem(0)
        With outlookMail
            .To = "XXXXX@XXXXX.com"
            .htmlBody = StrBody & htmlBody
            .attachments.Add dummy.Path & "\" & oneSheet.Name & ".csv"
            .Display
            .Subject = var & " - " & UCase(oneSheet.Name) & " CSV " & "(" & Today & ")"
        End With
    Next oneSheet
End Sub

Answer 1

Should be close:

Sub COMEON()
    Dim oneSheet As Worksheet
    Dim scriptingObject As Object
    Dim outlookApplication As Object
    Dim outlookMail As Object
    Dim htmlBody As String
    Dim dummy As Workbook
    Dim var As String
    Dim StrBody As String, arrSheets, Today
    
    var = Range("A1").Value
    Today = Format(Now(), "dd-mm-yyyy")
    
    Set dummy = ActiveWorkbook
    
    Set outlookApplication = CreateObject("Outlook.Application")
    Set outlookMail = outlookApplication.CreateItem(0)
    With outlookMail
        .To = "XXXXX@XXXXX.com"
        .bodyformat = 1 'HTML
        .Subject = var & " - CSV " & "(" & Today & ")"
        .Display
    End With
    
    StrBody = "THIS IS A TEST<br><br>Files: <ul>"
    arrSheets = Array("Account", "Subscription", "Users")
    For Each oneSheet In dummy.Worksheets
        If Not IsError(Application.Match(oneSheet.Name, arrSheets, 0)) Then
            StrBody = StrBody & "<li>" & oneSheet.Name & "</li>"
            Application.DisplayAlerts = False
            Sheets(oneSheet.Name).Copy
            ActiveWorkbook.SaveAs dummy.Path & "\" & oneSheet.Name & ".csv"
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
            'add attachment
            outlookMail.attachments.Add dummy.Path & "\" & oneSheet.Name & ".csv"
        End If 'want this sheet
    Next oneSheet
    
    With outlookMail
        .htmlBody = StrBody & "</ul>" & .htmlBody
    End With
    
End Sub

Basically move stuff out of the loop that doesn't need to be there.