How to send only a spreadsheet by email in VBA and not the whole file using the code below

Question

Option Compare Database
Option Explicit
Public Const C_Title  As String = "ef3.Cinco"
'Metodos
Public Sub SendMail(obj As MailClass) 'Nome da classe...

    Dim appOutLook As Outlook.Application '"Importando" Outlook
    Set appOutLook = CreateObject("Outlook.Application")

    Dim MailOutLook As Outlook.MailItem 'Propriedades de mensagem
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
        .To = obj.Destinatario 'Destinatários (separados por ';')
        .Subject = obj.Assunto 'Assunto
        .Body = obj.Detalhe 'Corpo do eMail
        .Attachments.Add (obj.Anexo) 'Caminho do anexo
        .Send 'Enviar
    End With
    Set MailOutLook = Nothing

End Sub

Answer 1

Ideal would be to save in a new file the sheet you want to send and pass the full path as parameter to this function, follows generic example I did as a model:

Sub enviaPlanilhaAtiva()

Dim oOutlook As Object
Dim oEmail As Object
Dim wbAtual As Workbook
Dim sNomeArquivo As String
Dim sLocalTemp As String
Application.ScreenUpdating = False

Set oOutlook = CreateObject("Outlook.Application")
Set oEmail = oOutlook.CreateItem(0)
sLocalTemp = "C:\temp\"

' Copia a planilha ativa e salva em local temporário
ActiveSheet.Copy
Set wbAtual = ActiveWorkbook

' Aqui você define qual planilha deve ser gravada
sNomeArquivo = wbAtual.Worksheets(1).Name

On Error Resume Next
Kill sLocalTemp & sNomeArquivo
On Error GoTo 0
wbAtual.SaveAs FileName:=sLocalTemp & sNomeArquivo

With oEmail
    '.To = "[email protected]"
    '.Subject = "Assunto"
    '.body = "Corpo do e-mail"
    .Attachments.Add wbAtual.FullName
    .Display
End With

'Deleta o arquivo temporário
wbAtual.ChangeFileAccess Mode:=xlReadOnly
Kill wbAtual.FullName
wbAtual.Close SaveChanges:=False

Set oEmail = Nothing
Set oOutlook = Nothing
End Sub

I hope I’ve helped!