Out of stack error with VBA mail merge and email

G

gfspiteri

Dear experts,
I have been trying to produce a mail merge using Word 2000 on Windows
2000 and then emailing the results via a macro. The macro first does
the mail merge, which works fine, but when I then try to save using the
activedocument.saveas method, word give an Out of Stack Space error and
crashes, AFTER saving the file. The next section then should
technically email the document as an attachment (and I know this bit
works fine).

I have tried this on another machine and the same thing happens. I
would really appreciate any help on how to solve this or ways of going
around this problem. The VBA code is below,
cheers
Gianfranco

Sub mailmerge()
Documents.Open
FileName:="F:\Users\GSpiteri\ExceedanceProject\exceedance_merge.doc",
ConfirmConversions:=False _
, ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

ActiveDocument.mailmerge.Destination = wdSendToNewDocument
ActiveDocument.mailmerge.Execute

End Sub

Sub exceedance()
'saves mailmerge
Dim SaveName As String
Dim Path As String
Dim FileName As String


Windows("exceedance_merge.doc").Activate
ActiveWindow.Close wdDoNotSaveChanges
Path = "F:\Users\GSpiteri\ExceedanceProject\Reports\"
FileName = "exceedance" + Format$(Date, "ddmmyyyy")
SaveName = Path + FileName

'check if document already exists and delete it if yes

If Len(Dir(SaveName & ".doc")) > 0 Then
Kill [SaveName] & ".doc"
MsgBox [SaveName] & ".doc has been annihilated"
Else
MsgBox [SaveName] & ".doc never existed"
End If
'problem part
ActiveDocument.SaveAs ([SaveName])
'crashes here
End Sub

Sub email()
'email document as attachment
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem

On Error Resume Next

If Len(ActiveDocument.Path) = 0 Then
MsgBox "Document needs to be saved first"
Exit Sub
End If

Set oOutlookApp = GetObject(, "Outlook.Application")

If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
.To = "(e-mail address removed)"
.CC = ""
.Subject = "Exceedance report, Northern Ireland, " & Format$(Date,
"dd-MMM-yyyy")
.Body = "Exceedance report generated on " & Format$(Date, "dd MMMM
yyyy") & " attached," & vbCr & "Gianfranco" & vbCr
'Add the document as an attachment, you can use the .displayname
property
'to set the description that's used in the message
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue,
_
DisplayName:="ExceedanceReport" & Format$(Date, "mm-dd-yyyy")
.Send
End With

If bStarted Then
oOutlookApp.Quit
End If

Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub
 
C

Cindy M.

Hi GianFranco
I have been trying to produce a mail merge using Word 2000 on Windows
2000 and then emailing the results via a macro. The macro first does
the mail merge, which works fine, but when I then try to save using the
activedocument.saveas method, word give an Out of Stack Space error and
crashes, AFTER saving the file. The next section then should
technically email the document as an attachment (and I know this bit
works fine).
Click to expand...
Are you able to save the document manually, using File/Save As?

When the code fails, is it deleting the file beforehand / did the file
already exist on the machine?

If you comment out the SaveAs line, then Debug.Print the SaveName
information will the procedure complete? If it does, Ctrl+G to see the
Immediate Window where Debug.Print writes the information. Select the
result, copy and use in File/Save As. Can you save the document with the
information SaveName generates?

(And have you considered adding ".doc" to SaveName before using it in
SaveAs? I don't think that should be causing the problem, but...)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 
G

gakku

Hi Cindy,
Are you able to save the document manually, using File/Save As?
Click to expand...

Yes, this works fine.
When the code fails, is it deleting the file beforehand / did the file
already exist on the machine?
Click to expand...

The code looks for a file of the same nameand deletes it before trying
to save, so I dont think this should be a problem, however the error
happens both if the document existed before and if the document was not
present
If you comment out the SaveAs line, then Debug.Print the SaveName
information will the procedure complete? If it does, Ctrl+G to see the
Immediate Window where Debug.Print writes the information. Select the
result, copy and use in File/Save As. Can you save the document with the
information SaveName generates?
Click to expand...

This seems to be the problem - when I use the SaveName information from
debug.print and saveas manually, word still crashes.

Still dont know how to work round this though!

many thanks and looking forward to the solution :)

Gianfranco
 
C

Cindy M.

Hi Gakku,
The code looks for a file of the same nameand deletes it before trying
to save, so I dont think this should be a problem, however the error
happens both if the document existed before and if the document was not
present
Click to expand...
Sometimes, the file system can't complete the deletion before you try to
save again. I just wanted to make sure we weren't dealing with that kind
of conflict :)
This seems to be the problem - when I use the SaveName information from
debug.print and saveas manually, word still crashes.
Click to expand...
OK, can you show us (copy/paste) the result of Debug.print, please? I
don't see anything from the code that would be a problem, but perhaps
when I see the result it will trigger an alarm bell.

