Release Variables

[Jeepers321]

I have a button on an outlook form that takes the data from that form and sends it to a word document with strategically placed bookmarks for that data.

My problem is this.

When the user clicks this button, everything works fine, but when they close the document or if they close MSWord the document is still open in the memory. I had a user this morning tell me that when she opened word she got 8 recovery documents that also opened and they all pretained to documents this form created the day before.

Please look at my code below and offer any suggestion you may have. I thought I was killing everything I "Set" but there is something there that just won't DIE.

Thanks.


Sub OLBtn_Click()

Dim strTemplate
Dim objWord
Dim objDocs
Dim mybklist
Dim CustRefNum

Set objWord = CreateObject("Word.Application&quot

' Put the name of your Word template that contains the bookmarks
strTemplate = "MyWordTemplate.dot"

' Location of Word template; could be on a shared LAN
strTemplate = "\\Network Folder\Template Folder\" & strTemplate

Set objDocs = objWord.Documents

objDocs.Add strTemplate
set mybklist = objWord.ActiveDocument.Bookmarks


' Bookmark CustRefNum
objWord.ActiveDocument.Bookmarks("CustRefNum&quot.Select 'Name of the Document Bookmark
CustRefNum = Item.UserProperties.find("Customer Ref&quot.Value 'Name of the Outlook Field
If CustRefNum = True then
CustRefNum = "Yes"
ElseIf CustRefNum = False then
CustRefNum = ""
End If
objWord.Selection.TypeText Cstr(CustRefNum)



objWord.visible = True

Set strTemplate = Nothing
Set ObjWord = Nothing
Set ObjDocs = Nothing
Set mybklist = Nothing
Set CustRefNum = Nothing

End Sub

 

[tsuji]

Hello Jeepers321,

I suppose further editing and saving of the document are left to the care of the users? In that case, I don't think recovery documents etc are due to this Sub.

Nevetheless, you can improve the memory release just a tiny bit. (1) When you de-reference objects, it is safer to do it in reverse order of its being set, (2) you do not set nothing to any variable other than objects. Hence,

'Set strTemplate = Nothing '<---no need
Set mybklist = Nothing '<---moved up
Set ObjDocs = Nothing '<---moved up
Set ObjWord = Nothing
'Set CustRefNum = Nothing '<---no need

regards - tsuji

 

[tsuji]

Further note:

Also, an additional spot that can cause trouble is your add doc with template located somewhere (remotely?). It might not be able to find it or might take long time to locate. So user(s) might get impatient (as you make word visible only after all the preparation done) and click again. After multiple click, user(s) might get confused and all mixed up.
If this is the reason, you might consider better control surrounding this line and perhaps user-friendly message advising user(s) work-in-progress?

- tsuji

 

[Jeepers321]

tsuji,
I tried your ideas and the document is still remaining in the memory after it prints. My best guess is that the I should put some code on the Word Template to make sure that it is released after it prints.

Thanks again for your help and if you have any additional suggestion please let me know.

 

[mrmovie]

do you have to close the template document that you have opened? before you close the word application