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