Macro to email spreadsheet
 

Hi -

A couple of years ago I had great help from this discussion group to write
the following macro to automatically email a spreadsheet. It was so
successful I've been asked repeatedly to use it in other spreadsheets. This
code is necessary in order not to truncate text in cells, as most of the
spreadsheets I use it on have a very long "comments" cell.

The problem I face on the most recent application is that the sheet that is
being copied has a Worksheet SelectionChange Macro written into it that
executes when a particular cell is updated. During the copy of the
spreadsheet, because the macro is being copied and that cell is being updated
the macro "dies". Is there any update I can do to this code that will
eliminate the copy of the macro? I don't need it in the emailed file.

Sub Mail_ActiveSheet_techservicerpt()
Dim ws As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

Application.ScreenUpdating = False

ActiveSheet.Unprotect

Set wb1 = ThisWorkbook
wb1.Sheets("Technical Service Report").Copy
Set wb2 = ActiveWorkbook
For Each ws In wb2.Worksheets
wb1.Sheets(ws.Name).Cells.Copy wb2.Sheets(ws.Name).Cells(1)
Next ws
With wb2
.SaveAs "C:\" & Sheets("Technical Service Report").Range("m1").Value
& " Approved.xls"
.SendMail "insert email", Sheets("Technical Service
Report").Range("m1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets("Technical Service Report").Protect
End Sub

Thanks in advance,
Holly

Macro to email spreadsheet
 

You can turn of events in excel using

Application.EnableEvents = False

then set is back to true at the end of the macro

"hnyb1" wrote:

Hi -

A couple of years ago I had great help from this discussion group to write
the following macro to automatically email a spreadsheet. It was so
successful I've been asked repeatedly to use it in other spreadsheets. This
code is necessary in order not to truncate text in cells, as most of the
spreadsheets I use it on have a very long "comments" cell.

The problem I face on the most recent application is that the sheet that is
being copied has a Worksheet SelectionChange Macro written into it that
executes when a particular cell is updated. During the copy of the
spreadsheet, because the macro is being copied and that cell is being updated
the macro "dies". Is there any update I can do to this code that will
eliminate the copy of the macro? I don't need it in the emailed file.

Sub Mail_ActiveSheet_techservicerpt()
Dim ws As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

Application.ScreenUpdating = False

ActiveSheet.Unprotect

Set wb1 = ThisWorkbook
wb1.Sheets("Technical Service Report").Copy
Set wb2 = ActiveWorkbook
For Each ws In wb2.Worksheets
wb1.Sheets(ws.Name).Cells.Copy wb2.Sheets(ws.Name).Cells(1)
Next ws
With wb2
.SaveAs "C:\" & Sheets("Technical Service Report").Range("m1").Value
& " Approved.xls"
.SendMail "insert email", Sheets("Technical Service
Report").Range("m1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets("Technical Service Report").Protect
End Sub

Thanks in advance,
Holly

Macro to email spreadsheet
 

Thanks very much! I should have thought of that.

"Joel" wrote:

You can turn of events in excel using

Application.EnableEvents = False

then set is back to true at the end of the macro

"hnyb1" wrote:

Hi -

A couple of years ago I had great help from this discussion group to write
the following macro to automatically email a spreadsheet. It was so
successful I've been asked repeatedly to use it in other spreadsheets. This
code is necessary in order not to truncate text in cells, as most of the
spreadsheets I use it on have a very long "comments" cell.

The problem I face on the most recent application is that the sheet that is
being copied has a Worksheet SelectionChange Macro written into it that
executes when a particular cell is updated. During the copy of the
spreadsheet, because the macro is being copied and that cell is being updated
the macro "dies". Is there any update I can do to this code that will
eliminate the copy of the macro? I don't need it in the emailed file.

Sub Mail_ActiveSheet_techservicerpt()
Dim ws As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

Application.ScreenUpdating = False

ActiveSheet.Unprotect

Set wb1 = ThisWorkbook
wb1.Sheets("Technical Service Report").Copy
Set wb2 = ActiveWorkbook
For Each ws In wb2.Worksheets
wb1.Sheets(ws.Name).Cells.Copy wb2.Sheets(ws.Name).Cells(1)
Next ws
With wb2
.SaveAs "C:\" & Sheets("Technical Service Report").Range("m1").Value
& " Approved.xls"
.SendMail "insert email", Sheets("Technical Service
Report").Range("m1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets("Technical Service Report").Protect
End Sub

Thanks in advance,
Holly