Guru do Excel e Power BI

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
'Working in Excel 2002-2016
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range
    Dim destinatario As Range
    Dim assunto As Range


    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Fill in the Worksheet/range you want to mail
    'Note: if you use one cell it will send the whole worksheet
    Set Sendrng = Worksheets("Plan1").Range("B1:Q19")

    'Remember the activesheet
    Set AWorksheet = ActiveSheet
    Set destinatario = Worksheets("E-mail").Range("L11")
    Set assunto = Worksheets("E-mail").Range("L13")

    With Sendrng

        ' Select the worksheet with the range you want to send
        .Parent.Select

        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell

        'Select the range you want to mail
        .Select

        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope
        
      
           With .Item
                .To = destinatario.Value
                .Subject = assunto.Value
                .display

            End With

        End With

        'select the original ActiveCell
        rng.Select
    End With

    'Activate the sheet that was active before you run the macro
    AWorksheet.Select

StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
  
    ActiveWorkbook.EnvelopeVisible = False

End Sub

Sub Tente()
    Dim ans As VbMsgBoxResult
    
    ans = MsgBox("Deseja enviar o Email?", vbQuestion + vbYesNoCancel, "Enviar Email?")
    
    If ans = vbYes Then
           Call Send_Range_Or_Whole_Worksheet_with_MailEnvelope
           'Ou use essa lógica para disparar a opção-> .send
    Else
        Exit Sub
    End If
    
End Sub