Send Range as attachment using Outlook and Excel VBA

If you want to export a range to new workbook and send it via attachment to client. Try this macro -


Sub send_email_via_outlook()

' Tools - Refrence - Microsoft Outlook


Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim flname As String
Dim wkb As Workbook

flname = VBA.Environ("temp") & "\" & VBA.Format(VBA.Now, "dd_mm_yyy_hh_mm_ss") & ".xlsx"
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

Set wkb = Workbooks.Add ' add new workbook
' change sheet name and range here
ThisWorkbook.Sheets(1).Range("A1:g25").Copy Destination:=wkb.Sheets(1).Range("a1")
wkb.SaveAs flname ' save workbook with temp name
wkb.Close 'close it

With olMail
    .To = "koul.ashish@gmail.com"
    .Subject = "Hello"
    '  vbNewLine  is used to insert a row
    .Body = "Dear Ashish" & vbNewLine & "Please find the attachment" & vbNewLine & vbNewLine & vbNewLine & "Regards" & vbNewLine & "Ashish Koul"
    .Attachments.Add flname  ' attach the workbook
    .Display ' or use .send
End With

Set wkb = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub




Download Working File