auto email...no links

[Davers]

I have some code that takes the active sheet and sends it as an attachment in Outlook. There are quite a few formulae in the worksheet, so I copy the range and paste values...However, that attachment still has the links to the original Workbook. Is there any way I can sever the links before it gets mailed out??? Here is the code below...


Sub Mail_ActiveSheet()
Dim strDate As String
' This sends the active sheet in the book
ActiveSheet.Copy
strDate = Format(Date, "mm-dd-yy")
' This names the sheet
ActiveWorkbook.SaveAs "One Sheet of " & ThisWorkbook.Name _
& " " & strDate & ".xls"

Range("B1:X79").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
' This is your mail recipient, then the subject line
ActiveWorkbook.SendMail "me@work.com", _
"BAC Totals Report for "
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

Thanks,

Dave M.

 

[Andrew Poulsom]

Manually copy the sheet to a new workbook and do the Copy|Paste Special Values bit. Then choose Insert|Name|Define and examine the Names for any that refer to your original workbook. You will need to delete these in your code to sever the links before mailing the workbook.

Or you can copy the cells instead of the sheet, like this:

Sub Test()
    ActiveSheet.Cells.Copy
    Workbooks.Add
    ActiveSheet.Paste
    ActiveSheet.Cells.Copy
    ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

[/code]