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
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
No comments:
Post a Comment