emailing worksheets without formulas

[gtrplayer5150]

I'm using the following code to copy 2 sheets of a workbook and email them. Everything works fine but the file that it emails is 3M and it tries to run the code in thisworkbook. I just want to send a snapshot of the two sheets so the recipient can print them out. I've tried using the PastSpecial function but can't get it to work. Any ideas?

thanks

Private Sub cmdSend_Click()

Dim wb As Workbook
    Dim strdate As String
    strdate = Format(Now, "mm-dd-yy")
    Application.ScreenUpdating = False
    Sheets(Array("8d supplier cover sheet", "#5035e")).Copy
    Set wb = ActiveWorkbook
    With wb
        .SaveAs "Scar Report - " & Sheets("8d supplier cover sheet").Range("b5").Value _
                    & ".xls"
              
        .SendMail Sheets("8d supplier cover sheet").Range("j1").Value, _
                     "Scar Number: " & Sheets("8d supplier cover sheet").Range("b5").Value
                     
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With
    Application.ScreenUpdating = True

 

[Zack Barresse]

What have you tried exactly? Btw, any mass-PasteSpecial method will fail if cell A1 is merged, so I hope this is not the case.

 

[gtrplayer5150]

I've put this code after the .copy command. But I do have cell A1 merged so that would explain a lot of things.

Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

 

[Zack Barresse]

Yes, it will fail (as you've seen). Unmerge A1 and it will work just fine.

 

[gtrplayer5150]

Ok, so I unmerge cell A1 and I run this code and it emails the worksheets but when I open the file it tries to run the code in thisworkbook. I then go back to my original file and all of the formulas are wiped out. Luckily I have a backup. Any ideas?

thanks

    Dim wb As Workbook
    Dim strdate As String
    strdate = Format(Now, "mm-dd-yy")
    Application.ScreenUpdating = False
    Sheets(Array("8d supplier cover sheet", "#5035e")).Copy
    Worksheets.Select
    Cells.Copy
    Cells.PasteSpecial xlPasteValues
    Sheets(1).Select
    Worksheets(1).Select
    Application.CutCopyMode = False

    Set wb = ActiveWorkbook
    With wb
        .SaveAs "Scar Report - " & Sheets("8d supplier cover sheet").Range("b5").Value _
                    & ".xls"
              
        .SendMail Sheets("8d supplier cover sheet").Range("j1").Value, _
                     "Scar Number: " & Sheets("8d supplier cover sheet").Range("b5").Value
                     
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With
    Application.ScreenUpdating = True

 

[Zack Barresse]

Yes, because you did not explicitly reference which workbook to copy/pastespecial/values to, so it uses the workbook before it's copied. Try changing it around ..

    Dim wb As Workbook
    Dim strdate As String, strPath as String
    strdate = Format(Now, "mm-dd-yy")
    Application.ScreenUpdating = False
    Sheets(Array("8d supplier cover sheet", "#5035e")).Copy
    Set wb = ActiveWorkbook
    wb.Sheets(1).Cells.Copy wb.Sheets(1).Cells.PasteSpecial(xlPasteValues)
    Application.CutCopyMode = False

    With wb
        .SaveAs "Scar Report - " & Sheets("8d supplier cover sheet").Range("b5").Value & ".xls"
        .ChangeFileAccess xlReadOnly
        .SendMail Sheets("8d supplier cover sheet").Range("j1").Value, _
                     "Scar Number: " & Sheets("8d supplier cover sheet").Range("b5").Value
        strPath = .FullName
        .Close False
        Kill .strPath
    End With
    Application.ScreenUpdating = True

 

[gtrplayer5150]

Well I finally got the sheets to send and save correctly but and error occurs at:

Kill .strPath

error:
Run-Time Error 424 "Object required"

thanks a lot for taking the time to help me with this!!!

 

[Andrew Hardscape]

Why not simply click on the e-mail button.

Then select "send the current sheet as the message body"


works for me!

 

[gtrplayer5150]

Nevermind, firefytr, I retyped that line in and now it works great! Thanks again for your help!!!!!!