Hi ell7

Test this one for me and post back if this is what you want

Sub Email_worksheets_Break_links_Test()
Dim sh As Worksheet
Dim wb As Workbook
Dim WorkbookLinks As Variant
Dim i As Long
Dim Answer

Answer = MsgBox("Do you want to mail all sheets that have a mail address in A1 ?", _
vbYesNo, "Ron's code test!")

If Answer = vbYes Then

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook

WorkbookLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not IsEmpty(WorkbookLinks) Then
For i = 1 To UBound(WorkbookLinks)
wb.BreakLink _
Name:=WorkbookLinks(i), _
Type:=xlLinkTypeExcelLinks
Next i
Else
' No Links to other workbooks"
End If

With wb
.SaveAs "Cost Centre Reporting - " & sh.Name & ".xls"
.SendMail wb.Sheets(1).Range("a1").Value, _
"Please find attached your detailed " & sh.Name & " cost centre report"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True

Else
MsgBox "You not want to run the macro"
End If

End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"ell7" wrote in message ups.com...
Hi Ron - thanks for your quick reply. I only need one message box for
when I start the macro. And, yes, I want to break the formula links to
external references. I have a lot of references to files that the
recipients of the worksheets do not have access to, so I would like to
break these links. I would prefer to do this rather than simply to
convert the formulae to values as I would like to retain the internal
formulae within the sheet itself.
Thanks in advance for your help.