| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IS THERE A WAY TO CLEAR WORKSHEET EVENTS USING ANOTHER MACRO
Hi,
I have a workbook of several sheets and all sheets have worksheet events. My problem is I do not want them sticked to my attached file when I run my email macro. Is there a possibility to insert a code to my outlook macro will be able to clear the worksheet events of my attached file automatically or any workbook event delete them on exit. Thanks for kindest supports Reha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IS THERE A WAY TO CLEAR WORKSHEET EVENTS USING ANOTHER MACRO
Reha
See Chip Pearson's site for info and code....... Specifically the section on "Deleting all VBA code in a project" About 2/3 of the way down the page. http://www.cpearson.com/excel/vbe.htm Gord Dibben MS Excel MVP On Sat, 9 Dec 2006 02:45:25 +0200, "Reha" wrote: Hi, I have a workbook of several sheets and all sheets have worksheet events. My problem is I do not want them sticked to my attached file when I run my email macro. Is there a possibility to insert a code to my outlook macro will be able to clear the worksheet events of my attached file automatically or any workbook event delete them on exit. Thanks for kindest supports Reha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IS THERE A WAY TO CLEAR WORKSHEET EVENTS USING ANOTHER MACRO
Gord thanks for your reply,
Before writing my mail I checked Chip Pearson' s page and also made a trial in my workbook but I was disapointed cause of the result that my macros were deleted all. What I need is to delete only the event macro on my file attached to the mail after I run my outlook macro and do not want to delete them in my original excel file. The procedure of my outlook macro is pasting a copy of the selected one between several sheets even with the event macro that creates a problem to my customers because of macro warning while opening my mail attachment and asked me always for virus infections. Eventually I need a code to delete or clear the worksheet event in my attachment. Regards Reha "Gord Dibben" <gorddibbATshawDOTca, haber iletisinde sunlari .. . Reha See Chip Pearson's site for info and code....... Specifically the section on "Deleting all VBA code in a project" About 2/3 of the way down the page. http://www.cpearson.com/excel/vbe.htm Gord Dibben MS Excel MVP On Sat, 9 Dec 2006 02:45:25 +0200, "Reha" wrote: Hi, I have a workbook of several sheets and all sheets have worksheet events. My problem is I do not want them sticked to my attached file when I run my email macro. Is there a possibility to insert a code to my outlook macro will be able to clear the worksheet events of my attached file automatically or any workbook event delete them on exit. Thanks for kindest supports Reha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IS THERE A WAY TO CLEAR WORKSHEET EVENTS USING ANOTHER MACRO
Reha,
I am by no means an expert in Outlook, and I put together the following code mostly by trial and error. There may be a better way to go about this, but I think the following code will do what you want. It takes the attached Excel file from a MailItem object, saves it to a temp folder, deletes all the VBA code from the workbook in the temp folder, deletes the original attachment, and re-attaches the modified file. The original copy of the file is not changed, only the attached copy is modified. The code between the lines marked with '<<<< are most relevant to your question. I assume your code has an object variable refering to a running Excel Application. Subsitute that variable's name where "Excel" appears in the code (but not in the variable declarations) that follows: Public Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" ( _ ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Dim OLK As Outlook.Application Const MAX_PATH = 260& Sub AAA() Dim MItem As Outlook.MailItem Dim Attch As Outlook.Attachment Dim WB As Excel.Workbook Dim AttchName As String Dim TempPath As String Dim Pos As Integer Dim PathLen As Long Dim VBComp As Object '''''''''''''''''''''''''''''''''''''''''''''''' ' Get a reference to a running instance of ' Outlook. '''''''''''''''''''''''''''''''''''''''''''''''' On Error Resume Next If OLK Is Nothing Then Set OLK = GetObject(, "Outlook.Application") End If If OLK Is Nothing Then ''''''''''''''''''''''''''''''''''''''''''''''''' ' Outlook isn't running. Get out. '''''''''''''''''''''''''''''''''''''''''''''''' Exit Sub End If On Error GoTo 0 '''''''''''''''''''''''''''''''''''''''''''' ' Get a temp folder name from Windows '''''''''''''''''''''''''''''''''''''''''''' TempPath = String$(MAX_PATH, vbNullChar) PathLen = GetTempPath(MAX_PATH, TempPath) TempPath = Left$(TempPath, PathLen) ''''''''''''''''''''''''' ' Create a new mail item ' and set the recipient. ''''''''''''''''''''''''' Set MItem = OLK.CreateItem(olMailItem) MItem.Recipients.Add " '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ''''''''''''''''''''''''''''''''''''''''''' ' Attach C:\bookone.xls to the mail message. '''''''''''''''''''''''''''''''''''''''''''' Set Attch = MItem.Attachments.Add("C:\bookone.xls", olByValue) ''''''''''''''''''''''''''''''''''''''''''''' ' Make sure there is no file with the same ' name as the attachment in the temp folder. ''''''''''''''''''''''''''''''''''''''''''''' On Error Resume Next Kill TempPath & Attch.Filename On Error GoTo 0 '''''''''''''''''''''''''''''''''''''''''''''' ' store the attachment's file name ' for later use. ''''''''''''''''''''''''''''''''''''''''''''' AttchName = Attch.Filename '''''''''''''''''''''''''''''''''''''''''''' ' Save the attachment as a file in the temp ' folder. '''''''''''''''''''''''''''''''''''''''''''' Attch.SaveAsFile TempPath & Attch.Filename Excel.Application.ScreenUpdating = False '''''''''''''''''''''''''''''''''''''''''''' ' Open the copy of the file that is in the ' temp folder. '''''''''''''''''''''''''''''''''''''''''''' Set WB = Excel.Workbooks.Open(Filename:=TempPath & Attch.Filename) ''''''''''''''''''''''''''''''''''''''''''' ' delete all VBA code ''''''''''''''''''''''''''''''''''''''''''' For Each VBComp In WB.VBProject.VBComponents Select Case VBComp.Type Case 1, 2, 3 With WB.VBProject.VBComponents .Remove .Item(VBComp.Name) End With Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp ''''''''''''''''''''''''''''''''''''''''''' ' Save and close the workbook. ''''''''''''''''''''''''''''''''''''''''''' WB.Close savechanges:=True '''''''''''''''''''''''''''''''''''''''''' ' Delete the original attachment and attach ' the modified file from the temp folder. '''''''''''''''''''''''''''''''''''''''''' Attch.Delete MItem.Attachments.Add Source:=TempPath & AttchName, Type:=olByValue Excel.Application.ScreenUpdating = True '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< '''''''''''''''''''''''''''''''' ' Send the message ''''''''''''''''''''''''''''''''' MItem.Send '''''''''''''''''''''''''''''''''''''''''''' ' Be a good citizen and clean up your trash. '''''''''''''''''''''''''''''''''''''''''''' On Error Resume Next Kill TempPath & Attch.Filename On Error GoTo 0 End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Reha" wrote in message ... Gord thanks for your reply, Before writing my mail I checked Chip Pearson' s page and also made a trial in my workbook but I was disapointed cause of the result that my macros were deleted all. What I need is to delete only the event macro on my file attached to the mail after I run my outlook macro and do not want to delete them in my original excel file. The procedure of my outlook macro is pasting a copy of the selected one between several sheets even with the event macro that creates a problem to my customers because of macro warning while opening my mail attachment and asked me always for virus infections. Eventually I need a code to delete or clear the worksheet event in my attachment. Regards Reha "Gord Dibben" <gorddibbATshawDOTca, haber iletisinde sunlari .. . Reha See Chip Pearson's site for info and code....... Specifically the section on "Deleting all VBA code in a project" About 2/3 of the way down the page. http://www.cpearson.com/excel/vbe.htm Gord Dibben MS Excel MVP On Sat, 9 Dec 2006 02:45:25 +0200, "Reha" wrote: Hi, I have a workbook of several sheets and all sheets have worksheet events. My problem is I do not want them sticked to my attached file when I run my email macro. Is there a possibility to insert a code to my outlook macro will be able to clear the worksheet events of my attached file automatically or any workbook event delete them on exit. Thanks for kindest supports Reha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IS THERE A WAY TO CLEAR WORKSHEET EVENTS USING ANOTHER MACRO
Dear Chip and Gord,
Thanks for everything to solve my big headache that everthing is OK and macro is working well. Chip especially your definition in your mail made me understand why I was mistaken to install the VBA remover macro and I did it. One more thanks to both of you and wish good luck always. Regards Reha "Chip Pearson" , haber iletisinde şunları ... Reha, I am by no means an expert in Outlook, and I put together the following code mostly by trial and error. There may be a better way to go about this, but I think the following code will do what you want. It takes the attached Excel file from a MailItem object, saves it to a temp folder, deletes all the VBA code from the workbook in the temp folder, deletes the original attachment, and re-attaches the modified file. The original copy of the file is not changed, only the attached copy is modified. The code between the lines marked with '<<<< are most relevant to your question. I assume your code has an object variable refering to a running Excel Application. Subsitute that variable's name where "Excel" appears in the code (but not in the variable declarations) that follows: Public Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" ( _ ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Dim OLK As Outlook.Application Const MAX_PATH = 260& Sub AAA() Dim MItem As Outlook.MailItem Dim Attch As Outlook.Attachment Dim WB As Excel.Workbook Dim AttchName As String Dim TempPath As String Dim Pos As Integer Dim PathLen As Long Dim VBComp As Object '''''''''''''''''''''''''''''''''''''''''''''''' ' Get a reference to a running instance of ' Outlook. '''''''''''''''''''''''''''''''''''''''''''''''' On Error Resume Next If OLK Is Nothing Then Set OLK = GetObject(, "Outlook.Application") End If If OLK Is Nothing Then ''''''''''''''''''''''''''''''''''''''''''''''''' ' Outlook isn't running. Get out. '''''''''''''''''''''''''''''''''''''''''''''''' Exit Sub End If On Error GoTo 0 '''''''''''''''''''''''''''''''''''''''''''' ' Get a temp folder name from Windows '''''''''''''''''''''''''''''''''''''''''''' TempPath = String$(MAX_PATH, vbNullChar) PathLen = GetTempPath(MAX_PATH, TempPath) TempPath = Left$(TempPath, PathLen) ''''''''''''''''''''''''' ' Create a new mail item ' and set the recipient. ''''''''''''''''''''''''' Set MItem = OLK.CreateItem(olMailItem) MItem.Recipients.Add " '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ''''''''''''''''''''''''''''''''''''''''''' ' Attach C:\bookone.xls to the mail message. '''''''''''''''''''''''''''''''''''''''''''' Set Attch = MItem.Attachments.Add("C:\bookone.xls", olByValue) ''''''''''''''''''''''''''''''''''''''''''''' ' Make sure there is no file with the same ' name as the attachment in the temp folder. ''''''''''''''''''''''''''''''''''''''''''''' On Error Resume Next Kill TempPath & Attch.Filename On Error GoTo 0 '''''''''''''''''''''''''''''''''''''''''''''' ' store the attachment's file name ' for later use. ''''''''''''''''''''''''''''''''''''''''''''' AttchName = Attch.Filename '''''''''''''''''''''''''''''''''''''''''''' ' Save the attachment as a file in the temp ' folder. '''''''''''''''''''''''''''''''''''''''''''' Attch.SaveAsFile TempPath & Attch.Filename Excel.Application.ScreenUpdating = False '''''''''''''''''''''''''''''''''''''''''''' ' Open the copy of the file that is in the ' temp folder. '''''''''''''''''''''''''''''''''''''''''''' Set WB = Excel.Workbooks.Open(Filename:=TempPath & Attch.Filename) ''''''''''''''''''''''''''''''''''''''''''' ' delete all VBA code ''''''''''''''''''''''''''''''''''''''''''' For Each VBComp In WB.VBProject.VBComponents Select Case VBComp.Type Case 1, 2, 3 With WB.VBProject.VBComponents .Remove .Item(VBComp.Name) End With Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp ''''''''''''''''''''''''''''''''''''''''''' ' Save and close the workbook. ''''''''''''''''''''''''''''''''''''''''''' WB.Close savechanges:=True '''''''''''''''''''''''''''''''''''''''''' ' Delete the original attachment and attach ' the modified file from the temp folder. '''''''''''''''''''''''''''''''''''''''''' Attch.Delete MItem.Attachments.Add Source:=TempPath & AttchName, Type:=olByValue Excel.Application.ScreenUpdating = True '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< '''''''''''''''''''''''''''''''' ' Send the message ''''''''''''''''''''''''''''''''' MItem.Send '''''''''''''''''''''''''''''''''''''''''''' ' Be a good citizen and clean up your trash. '''''''''''''''''''''''''''''''''''''''''''' On Error Resume Next Kill TempPath & Attch.Filename On Error GoTo 0 End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Reha" wrote in message ... Gord thanks for your reply, Before writing my mail I checked Chip Pearson' s page and also made a trial in my workbook but I was disapointed cause of the result that my macros were deleted all. What I need is to delete only the event macro on my file attached to the mail after I run my outlook macro and do not want to delete them in my original excel file. The procedure of my outlook macro is pasting a copy of the selected one between several sheets even with the event macro that creates a problem to my customers because of macro warning while opening my mail attachment and asked me always for virus infections. Eventually I need a code to delete or clear the worksheet event in my attachment. Regards Reha "Gord Dibben" <gorddibbATshawDOTca, haber iletisinde sunlari .. . Reha See Chip Pearson's site for info and code....... Specifically the section on "Deleting all VBA code in a project" About 2/3 of the way down the page. http://www.cpearson.com/excel/vbe.htm Gord Dibben MS Excel MVP On Sat, 9 Dec 2006 02:45:25 +0200, "Reha" wrote: Hi, I have a workbook of several sheets and all sheets have worksheet events. My problem is I do not want them sticked to my attached file when I run my email macro. Is there a possibility to insert a code to my outlook macro will be able to clear the worksheet events of my attached file automatically or any workbook event delete them on exit. Thanks for kindest supports Reha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 windows for workbook - Macro switches worksheet | Excel Discussion (Misc queries) | |||
| Macro wiping out worksheet | Excel Worksheet Functions | |||
| macro help | Excel Discussion (Misc queries) | |||
| Using a Macro to clear out check box seletions | Excel Worksheet Functions | |||
| Executing macro for all worksheet from a different worksheet | New Users to Excel | |||
Linear Mode