I have a couple of suggestions that I don't expect to help, but let's
make sure:

FileName = "exceedance" + Format$(Date, "ddmmyyyy")
SaveName = Path + FileName

Instead of using a +, use a & to concatenate the parts of FileName. VBA
can coerce + to concatenate, but its primary use is to add numbers. Since
the result of the Format function is numeric, you may be running into
something, here.

Also, remove the $ from the Formaat function. More recent versions of VBA
don't use that. I doubt it's the problem, but let's make sure :)

You might also try putting something non-numeric after the date: & "x"

And finally, put - & ".doc" - at the end of the SaveName line.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 
G

gakku

Cindy,
did the changes you suggested. The debug.print comes out as:
F:\Users\GSpiteri\ExceedanceProject\Reports\exceedance21082006x.doc
and it still crashes - however now I notice it also crashes when I try
to save the mail merge results manually with any name. (I think this is
what you asked earlier, I misunderstood) So maybe the problem is with
the mail merge?

The thing is, it only crashes when I do the mail merge through VBA not
when I do it manually. So if I run the mail merge manually and then run
the second part of the VBA code, everything works fine.

Gianfranco
 
C

Cindy M.

Hi Gakku,
however now I notice it also crashes when I try
to save the mail merge results manually with any name.
Click to expand...
Excellent detective work :) I wasn't concentrating too much on that part
of your code...

So, the problem occurs also after Sub mailmerge has executed, before you
ever run exceedance? (Just want to make sure it's nothing in that proc)

As a first step, let's try changing the proc a little bit:

Sub Execute_mailmerge() 'To avoid any mixup with mailmerge method name
Dim doc as Word.Document
Set doc = Documents.Open( _
FileName:="F:\Users\GSpiteri\ExceedanceProject\exceedance_merge.doc")

doc.mailmerge.Destination = wdSendToNewDocument
doc.mailmerge.Execute
End Sub

Can you now directly File/SaveAs in the Word UI?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 
G

gakku

another change...
I no longer get the out of stack error, now word just crashes,
sometimes after completing the whole code and sending the email,
sometimes right after saving.
Word creates an error log and the code for the error is "Exception
number: c0000005 (access violation)"

I am completely stuck!
Gianfranco
 
G

gakku

I must be the unluckiest guy on the planet right now :)
still unable to saveas and I get a new error:
A table in this document has become corrupted...(though this doesnt
always appear)
and word crashes again!
from the log file: Exception number: c0000005 (access violation)

if I remove all tables, then it crashes with the same log file
exception error,

Gianfranco
 
G

gakku

Hi Cindy,
found a non-ideal workaround:
I saved the file as RTF and Word did not crash and the whole
merge-save-email program worked. The only downside is that the file is
now 3Mb in size which is too much to email, whereas the word file
previously was 0.5Mb (the file is merge of a large number of graphs
mainly)
I still would like to get a decently sized document so if you have any
suggestions I am all ears,
thanks
Gianfranco
 
C

Cindy M.

Hi Gakku,
I no longer get the out of stack error, now word just crashes,
sometimes after completing the whole code and sending the email,
sometimes right after saving.
Word creates an error log and the code for the error is "Exception
number: c0000005 (access violation)"
Click to expand...
OK, actually, I think this is good news :) Apparently, this file's
internal structures have been damaged.

Go back to the original main merge document. Copy everything EXCEPT
the last paragraph mark to a NEW document. SAVEAS the original main
merge document to a different name/path. SaveAs the new document to
the original name/path (so the macro finds it). Link the new
document to the data source and test (manually, first) merging,
saving and sending.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17
2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :)
 
C

Cindy M.

<[email protected]>
<VA.00000174.01f47c34@speedy>
<[email protected]>
Newsgroups: microsoft.public.word.vba.general
NNTP-Posting-Host: 239.83.202.62.cust.bluewin.ch 62.202.83.239
Lines: 1
Path: number1.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
Xref: number1.nntp.dca.giganews.com microsoft.public.word.vba.general:89876

Hi Gianfranco,
worked perfectly
Click to expand...
Great:) I'm relieved the issue is resolved - it was a
nasty one!

Cindy Meister
 

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

Word macro that can call cells out of Excel for a find and replace 0
VBA - exporting serial letters to single documents 0
Formatting an email sent from Word through Outlook 4
command button to send mail 2
Formatted e-mails 2
Using Word VBA to send emails not working 0
Making a macro only work on certain files 2
Working outlook via vba code with xlsx - error 0

Top