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

Question

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

Navigation

#1 by (2 votes)

2

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

email excel vba

asked by anonymous 26.05.2015 / 23:16

1 answer

Competition WebServices ASMX with Ajax Wildfly - "Netty 'connector not found in main configuration file"

score 2 · Accepted Answer

Ideal would be to save in a new file the worksheet you want to send and pass the complete path as a parameter to this function, following the generic example I made as a template:

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 have helped!