Emailing Worksheets(Again) (2000)

Topic

New Reply

I know this is a much explored topic, but in the code below most everything works fine (courtesy of Legare, for the most part). The only thing that does not seem to work is when the emailed sheet is opened, it asks for the sheet which contains the information to which it is linked. Is there any way to disable the link update?

I tried the .range.pastespecial approach through two cycles to get the values and the formats for each run but the column spacing comes out different.

Here is what I am running:

Sub MailActiveSheet()
Dim strPath As String
Dim oBook As Workbook
Dim oSheet As Worksheet
resp = MsgBox("This will email the active sheet (the one you are looking at) as _
      an attachment.  Proceed?", vbOKCancel, "Email the active sheet?")
If resp = vbCancel Then End
Application.ScreenUpdating = False
strSheet = ThisWorkbook.ActiveSheet.Name
Application.SheetsInNewWorkbook = 1
strPath = ActiveWorkbook.Path
    If Right(strPath, 1)  "" Then
        strPath = strPath & ""
    End If
    Application.ScreenUpdating = False
    Set oSheet = ActiveSheet
    oSheet.Cells.Copy
    Set oBook = Workbooks.Add
    oBook.Worksheets(1).Name = strSheet
    oBook.Activate
    oBook.Worksheets(1).Paste Destination:=oBook.Worksheets(1).Range("A1")
    oBook.UpdateRemoteReferences = False
    Application.DisplayAlerts = False
    oBook.SaveAs Filename:=strPath & strSheet
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
    ActiveWorkbook.SendMail Recipients:=""
    ActiveWorkbook.Close savechanges = False
    Application.SheetsInNewWorkbook = 3
End Sub

Reply | Quote

Replies

Mike,

See if the following will work.

    Sub MailActiveSheet()
    Dim strPath As String
        resp = MsgBox("This will email the active sheet", vbOKCancel)
        If resp = vbCancel Then End
        strPath = ActiveWorkbook.Path
            If Right(strPath, 1)  "" Then
            strPath = strPath & ""
        End If
        ActiveSheet.Copy
        With Cells
            .Copy
            .PasteSpecial xlPasteValues
        End With
        [A1].Select
        ActiveWorkbook.SaveAs FileName:=strPath & ActiveSheet.Name
        ActiveWorkbook.SendMail Recipients:=""
    End Sub

I presume you require formulas replaced by values. However if the sheet you are mailing originally only had internal sheet references (i.e. the formulas did not reference any ranges outside of the sheet) , there may be no need to copy and paste.

Andrew C

Reply | Quote

Andrew that worked great! Nice and clean too.

Thanks,

Reply | Quote

HI!

This macro has been a big hit here… my users are very happy with it. However, I have one problem. The Workbook I put this in has a bazillion pages and over 2500 named ranges. The macro won’t take the named ranges for the sheet with it. (Neither will a simple copy and paste do so.)

So… any ideas on how to take the named ranges with the copy/paste?

Thanks! JC

Reply | Quote