Cleaning up artifacts when mail-merge fails

[1DMF]

Hi,

On rare occasions the mail-merge process fails, but is leaving a WINWORD.EXE process running in memory.

This in turn is then impeding any further mail-merge functionality from working due to this memory resident artifact.

For me the fix is easy, open task manager, kill the WINWORD.EXE process, press the button to run the mail-merge again.

However, this isn't user friendly and I get support calls when this occurs.

Is this a known phenomenon, and what I describe is the only fix, or do I have some bug somewhere that is leaving this artifact behind?

I'd like to know there is a needle before I dive into this haystack!

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Andrzejek]

How and where do you declare the Word object in your code?
Do you have error handlers to know when/if your mail marge process fails?
Do you [tt]Set objWord = Nothing[/tt] at the end, even when it fails?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

>Do you Set objWord = Nothing at the end, even when it fails?

If it's the end, then objWord will be going out of scope, and that has exactly the same effect.

It won't be that. It'll almost certainly simply need an objWord.Quit before objWord=Nothing (or before objWord goes out of scope).

(I assume you are already dealing with the scenario where Word might already be open before the mailmerge is started)

 

[1DMF]

Here is an extract...

Call KillFile(sXLS)

        ' run XLS export
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "selReport", sXLS, True
        
        ' set table name
        sTable = "selReport"

        ' create word object
        Set WordApp = CreateObject("Word.Application")
        
        ' Open document
        WordApp.Documents.Open sFile
        WordApp.Visible = False
        
        ' issue mailmerge command
        With WordApp.ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource Name:=sXLS, _
            ReadOnly:=True, Connection:=sTable, SubType:=wdMergeSubTypeWord, _
            SQLStatement:="Select * From [" & sTable & "]"
            .Destination = wdSendToNewDocument
            .Execute
        End With

        ' save document
        WordApp.NormalTemplate.Saved = True
        WordApp.ActiveDocument.SaveAs FileName:=sWord, FileFormat:=wdWordDocument

        ' quit word
        Call closeWord(WordApp, False)
        
        ' update case
        [Forms]![Check_Case].RepID = sDoc
        [Forms]![Check_Case].Refresh
        
        ' display document
        Call FollowHyperlink(sWord, , True)
        
    End If

Exit_add_report:
    
    ' kill objects
    Set oContact = Nothing
    Set WordApp = Nothing
       
    ' delete XLS File
    Call KillFile(sXLS)
    
    Exit Sub

EH_add_report:

    MsgBox "Error in addReport : " & Err.Description & " - " & Err.Number & " ( " & sDoc & ")"
    Resume Exit_add_report

I have no idea if they got an error msg or what actually happened, and the other issue I have is the user said they used task manager and 'End Task' to kill the Access App, so perhaps it was hanging due to network / SQL slowness and the user got impatient and killed Access before the clean up code got a chance to run?

I'm not bombarded with support request due to this, it's on the odd occasion, so I don't believe there is a fundamental flaw / bug in the above code, unless you can spot one?

(I assume you are already dealing with the scenario where Word might already be open before the mailmerge is started)

err no, it that a thing? Can you elaborate please.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

>' quit word
>Call closeWord(WordApp, False)

I suspect you'll find

WordApp.Quit

in that procedure. And this is being skipped when an error is raised.

 

[1DMF]

Hi Mike,

Yeah, I did wander about this myself, I've moved it to the clean up code (exit).

The function ignores any errors itself, which is why I outsourced the closure to a function.

Might be a while before I can tell if it's resolved the issue, due to the infrequency it happens.

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music