I currently have the following code that is customized to take parts of a sheet and paste it in a new sheet and then attach that sheet to outlook. The problem with this is that the user cannot see the sheet until they open the attachment from outlook. so basically if there are changes to be made the user will have to open the sheet, edit and resave and reattach. what I am trying to do it to insert another button onto the main sheet that will allow the user to open the sheet and not attach it to outlook. Also, I would like to have the file name "YTD Production" Any ideas on how I could edit the following code to do this. The code below is mainly bits and pieces of the actual code but hopefully the current concept makes sense. if not i can clarify, Thanks so much for your help with this.


Private Sub PrintSheet()

    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object

    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("B:B, C:C,D:D, G:G,H:H, I:I, K:K,R:R, V:V,AB:AB, X:X,AC:AC, AD:AD").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
        End With
        Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("D2").Select
    Selection.Cut
    Range("A1").Select
    ActiveSheet.Paste
    
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "2012 PRODUCTION REPORT"
    Range("C2:C5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    
    With ActiveSheet.PageSetup
     .Zoom = False
     .FitToPagesWide = 1
     .FitToPagesTall = 1000
End With
    
    wb.Sheets("Baird Askew").Shapes("Picture 1").Copy
    Dest.Sheets(1).Paste Range("A1")


    TempFilePath = Environ$("temp") & "\"
    TempFileName = "YTD Production Report"
    
    If Val(Application.Version) < 12 Then
        
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If

    Set OutApp = CreateObject("Excel.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        
    End With

    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub