Email EXCEL Sheet from Workbook

[Dom.Knight]

Hello,

Is it possible to email a specfic sheet from an excel workbook as a PDF without sending all sheets from the workbook.

I used to think this was possible however Im now only able to share the whole workbook sheets 1-7 as a whole rather than just share 1 sheet.

[davesexcel]

Here is a generic email to attach a worksheet.

It will create a copy of the active sheet and then save it in the temp folder, then create the email with the attached sheet.

Sub EmailOneSheet()
    Dim oApp As Object
    Dim oMail As Object
    Dim wb As Workbook
    fname = Environ("Temp") & "\copySheet.xlsx"

    'Turn off screen updating
    Application.ScreenUpdating = False
    'Make a copy of the active sheet and save it to a temporary file
    ActiveSheet.Copy
    Cells.Copy
    Cells.PasteSpecial xlPasteValues

    Set wb = ActiveWorkbook
    wb.SaveAs fname

    'Create and show the outlook mail item
    Set oMail = CreateObject("Outlook.Application").CreateItem(0)
    With oMail    'Add a recipient
        .To = "Marcus.Small@YourEmail.com.au"
        .Subject = "One Page Activesheet v2"
        .Attachments.Add wb.FullName
        .Display
    End With

    wb.Close False
    Application.ScreenUpdating = True
    Set oMail = Nothing
End Sub