Sub CreateEMAIL()
Dim xOtl As Object
Dim xOtlMail As Object
Dim xStrBody As String
    xStrBody = "<h2>Привет</h2>" _
               & "<h3>новые данные" & "</h3>" _
               & Chr(10) & TableToHtml(Range("A1:D4")) & Chr(10) _
               & "<br>пока</br>"
    On Error Resume Next
    Set xOtl = CreateObject("Outlook.Application")
    Set xOtlMail = xOtl.CreateItem(0)
    With xOtlMail
    .To = "Email Address"
    .CC = "Email Address"
    .BCC = "Email Address"
    .Subject = "Subject line"
    .HTMLBody = .HTMLBody & xStrBody
    .Display
    End With
    Set xOtl = Nothing
    Set xOtlMail = Nothing
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Лист1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Лист2").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Лист3").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("ИТОГИ").Select
End Sub

let
    f="LOCATION_ОБЩИЙ", /* имя умной таблицы и название столбца откуда брать инфу(без кавычек)*/
    n=1,
    m = Text.From ( Excel.CurrentWorkbook(){[Name=f]}[Content]{n}[ЛИСТ]),
    Источник = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name=f]}[Content]{n}[LOCATION_ОБЩИЙ]), null, true),
    #"Лист" = Источник{[Item=m,Kind="Sheet"]}[Data]
in
    #"Лист"

Источник = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="LOCATION_ОБЩИЙ"]}[Content]{0}[LOCATION_ОБЩИЙ]), null, true),
    #"Лист" = Источник{[Name="10"]}[Data],
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Лист",{"Column1", "Column2", "Column4", "Column5", "Column11"}),

Sub Filter_po_vudeleniy() ' фильтр по выделению
    Dim filterRange As Range, filterValues() As Variant, cl As Range, i As Integer, rng As Range
    Set filterRange = Range("A1")
    Set rng = Selection '
    If rng.Cells.Count > 1 Then Set rng = rng.SpecialCells(xlCellTypeVisible)
    ReDim filterValues(1 To rng.Count)
    i = 0
    For Each cl In rng
        i = i + 1
        filterValues(i) = cl.Text
    Next cl
    Dim ColNumber As Integer
    ColNumber = rng.Column - filterRange.Column + 1 ' Val(InputBox("COLUMN Number"))
    filterRange.autofilter Field:=ColNumber, Criteria1:=filterValues, Operator:=xlFilterValues
End Sub