Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Emailing Worksheets(Again) (2000)

    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:

    <pre>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
    </pre>


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Emailing Worksheets(Again) (2000)

    Mike,

    See if the following will work.<pre> 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</pre>

    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

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Emailing Worksheets(Again) (2000)

    Andrew that worked great! Nice and clean too.

    Thanks,

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Ames, Iowa, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Emailing Worksheets(Again) (2000)

    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? <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    Thanks! JC<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •