Word Mail Merge Custom Template

[Guest]

I can open a Word custom mail merge template. However, the user still has to
click on the merge button to create the document. Is there a way to do this
in code so the user doesn't have to click the button. All the user sees is
the new document that is create from the mail merge template. So far I have
this in my code:

Public Function funWord_Regular()

Dim objWord As Object
Dim objMergeDoc As Object

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set objWord = CreateObject("Word.Application")
End If

Err.Clear

objWord.Visible = True
objWord.Activate

Set objMergeDoc = objWord.Documents.Add("H:\Access\Testing\ack regular.dot")

End Function

thanks!

 

[Geoff]

Check out the Mailmerge object.
See post to IndianaJonas on June 15.

 

[Guest]

Everything words great! Able to open my letters in word and have the
mailmerge documents close automatically. However, when the user closes out of
word they get

WINWORD.exe has generated errors and will be closed by Windows. you will
need to restart the program.
An error log is being created.

Any idea why this would happen? Here is the code that generates 2 types of
mail merge letters and activate and make visible word:

Public Function funWord_Regular()

Dim objWord As Word.Application, objMergeDoc As Word.Document, objMerge As
Word.MailMerge

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set objWord = CreateObject("Word.Application")
End If

Err.Clear

On Error GoTo ErrHandler

Set objMergeDoc = objWord.Documents.Add("H:\Access\Testing\ack regular.dot")

Set objMerge = objMergeDoc.MailMerge
objMerge.Execute
objMergeDoc.Close (wdDoNotSaveChanges)

Exit Function

ErrHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Err.Clear

End Function

Public Function funWord_Tribute()

Dim objWord As Word.Application, objMergeDoc As Word.Document, objMerge As
Word.MailMerge

On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set objWord = CreateObject("Word.Application")
End If

Err.Clear

On Error GoTo ErrHandler

Set objMergeDoc = objWord.Documents.Add("H:\Access\Testing\ack tribute.dot")

Set objMerge = objMergeDoc.MailMerge
objMerge.Execute
objMergeDoc.Close (wdDoNotSaveChanges)

Exit Function

ErrHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Err.Clear

End Function

Public Function funWord_Activate()

Dim objWord As Word.Application

On Error Resume Next

Set objWord = GetObject(, "Word.Application")
If Err.Number = 0 Then
objWord.Visible = True
objWord.Activate
End If

Err.Clear

End Function

- All three functions are called from a macro.

 

[Geoff]

Sorry, I have no idea why that might happen.

However, as you are calling all three functions from one macro, then your
code is a little inefficient, which might be the cause of the problem: For,
you create the same object variables three times in each of your three
functions. I would create and destroy them them just once.

Here is an example.

(Incidentally, you could assign the "Public Sub MasterRoutine()" to a button
on a custom toolbar. Alternatively, you may need to change it into a
function if you must call it from a macro.)


' Declare variables with module-wide scope
' (at the top of a module):
Private mobjWord As Word.Application
Private mobjMergeDoc As Word.Document

Public Sub MasterRoutine()

On Error GoTo ErrHandler
Call StartWord
Call MyMailmerge("H:\Access\Testing\ack regular.dot")
Call MyMailmerge("H:\Access\Testing\ack tribute.dot")
mobjWord.Activate

Bye:

' Destroy object variables:
Set mobjMergeDoc = Nothing
Set mobjWord = Nothing
Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

' The "On Error" and "Resume" statements call Err.Clear
' automatically (see the Clear Method in help):
Resume Bye

End Sub

Private Sub StartWord()
On Error Resume Next
Set mobjWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set mobjWord = CreateObject("Word.Application")
End If
End Sub

Private Sub MyMailmerge(strMainPathName As String)

Set mobjMergeDoc = mobjWord.Documents.Add(strMainPathName)

' It's faster to use the "With...End With"
' when calling out-of-process servers like Word:
With mobjMergeDoc

With .MailMerge

' Use the OpenDataSource method if you
' want to attach a data source to the
' main document at run time. You could
' pass strDataPathName to this
' subprocedure, just like strMainPathName,
' for example:
' .OpenDataSource strDataPathName

' Always check the State property before
' executing the merge as you must have
' a main document attached to a datasource
' before executing:
If .State = wdMainAndDataSource Then
.Execute
End If

End With

' Close the main document:
.Close wdDoNotSaveChanges

End With

End Sub

Regards
Geoff

 

[Guest]

The letters are independent and may not be printed that day. There is a
condition set in the macro that checks to see if there are any records in the
query that the mailmerge letter calls. This will keep the mailmerge from
opening a letter with no data in the fields. Maybe because I didn't destroy
the object variables it could be causing the problem.

Thanks for your help and additional suggestions!

 

[Geoff]

The letters are independent and may not be printed that day. There is a
condition set in the macro that checks to see if there are any records in
the
query that the mailmerge letter calls. This will keep the mailmerge from
opening a letter with no data in the fields. Maybe because I didn't
destroy
the object variables it could be causing the problem.

Thanks for your help and additional suggestions!

 

[Geoff]

It may well be that not destroying object variables that are used repeatedly
in quick succession is causing the problem. I'm afraid this is a bit of a
mystery. I suspect it is an extremely complex topic/problem. The general
advice is that you should always destroy object variables when you're
finished with them, even though VBA should destroy them automatically when
they go out of scope.

In the light of you last post, I would offer one more suggestion. Everything
you can do with a macro, you can do with VBA and it's usually better to do
it with VBA. So, to run the letters independently or together, you could
simply set up three different "master" subroutines, MergeRegular,
MergeTribute, or MergeBothRegularAndTribute. This would overcome the
problem of only setting up and destroying object variables once.
Furthermore, to check if there are any records before merging, you could
examine the RecordCount property of the DataSource object in the MyMailmerge
subprocedure:

With .MailMerge

' See if there are records to be merged:
If .DataSource.RecordCount = 0 Then
GoTo Exit_NoRecordsMessage
End If


Here's the new code:


' Declare variables with module-wide scope
' (at the top of a module):
Private mobjWord As Word.Application
Private mobjMergeDoc As Word.Document

Public Sub MergeRegular()

On Error GoTo ErrHandler
Call StartWord
Call MyMailmerge("H:\Access\Testing\ack regular.dot")
mobjWord.Activate

Bye:

' Destroy object variables:
Set mobjMergeDoc = Nothing
Set mobjWord = Nothing
Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume Bye

End Sub

Public Sub MergeTribute()

On Error GoTo ErrHandler
Call StartWord
Call MyMailmerge("H:\Access\Testing\ack tribute.dot")
mobjWord.Activate

Bye:

' Destroy object variables:
Set mobjMergeDoc = Nothing
Set mobjWord = Nothing
Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume Bye

End Sub

Public Sub MergeBothRegularAndTribute()

On Error GoTo ErrHandler
Call StartWord
Call MyMailmerge("H:\Access\Testing\ack regular.dot")
Call MyMailmerge("H:\Access\Testing\ack tribute.dot")
mobjWord.Activate

Bye:

' Destroy object variables:
Set mobjMergeDoc = Nothing
Set mobjWord = Nothing
Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume Bye

End Sub

Private Sub StartWord()
On Error Resume Next
Set mobjWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set mobjWord = CreateObject("Word.Application")
End If
End Sub

Private Sub MyMailmerge(strMainPathName As String)

Set mobjMergeDoc = mobjWord.Documents.Add(strMainPathName)

' Do stuff with mailmerge object:
With mobjMergeDoc

With .MailMerge

' See if there are records to be merged:
If .DataSource.RecordCount = 0 Then
GoTo Exit_NoRecordsMessage
End If

' Always check the State property before
' executing the merge as you must have
' a main document attached to a datasource
' before executing:
If .State = wdMainAndDataSource Then
.Execute
End If

End With

' Close the main document:
.Close wdDoNotSaveChanges

End With

Bye:

Exit Sub

Exit_NoRecordsMessage:

' Insert a message to user here if you want,
' using the MsgBox function.
GoTo Bye

End Sub


Geoff

 

[Geoff]

Sent by mistake...

 

[Guest]

Wow! Thanks for spending so much time on this subject for me. I will rewrite
my code to your specs and give it a shot. My computer at home (XP w/ Office
XP) has not problem w/ my code. However, my workstation using Office 2000
does.

I will let you know how it goes. I haven't used Access w/ VB since Access
97; mostly a SQL programmer now. However, I was trying to keep the same
functionality when I started working for my current employer.

Thanks again!