Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
I had the email code shown below working just fine. No problems.
I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
On Aug 23, 2:43 pm, ssGuru wrote:
I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub I walked through the e-mail routine and this is what I found out everything seems to be OK until it gets past the setting eCopy1 and 2 and 3 and so forth When it gets past With Destwb Destwb should be a temporary .xls file with just 2 of the worksheets from the original workbook and becomes the ActiveWorkbook? When it gets to this line you either get Not enough memory or The File exists depending on whether the file is in the Temp folder .SaveAs TempFilePath & TempFileName,FileFormat(ReportDate, "yymmdd") & " Forcast" When you get to this line you get Not enough memory ..SendMail Recepients:=Array(eMain,eCopy1,eCopy2,eCopy3),Subj ect:=(RptCreator & " Forcast " & ReportDate) When it gets to this line it asks you if you want to End Debug Help Kill TempFilePath & TempFileName If you press Debug it just takes you back to the line If you press End it ends the routine Could sure use some help with this memory issue. Thanks Dennis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
You changed a lot in the Macro from my site
http://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ssGuru" wrote in message oups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
On Aug 24, 12:10 am, "Ron de Bruin" wrote:
You changed a lot in the Macro from my sitehttp://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in ooglegroups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub- Hide quoted text - - Show quoted text - Yes the add-in worked just fine. And my original modification of your code worked fine. I must have changed SOMETHING incorrectly since I am now generating this MEMORY error. I have just changed this part of the code to include the file extension. The variables clearly show the proper path using my Environment and the TempFileName looks good created from my range variables. When I put the break on the "With Destwb.."code then the memory error doesn't fire. TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator & FileExtStr With Destwb When I put the break on the ".SaveAs...." the Memory error does fire .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next When I look at the TempFilePath and the TempFileName values it looks right. So it must be something to do with Destwb whose value is the temporary workbook created from 2 of the worksheets. Maybe if these were created with PasteSpecial Values it might help? The first sheet has some calculations while the 2nd does not. (I tried your code to do that but it failed and everything else used to work so I stopped dealing with it.) I have watched file explorer on my Environment TEMP folder as it creates the many temporary files during the process and when it eventually creates the tempory file to send and it all looks OK. No large files are created. My workbook that runs this code is 2.8 Mb and the resulting file that is emailed with the 2 pages is only about 104k. Any suggestion on what I can do to find why it is. I can send you the screen shots of the error dialogue boxes if you like. BTW I have credited you in the code. Thanks again for posting it. Dennis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
On Aug 24, 12:03 pm, ssGuru wrote:
On Aug 24, 12:10 am, "Ron de Bruin" wrote: You changed a lot in the Macro from my sitehttp://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in ooglegroups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub- Hide quoted text - - Show quoted text - Yes the add-in worked just fine. And my original modification of your code worked fine. I must have changed SOMETHING incorrectly since I am now generating this MEMORY error. I have just changed this part of the code to include the file extension. The variables clearly show the proper path using my Environment and the TempFileName looks good created from my range variables. When I put the break on the "With Destwb.."code then the memory error doesn't fire. TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator & FileExtStr With Destwb When I put the break on the ".SaveAs...." the Memory error does fire .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next When I look at the TempFilePath and the TempFileName values it looks right. So it must be something to do with Destwb whose value is the temporary workbook created from 2 of the worksheets. Maybe if these were created with PasteSpecial Values it might help? The first sheet has some calculations while the 2nd does not. (I tried your code to do that but it failed and everything else used to work so I stopped dealing with it.) I have watched file explorer on my Environment TEMP folder as it creates the many temporary files during the process and when it eventually creates the tempory file to send and it all looks OK. No large files are created. My workbook that runs this code is 2.8 Mb and the resulting file that is emailed with the 2 pages is only about 104k. Any suggestion on what I can do to find why it is. I can send you the screen shots of the error dialogue boxes if you like. BTW I have credited you in the code. Thanks again for posting it. Dennis- Hide quoted text - - Show quoted text - Ron, I recopied your code to TEST again and only changed the email address and the Array sheets Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" I still get the ResourceMemory ERROR on the .SendMail statement IF Outlook is closed. Your code and Outlook open seems to work again. .SendMail ", _ "This is the Subject line" On Error GoTo 0 Your code, I don't get an error on the Kill statement and it deletes the file in TEMP just fine. Sooooo it seems that I have to test for Outlook being open and open it if not BEFORE running SendMail. I thought SendMail didn't care what eMail client was in use or whether it was running before doing its thing??? How would I handle this problem?? Dennis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
Hi ssGuru
Do you use Excel/Outlook 2007 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ssGuru" wrote in message ups.com... On Aug 24, 12:03 pm, ssGuru wrote: On Aug 24, 12:10 am, "Ron de Bruin" wrote: You changed a lot in the Macro from my sitehttp://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in ooglegroups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub- Hide quoted text - - Show quoted text - Yes the add-in worked just fine. And my original modification of your code worked fine. I must have changed SOMETHING incorrectly since I am now generating this MEMORY error. I have just changed this part of the code to include the file extension. The variables clearly show the proper path using my Environment and the TempFileName looks good created from my range variables. When I put the break on the "With Destwb.."code then the memory error doesn't fire. TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator & FileExtStr With Destwb When I put the break on the ".SaveAs...." the Memory error does fire .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next When I look at the TempFilePath and the TempFileName values it looks right. So it must be something to do with Destwb whose value is the temporary workbook created from 2 of the worksheets. Maybe if these were created with PasteSpecial Values it might help? The first sheet has some calculations while the 2nd does not. (I tried your code to do that but it failed and everything else used to work so I stopped dealing with it.) I have watched file explorer on my Environment TEMP folder as it creates the many temporary files during the process and when it eventually creates the tempory file to send and it all looks OK. No large files are created. My workbook that runs this code is 2.8 Mb and the resulting file that is emailed with the 2 pages is only about 104k. Any suggestion on what I can do to find why it is. I can send you the screen shots of the error dialogue boxes if you like. BTW I have credited you in the code. Thanks again for posting it. Dennis- Hide quoted text - - Show quoted text - Ron, I recopied your code to TEST again and only changed the email address and the Array sheets Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" I still get the ResourceMemory ERROR on the .SendMail statement IF Outlook is closed. Your code and Outlook open seems to work again. .SendMail ", _ "This is the Subject line" On Error GoTo 0 Your code, I don't get an error on the Kill statement and it deletes the file in TEMP just fine. Sooooo it seems that I have to test for Outlook being open and open it if not BEFORE running SendMail. I thought SendMail didn't care what eMail client was in use or whether it was running before doing its thing??? How would I handle this problem?? Dennis |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
On Aug 25, 10:24 am, "Ron de Bruin" wrote:
Hi ssGuru Do you use Excel/Outlook 2007 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in oglegroups.com... On Aug 24, 12:03 pm, ssGuru wrote: On Aug 24, 12:10 am, "Ron de Bruin" wrote: You changed a lot in the Macro from my sitehttp://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in ooglegroups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub- Hide quoted text - - Show quoted text - Yes the add-in worked just fine. And my original modification of your code worked fine. I must have changed SOMETHING incorrectly since I am now generating this MEMORY error. I have just changed this part of the code to include the file extension. The variables clearly show the proper path using my Environment and the TempFileName looks good created from my range variables. When I put the break on the "With Destwb.."code then the memory error doesn't fire. TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator & FileExtStr With Destwb When I put the break on the ".SaveAs...." the Memory error does fire .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next When I look at the TempFilePath and the TempFileName values it looks right. So it must be something to do with Destwb whose value is the temporary workbook created from 2 of the worksheets. Maybe if these were created with PasteSpecial Values it might help? The first sheet has some calculations while the 2nd does not. (I tried your code to do that but it failed and everything else used to work so I stopped dealing with it.) I have watched file explorer on my Environment TEMP folder as it creates the many temporary files during the process and when it eventually creates the tempory file to send and it all looks OK. No large files are created. My workbook that runs this code is 2.8 Mb and the resulting file that is emailed with the 2 pages is only about 104k. Any suggestion on what I can do to find why it is. I can send you the screen shots of the error dialogue boxes if you like. BTW I have credited you in the code. Thanks again for posting it. Dennis- Hide quoted text - - Show quoted text - Ron, I recopied your code to TEST again and only changed the email address and the Array sheets Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" I still get the ResourceMemory ERROR on the .SendMail statement IF Outlook is closed. Your code and Outlook open seems to work again. .SendMail ", _ "This is the Subject line" On Error GoTo 0 Your code, I don't get an error on the Kill statement and it deletes the file in TEMP just fine. Sooooo it seems that I have to test for Outlook being open and open it if not BEFORE running SendMail. I thought SendMail didn't care what eMail client was in use or whether it was running before doing its thing??? How would I handle this problem?? Dennis- Hide quoted text - - Show quoted text - Outlook and Excel 2003 on this PC I have Outlook open before starting process. I have McAfee running. I can send you the screen shot of the ERROR but need a real email address as the group doesn't support screen shots. I have commented out my code and am running just your barebones code with the only changes being the sheet names and the email address. It completes the process BUT I continue to get the ERROR not enough resources at two points in the code. First when the temp file is SaveAs and renamed and second when the SendMail fires. I see the file being properly named and created in the TEMP environment folder just fine even though the ERROR says it can't continue. I see the mail sent just fine even though the ERROR says it can't continue The Kill statement DOES delete the file from the TEMP environment folder. I do have some sheets hidden and the sheets being emailed do ultimately have links to an external WB. I tried your portion of the code that PASTE SPECIAL VALUES but it fails on the 2nd page after copying the range. Could this be any indication of the resource memory problem? I could just turn off the ERROR during this process but I am reluctant to do that. Thanks again for all your help and advice, Hope we can resolve this issue and help others who may have similar experiences, Dennis |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
Can you send me the workbook private
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ssGuru" wrote in message ups.com... On Aug 25, 10:24 am, "Ron de Bruin" wrote: Hi ssGuru Do you use Excel/Outlook 2007 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in oglegroups.com... On Aug 24, 12:03 pm, ssGuru wrote: On Aug 24, 12:10 am, "Ron de Bruin" wrote: You changed a lot in the Macro from my sitehttp://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in ooglegroups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub- Hide quoted text - - Show quoted text - Yes the add-in worked just fine. And my original modification of your code worked fine. I must have changed SOMETHING incorrectly since I am now generating this MEMORY error. I have just changed this part of the code to include the file extension. The variables clearly show the proper path using my Environment and the TempFileName looks good created from my range variables. When I put the break on the "With Destwb.."code then the memory error doesn't fire. TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator & FileExtStr With Destwb When I put the break on the ".SaveAs...." the Memory error does fire .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next When I look at the TempFilePath and the TempFileName values it looks right. So it must be something to do with Destwb whose value is the temporary workbook created from 2 of the worksheets. Maybe if these were created with PasteSpecial Values it might help? The first sheet has some calculations while the 2nd does not. (I tried your code to do that but it failed and everything else used to work so I stopped dealing with it.) I have watched file explorer on my Environment TEMP folder as it creates the many temporary files during the process and when it eventually creates the tempory file to send and it all looks OK. No large files are created. My workbook that runs this code is 2.8 Mb and the resulting file that is emailed with the 2 pages is only about 104k. Any suggestion on what I can do to find why it is. I can send you the screen shots of the error dialogue boxes if you like. BTW I have credited you in the code. Thanks again for posting it. Dennis- Hide quoted text - - Show quoted text - Ron, I recopied your code to TEST again and only changed the email address and the Array sheets Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" I still get the ResourceMemory ERROR on the .SendMail statement IF Outlook is closed. Your code and Outlook open seems to work again. .SendMail ", _ "This is the Subject line" On Error GoTo 0 Your code, I don't get an error on the Kill statement and it deletes the file in TEMP just fine. Sooooo it seems that I have to test for Outlook being open and open it if not BEFORE running SendMail. I thought SendMail didn't care what eMail client was in use or whether it was running before doing its thing??? How would I handle this problem?? Dennis- Hide quoted text - - Show quoted text - Outlook and Excel 2003 on this PC I have Outlook open before starting process. I have McAfee running. I can send you the screen shot of the ERROR but need a real email address as the group doesn't support screen shots. I have commented out my code and am running just your barebones code with the only changes being the sheet names and the email address. It completes the process BUT I continue to get the ERROR not enough resources at two points in the code. First when the temp file is SaveAs and renamed and second when the SendMail fires. I see the file being properly named and created in the TEMP environment folder just fine even though the ERROR says it can't continue. I see the mail sent just fine even though the ERROR says it can't continue The Kill statement DOES delete the file from the TEMP environment folder. I do have some sheets hidden and the sheets being emailed do ultimately have links to an external WB. I tried your portion of the code that PASTE SPECIAL VALUES but it fails on the 2nd page after copying the range. Could this be any indication of the resource memory problem? I could just turn off the ERROR during this process but I am reluctant to do that. Thanks again for all your help and advice, Hope we can resolve this issue and help others who may have similar experiences, Dennis |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
EMail Outlook failing
On Aug 27, 2:16 pm, "Ron de Bruin" wrote:
Can you send me the workbook private -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in oglegroups.com... On Aug 25, 10:24 am, "Ron de Bruin" wrote: Hi ssGuru Do you use Excel/Outlook 2007 ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in oglegroups.com... On Aug 24, 12:03 pm, ssGuru wrote: On Aug 24, 12:10 am, "Ron de Bruin" wrote: You changed a lot in the Macro from my sitehttp://www.rondebruin.nl/mail/folder1/mail3.htm Is this one working OK for you ? -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "ssGuru" wrote in ooglegroups.com... I had the email code shown below working just fine. No problems. I moved the Excel files to the root drive in a sub folder and added some other code. Now the following code behind a EMail Reports button fails at the KILL statement near the end where it is supposed to delete the temorary file it created as an attachment. I can see the file in the Environment %/TEMP location and the code variable shows the correct path though it is hard to tell. PROBLEM: I get OUT OF MEMORY errors popups (twice) which are actually referring to Resource Memory I think. "Excel cannot complete this task with available resources. Choose less data or close other applications" Manually attaching the files to an email causes no problems. The file as attached is only 104kb. Plenty of HD space. 1Gb RAM. XP Pro. Same kinds of errors on other test machines and OS. So it seems to me that the code is failing at the KILL since it can't do it's thing. The email actually gets sent even though the process appears to have failed. But the error windows and the debug window would confuse a user. I also expect and do see the Outlook dialogue box "...A program is trying to automatically send e-mail....." where I expect the user to choose "YES". Is there a way to test for a file with the same name in the users/ local settings/TEMP and delete it first or allow an overwrite?? Thanks for any advice, Dennis EMAIL BUTTON CODE IN QUESTION: Private Sub btnEMail_Click() 'Works in Excel 97-2007, Tested with Outlook 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 sh As Worksheet Dim RptCreator As String Dim ReportDate As String Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy Set Destwb = ActiveWorkbook '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 sheets 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 'DD Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" eMain = Range("eMailMain").Value eCopy1 = Range("eMailCopy1").Value eCopy2 = Range("eMailCopy2").Value eCopy3 = Range("eMailCopy3").Value RptCreator = Range("RptCreator").Value ReportDate = Range("RptDate").Value 'TempFileName = RptCreator & "-" & Sourcewb.Name 'TempFileName = RptCreator & "_" & Format(ReportDate, "yymmdd") & " Forecast" TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator With Destwb 'DD TEST .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next 'DD TEST .SendMail "emailaddress", "This is the Subject line" '.SendMail eMailList, RptCreator & " Forcast " & ReportDate .SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3), Subject:=(RptCreator & " Forcast " & ReportDate) On Error GoTo 0 .Close savechanges:=False End With 'DD Kill TempFilePath & TempFileName & FileExtStr Kill TempFilePath & TempFileName ''THIS IS WHERE IT FAILS 'DD reset variables eCopy1 = "" eCopy2 = "" eCopy3 = "" RptCreator = "" ReportDate = "" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub- Hide quoted text - - Show quoted text - Yes the add-in worked just fine. And my original modification of your code worked fine. I must have changed SOMETHING incorrectly since I am now generating this MEMORY error. I have just changed this part of the code to include the file extension. The variables clearly show the proper path using my Environment and the TempFileName looks good created from my range variables. When I put the break on the "With Destwb.."code then the memory error doesn't fire. TempFileName = Format(ReportDate, "yymmdd") & "-Forecast-" & RptCreator & FileExtStr With Destwb When I put the break on the ".SaveAs...." the Memory error does fire .SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum On Error Resume Next When I look at the TempFilePath and the TempFileName values it looks right. So it must be something to do with Destwb whose value is the temporary workbook created from 2 of the worksheets. Maybe if these were created with PasteSpecial Values it might help? The first sheet has some calculations while the 2nd does not. (I tried your code to do that but it failed and everything else used to work so I stopped dealing with it.) I have watched file explorer on my Environment TEMP folder as it creates the many temporary files during the process and when it eventually creates the tempory file to send and it all looks OK. No large files are created. My workbook that runs this code is 2.8 Mb and the resulting file that is emailed with the 2 pages is only about 104k. Any suggestion on what I can do to find why it is. I can send you the screen shots of the error dialogue boxes if you like. BTW I have credited you in the code. Thanks again for posting it. Dennis- Hide quoted text - - Show quoted text - Ron, I recopied your code to TEST again and only changed the email address and the Array sheets Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" I still get the ResourceMemory ERROR on the .SendMail statement IF Outlook is closed. Your code and Outlook open seems to work again. .SendMail ", _ "This is the Subject line" On Error GoTo 0 Your code, I don't get an error on the Kill statement and it deletes the file in TEMP just fine. Sooooo it seems that I have to test for Outlook being open and open it if not BEFORE running SendMail. I thought SendMail didn't care what eMail client was in use or whether it was running before doing its thing??? How would I handle this problem?? Dennis- Hide ... read more »- Hide quoted text - - Show quoted text - I have the problem narrowed down to an issue that occurs when the code is trying to send a calculated WS. If the WS is calculated with NamedRanges on other WS and that are full columns it then has the out of memory issue. I am testing further now that I have narrowed the problem to this issue and will post when I am able to clearly define the issue and the fix. Dennis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up Outlook email? | New Users to Excel | |||
Email using Outlook | Excel Programming | |||
Outlook Email | Excel Programming | |||
Email & Outlook | Excel Discussion (Misc queries) | |||
Late Binding to Outlook from Excel: Outlook modifies email body | Excel Programming |