May 28 2020 05:09 AM
Hi there,
I'm looking to see if it is possible to send a Sheet as an attachment in Outlook at the click of a button.
I have already created the button and coded it to insert a screenshot, however, an attachment would be preferred.
The sheet I wish to send is called 'Progress'.
Thanks in advance.
May 28 2020 09:43 AM
You may try something like this and tweak it as per your requirement...
Sub AttachSheetToOutlook()
Dim SheetToAttach As Worksheet
Dim strFileToAttach As String
Dim olApp As Object
Set SheetToAttach = Worksheets("Progress")
strFileToAttach = Environ("UserProfile") & "\Desktop\" & SheetToAttach.Name & ".xlsx"
SheetToAttach.Copy
ActiveWorkbook.SaveAs strFileToAttach, 51
ActiveWorkbook.Close
Set olApp = CreateObject("Outlook.Application")
With olApp.createitem(0)
.To = "someone@someone.com"
.Subject = "Progress Report"
.Attachments.Add strFileToAttach
.Display
End With
Kill strFileToAttach
End Sub