Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email excel spreadsheet - embedded in email | Excel Discussion (Misc queries) | |||
Sending a Spreadsheet as an Email Attachment vs. Imbedded in Email | Excel Discussion (Misc queries) | |||
How do I create an email macro to auto fill the email? | Excel Discussion (Misc queries) | |||
Transfer Email addresses from spreadsheet to email address book | Excel Discussion (Misc queries) | |||
How do I set up a macro to forward an Excel Spreadsheet via email | Excel Discussion (Misc queries) |