Hi, I have adapted some code posted on here in order to e-mail each
sheet in a workbook to the e-mail addresses entered into cell A1 on
each sheet. What I would like to do is to break all the links in the
new workbooks before they are sent. I would also like to insert a
message box asking to confirm the action before it is carried out (ie -
"e-mail separate sheets?" Yes/No/Cancel)
Any help would be very much appreciated - Thanks!
The existing code I have is as follows:
Sub email_worksheets2()
Dim sh As Worksheet
Dim wb As Workbook
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Cost Centre Reporting - " & sh.Name & ".xls"
.SendMail ActiveSheet.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
End Sub