Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Email macro not working
I have used Ron de Bruin's code for emailing a worksheet in Outlook 2003 for
quite sometime and it works perfect, (I use the .Send not the .display). Now for the problem I have been asked to use the .display now instead of .send because we are now having to add an extra address (not very often maybe once a month or so). All I changed was the comment out of .Send and the uncommenting of .Display and it seemed to work until I hit the SEND button in Outlook and I get this error message "Operation can not be performed because the object has been deleted" If I reverse the commenting it sends OK. Below is the code I am using Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Exit Sub With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook Destwb.Colors = Sourcewb.Colors 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = "" & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use '.display End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Any help is greatly appreciated Danny |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Email macro not working
Hi tankerman
Do you have the same problem when you test one of the the example workbook on my site for the Outlook object model http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tankerman" wrote in message ... I have used Ron de Bruin's code for emailing a worksheet in Outlook 2003 for quite sometime and it works perfect, (I use the .Send not the .display). Now for the problem I have been asked to use the .display now instead of .send because we are now having to add an extra address (not very often maybe once a month or so). All I changed was the comment out of .Send and the uncommenting of .Display and it seemed to work until I hit the SEND button in Outlook and I get this error message "Operation can not be performed because the object has been deleted" If I reverse the commenting it sends OK. Below is the code I am using Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Exit Sub With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook Destwb.Colors = Sourcewb.Colors 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = "" & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use '.display End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Any help is greatly appreciated Danny |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Email macro not working
Hi Ron
Thanks for answering, I went back and tried to use mail sheet in outlook body, the .display worked fine it mailed the sheet to my address just fine. I tried using send workbook last saved version, workbook newly created, send only activesheet, and send sheet index on all of these I got the message "Operation cannot be performed because object has been deleted". If I click OK and hit send again I get the same message, I tried to attach another file and got the same message, BUT if I deleted the attachement before or after the error message I was able to send the message as a blank message or attach another file. Could it be how our Outlook is setup, I don't know. Thank You Tankerman "Ron de Bruin" wrote: Hi tankerman Do you have the same problem when you test one of the the example workbook on my site for the Outlook object model http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tankerman" wrote in message ... I have used Ron de Bruin's code for emailing a worksheet in Outlook 2003 for quite sometime and it works perfect, (I use the .Send not the .display). Now for the problem I have been asked to use the .display now instead of .send because we are now having to add an extra address (not very often maybe once a month or so). All I changed was the comment out of .Send and the uncommenting of .Display and it seemed to work until I hit the SEND button in Outlook and I get this error message "Operation can not be performed because the object has been deleted" If I reverse the commenting it sends OK. Below is the code I am using Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Exit Sub With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook Destwb.Colors = Sourcewb.Colors 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = "" & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use '.display End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Any help is greatly appreciated Danny . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
How do I create an email macro to auto fill the email? | Excel Discussion (Misc queries) | |||
Links sent via Email not working | Links and Linking in Excel | |||
Email VB not working | Excel Discussion (Misc queries) | |||
working on excel document in email saved changes in email not in . | Excel Discussion (Misc queries) |