EMail Outlook failing

S

ssGuru

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
 
S

ssGuru

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
Click to expand...

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),Subject:=(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
 
S

ssGuru

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


















- Show quoted text -
Click to expand...

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
 
S

ssGuru

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 -
Click to expand...

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 "(e-mail address removed)", _
"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 "(e-mail address removed)", _
"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
 
S

ssGuru

Hi ssGuru

Do you use Excel/Outlook 2007 ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm













- Show quoted text -
Click to expand...

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
 
S

ssGuru

Can you send me the workbook private

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm





...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

file extension shows up twice on attachment 2
Keeping Conditional fromating When Emailing Using A Control Button 0
Send round office a macro enabled ss 0
Address Range in Email Script 4
Change Range in macro 2
Add body message to auto-email code in Outlook 6
Ask for email title/comments in working Mail Active Sheet code 3
Adding text to email body 1

Top