Sending Sheet as an Attachment Outlook

%3CLINGO-SUB%20id%3D%22lingo-sub-1423526%22%20slang%3D%22en-US%22%3ESending%20Sheet%20as%20an%20Attachment%20Outlook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423526%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20to%20see%20if%20it%20is%20possible%20to%20send%20a%20Sheet%20as%20an%20attachment%20in%20Outlook%20at%20the%20click%20of%20a%20button.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20already%20created%20the%20button%20and%20coded%20it%20to%20insert%20a%20screenshot%2C%20however%2C%20an%20attachment%20would%20be%20preferred.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sheet%20I%20wish%20to%20send%20is%20called%20'Progress'.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1423526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424390%22%20slang%3D%22en-US%22%3ERe%3A%20Sending%20Sheet%20as%20an%20Attachment%20Outlook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F674965%22%20target%3D%22_blank%22%3E%40lewisreiddd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this%20and%20tweak%20it%20as%20per%20your%20requirement...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20AttachSheetToOutlook()%0ADim%20SheetToAttach%20%20%20As%20Worksheet%0ADim%20strFileToAttach%20As%20String%0ADim%20olApp%20%20%20%20%20%20%20%20%20%20%20As%20Object%0A%0ASet%20SheetToAttach%20%3D%20Worksheets(%22Progress%22)%0AstrFileToAttach%20%3D%20Environ(%22UserProfile%22)%20%26amp%3B%20%22%5CDesktop%5C%22%20%26amp%3B%20SheetToAttach.Name%20%26amp%3B%20%22.xlsx%22%0A%0ASheetToAttach.Copy%0AActiveWorkbook.SaveAs%20strFileToAttach%2C%2051%0AActiveWorkbook.Close%0A%0ASet%20olApp%20%3D%20CreateObject(%22Outlook.Application%22)%0A%0AWith%20olApp.createitem(0)%0A%20%20%20%20.To%20%3D%20%22someone%40someone.com%22%0A%20%20%20%20.Subject%20%3D%20%22Progress%20Report%22%0A%20%20%20%20.Attachments.Add%20strFileToAttach%0A%20%20%20%20.Display%0AEnd%20With%0AKill%20strFileToAttach%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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.

 

1 Reply
Highlighted

@lewisreiddd 

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