Word mailmerge - Visual Basic experts please help!

Here is the code as it is now. The merge document is displayed and the
user can then finish the merge.

Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'Dim objMMMD As Object
'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
..Destination = wdSendToNewDocument
..SuppressBlankLines = True
With .DataSource
..FirstRecord = .ActiveRecord
..LastRecord = .ActiveRecord
End With
..Execute Pause:=False
End With


Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


objApp.Visible = True
objApp.Activate

End Sub

I really do appreciate the help you give!

Murray

Doug Robbins - Word MVP;360676 Wrote:
> Murray,
>
> Can you post back with the complete code as you now have it.
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f9d413.RemoveThis@wordbanter.com
> wrote
> in message news:Murray.Muspratt-Rouse.2f9d413@wordbanter.com...-
>
> Doug, thank you for confirming my guess! I have in fact used 'Dim
> objMMD
> As Object' as Peter recommended - the result of a merge that should
> have
> produced 3 letters was that I got only one. For the moment I intend
> to
> leave things as they are (without the objMMD code), so that users
> will
> finish the merge with the function provided by Word 2007, unless, of
> course, there is a solution for the problem I have described!
>
> Murray
>
> Doug Robbins - Word MVP;360424 Wrote:-
> objMMMD is just a "label" assigned to a document object which by the
> command
>
> Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )
>
> is being set to the document with the file name of strDocName, which
> I
>
> believe is your mailmerge main document.
>
> having done that, you can refer to that document by the label that is
> assigned to the object.
>
> There is nothing special about the MMMD other than it conveys some
> meaning
> as you have deduced to the use of a person reading the code
>
> You could just as well have used:
>
> Dim doc as Object
> Set doc = objApp.Documents.Open (FileName: = strDocName, etc )
>
> but then doc does not convey as much intelligence to the user, other
> than it
> probably refers to a document, but which one.
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f82e3d.RemoveThis@wordbanter.com
> wrote
> in message news:Murray.Muspratt-Rouse.2f82e3d@wordbanter.com...-
>
> While thanking Peter and Doug for their advice I have to say that I
> have
> a major problem - I am not sure what objMMMD is. Does it stand for
> Mail
> Merge Main Document or something like that? If so is it meant to be
> used to identify the document set up with merge fields, so as to make
> it possible to close it without closing the result of the merge or
> whatever? If I am right then I think I understand what I must do to
> use
> it in the VBA code.
>
> Also, because the References pop-up only shows me the first character
> of the module name (C:\Program Files\Common Files\Microsoft
> Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is
> the
> right one!
>
> Murray
>
> Doug Robbins - Word MVP;360024 Wrote:-
> See Peter's previous message in which he says to use
>
> Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )
>
> Probably all that you really need of that command is:
>
> Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
> AddToRecentFiles:=False )
>
> And that is on the assumption that you do not want the document to be
> added
> to the list of most recently used files.
>
> If that is not an issue, you can simply use
>
> Set objMMMD = obj.App.Documents.Open(strDocName)
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f49c30.RemoveThis@wordbanter.com
> wrote
> in message
> news:Murray.Muspratt-Rouse.2f49c30@wordbanter.com...[color=blue][i]
>
> I think I have overcome the Tools/References problem, but now I have
> another one: - I get an compile error message telling me that End of
> Statement was expected, highlighting FileName in the statement to
> which
> I have added 'Set objMMMD ='
>
> Set objMMMD = objApp.Documents.Open FileName:=strDocName,
> ConfirmConversions:=False, _
> ReadOnly:=False, AddToRecentFiles:=False _
> , PasswordDocument:="", _
> PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
> _
> WritePasswordTemplate:="", XMLTransform:=""
>
> Murray
>
> Peter Jamieson;359803 Wrote:[color=green][i]
> That is probably because the Word Object library has not been
> referenced
> via
> the Tools|References option in Access VBA. However, the original
> author
> may
> have avoided that deliberately because when you reference a library,
> the
> reference is to a specific version of Word.
>
> You can typically work around that using the following instead (you
> just
> don't see the autocomplete Help in the VBA editor that you would
> otherwise
> get)
>
> Dim objMMMD As Object
>
> --
> Peter Jamieson
> http://tips.pjmsn.me.uk
>
> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f39f0e.RemoveThis@wordbanter.com
> wrote
> in message news:Murray.Muspratt-Rouse.2f39f0e@wordbanter.com...-
>
> Thank you Doug for referring me to Peter Jamieson's advice. I have
> immediately run in to a problem - it does not like 'objMMMD As
> Word.Document' telling me 'User-defined type not defined' and
> suggesting that it might be in a properly registered object or type
> library. Did I misunderstand Peter's advice? Was I meant to change
> objMMMD to something else?
>
> Murray-
>
>
> +-------------------------------------------------------------------+
> +-------------------------------------------------------------------+
>
>
>
> --
> Murray Muspratt-Rouse --
>
>
> +-------------------------------------------------------------------+
> +-------------------------------------------------------------------+
>
>
>
> --
> Murray Muspratt-Rouse -


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse

Try the following (watch out for line breaks that may occur in the wrong
place.) I haven't really looked at the If... Else...End If manipulations of
the Access data at the beginning to see if there is anything wrong with it,
only at the Word part of the code.

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String

On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
With ObjMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _
ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
Extended Properties="""";Jet OLEDB:System database=""""; _
Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLE",
_
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" <Murray.Muspratt-Rouse.2fad133 RemoveThis @wordbanter.com> wrote
in message news:Murray.Muspratt-Rouse.2fad133@wordbanter.com...
>
> Here is the code as it is now. The merge document is displayed and the
> user can then finish the merge.
>
> Option Compare Database
> Public lngMyEmpID As Long
> Public datDate1 As Date
> Public datDate2 As Date
> Public datDate3 As Date
>
>
>
> Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
> strFormName As String)
> On Error Resume Next
> DoCmd.OpenQuery "qrydeleteMergeTablerows"
>
>
> 'Load data to MergeTable with a query that collects the required data
> after update
>
> If strFormName = "Volunteers" Then
> If strLetterDescription = "REFERENCE REQUEST" Then
> DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
> DoCmd.OpenQuery ("qryReferees")
> Else
> If strLetterDescription = "Referee chaser" Then
> DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
> DoCmd.OpenQuery ("qryRefereechaser")
> Else
> DoCmd.OpenQuery ("qryVolunteer")
> End If
> End If
> Else
> DoCmd.OpenQuery ("qryClient")
> End If
>
> Dim objApp As Object
> Dim strCurrentFileName As String
> strCurrentFileName = CurrentDb.Name
> 'Opens the document
>
> Set objApp = CreateObject("Word.Application")
> objApp.Visible = False
> 'Dim objMMMD As Object
> 'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
> ' objApp.ChangeFileOpenDirectory "C:\Temp\"
> objApp.Documents.Open FileName:=strDocName,
> ConfirmConversions:=False, _
> ReadOnly:=False, AddToRecentFiles:=False _
> , PasswordDocument:="", _
> PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
> _
> WritePasswordTemplate:="", XMLTransform:=""
>
> 'Format:=wdOpenFormatAuto
> ' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
> 'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
> objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
> strCurrentFileName, _
> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
> _
> AddToRecentFiles:=False, PasswordDocument:="",
> PasswordTemplate:="", _
> WritePasswordDocument:="", WritePasswordTemplate:="",
> Revert:=False, _
> Connection:= _
> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
> ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
> Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
> Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
> , SQLStatement:="SELECT * FROM `mergetable`",
> SQLStatement1:="", _
> SubType:=wdMergeSubTypeAccess
> With objApp.ActiveDocument.MailMerge
> Destination = wdSendToNewDocument
> SuppressBlankLines = True
> With .DataSource
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
> End With
> Execute Pause:=False
> End With
>
>
> Dim intSplitName As Integer
> Dim intLength As Integer
> intLength = Len(strDocName)
> intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
> strDocName = Right(strDocName, intLength - intSplitName)
>
>
> objApp.Windows(strDocName).Activate
> objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
> 'objMMMD.Close SaveChanges:=False
> 'Set objMMMD = Nothing
>
>
> 'objApp.Documents.Open strDocName
>
>
> objApp.Visible = True
> objApp.Activate
>
> End Sub
>
> I really do appreciate the help you give!
>
> Murray
>
> Doug Robbins - Word MVP;360676 Wrote:
>> Murray,
>>
>> Can you post back with the complete code as you now have it.
>>
>> --
>> Hope this helps.
>>
>> Please reply to the newsgroup unless you wish to avail yourself of my
>> services on a paid consulting basis.
>>
>> Doug Robbins - Word MVP
>>
>> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f9d413 RemoveThis @wordbanter.com
>> wrote
>> in message news:Murray.Muspratt-Rouse.2f9d413@wordbanter.com...-
>>
>> Doug, thank you for confirming my guess! I have in fact used 'Dim
>> objMMD
>> As Object' as Peter recommended - the result of a merge that should
>> have
>> produced 3 letters was that I got only one. For the moment I intend
>> to
>> leave things as they are (without the objMMD code), so that users
>> will
>> finish the merge with the function provided by Word 2007, unless, of
>> course, there is a solution for the problem I have described!
>>
>> Murray
>>
>> Doug Robbins - Word MVP;360424 Wrote:-
>> objMMMD is just a "label" assigned to a document object which by the
>> command
>>
>> Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )
>>
>> is being set to the document with the file name of strDocName, which
>> I
>>
>> believe is your mailmerge main document.
>>
>> having done that, you can refer to that document by the label that is
>> assigned to the object.
>>
>> There is nothing special about the MMMD other than it conveys some
>> meaning
>> as you have deduced to the use of a person reading the code
>>
>> You could just as well have used:
>>
>> Dim doc as Object
>> Set doc = objApp.Documents.Open (FileName: = strDocName, etc )
>>
>> but then doc does not convey as much intelligence to the user, other
>> than it
>> probably refers to a document, but which one.
>>
>> --
>> Hope this helps.
>>
>> Please reply to the newsgroup unless you wish to avail yourself of my
>> services on a paid consulting basis.
>>
>> Doug Robbins - Word MVP
>>
>> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f82e3d RemoveThis @wordbanter.com
>> wrote
>> in message news:Murray.Muspratt-Rouse.2f82e3d@wordbanter.com...-
>>
>> While thanking Peter and Doug for their advice I have to say that I
>> have
>> a major problem - I am not sure what objMMMD is. Does it stand for
>> Mail
>> Merge Main Document or something like that? If so is it meant to be
>> used to identify the document set up with merge fields, so as to make
>> it possible to close it without closing the result of the merge or
>> whatever? If I am right then I think I understand what I must do to
>> use
>> it in the VBA code.
>>
>> Also, because the References pop-up only shows me the first character
>> of the module name (C:\Program Files\Common Files\Microsoft
>> Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is
>> the
>> right one!
>>
>> Murray
>>
>> Doug Robbins - Word MVP;360024 Wrote:-
>> See Peter's previous message in which he says to use
>>
>> Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )
>>
>> Probably all that you really need of that command is:
>>
>> Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
>> AddToRecentFiles:=False )
>>
>> And that is on the assumption that you do not want the document to be
>> added
>> to the list of most recently used files.
>>
>> If that is not an issue, you can simply use
>>
>> Set objMMMD = obj.App.Documents.Open(strDocName)
>>
>> --
>> Hope this helps.
>>
>> Please reply to the newsgroup unless you wish to avail yourself of my
>> services on a paid consulting basis.
>>
>> Doug Robbins - Word MVP
>>
>> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f49c30 RemoveThis @wordbanter.com
>> wrote
>> in message
>> news:Murray.Muspratt-Rouse.2f49c30@wordbanter.com...[color=blue][i]
>>
>> I think I have overcome the Tools/References problem, but now I have
>> another one: - I get an compile error message telling me that End of
>> Statement was expected, highlighting FileName in the statement to
>> which
>> I have added 'Set objMMMD ='
>>
>> Set objMMMD = objApp.Documents.Open FileName:=strDocName,
>> ConfirmConversions:=False, _
>> ReadOnly:=False, AddToRecentFiles:=False _
>> , PasswordDocument:="", _
>> PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
>> _
>> WritePasswordTemplate:="", XMLTransform:=""
>>
>> Murray
>>
>> Peter Jamieson;359803 Wrote:[color=green][i]
>> That is probably because the Word Object library has not been
>> referenced
>> via
>> the Tools|References option in Access VBA. However, the original
>> author
>> may
>> have avoided that deliberately because when you reference a library,
>> the
>> reference is to a specific version of Word.
>>
>> You can typically work around that using the following instead (you
>> just
>> don't see the autocomplete Help in the VBA editor that you would
>> otherwise
>> get)
>>
>> Dim objMMMD As Object
>>
>> --
>> Peter Jamieson
>> http://tips.pjmsn.me.uk
>>
>> "Murray Muspratt-Rouse" Murray.Muspratt-Rouse.2f39f0e RemoveThis @wordbanter.com
>> wrote
>> in message news:Murray.Muspratt-Rouse.2f39f0e@wordbanter.com...-
>>
>> Thank you Doug for referring me to Peter Jamieson's advice. I have
>> immediately run in to a problem - it does not like 'objMMMD As
>> Word.Document' telling me 'User-defined type not defined' and
>> suggesting that it might be in a properly registered object or type
>> library. Did I misunderstand Peter's advice? Was I meant to change
>> objMMMD to something else?
>>
>> Murray-
>>
>>
>> +-------------------------------------------------------------------+
>> +-------------------------------------------------------------------+
>>
>>
>>
>> --
>> Murray Muspratt-Rouse --
>>
>>
>> +-------------------------------------------------------------------+
>> +-------------------------------------------------------------------+
>>
>>
>>
>> --
>> Murray Muspratt-Rouse -
>
>
> +-------------------------------------------------------------------+
> +-------------------------------------------------------------------+
>
>
>
> --
> Murray Muspratt-Rouse

I have implemented Doug's suggestions, but, while the code ran without
error, nothing was displayed - the application returned to the form in
MS Access from which the merge was initiated. The code that ran is
below: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
..MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
..Destination = wdSendToNewDocument
..SuppressBlankLines = True
With .DataSource
..FirstRecord = .ActiveRecord
..LastRecord = .ActiveRecord
End With
..Execute Pause:=False
..Close wdDoNotSaveChanges
End With


'Dim intSplitName As Integer
'Dim intLength As Integer
'intLength = Len(strDocName)
'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
'strDocName = Right(strDocName, intLength - intSplitName)


'objApp.Windows(strDocName).Activate
'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


'objApp.Visible = True
'objApp.Activate

End Sub

Murray

Doug Robbins - Word MVP;360910 Wrote:
> Try the following (watch out for line breaks that may occur in the wrong
>
> place.) I haven't really looked at the If... Else...End If
> manipulations of
> the Access data at the beginning to see if there is anything wrong with
> it,
> only at the Word part of the code.
>
> Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
> strFormName As String)
> Dim objApp As Object
> Dim objMMMD As Object
> Dim strCurrentFileName As String
>
> On Error Resume Next
> DoCmd.OpenQuery "qrydeleteMergeTablerows"
>
>
> 'Load data to MergeTable with a query that collects the required data
> after update
>
> If strFormName = "Volunteers" Then
> If strLetterDescription = "REFERENCE REQUEST" Then
> DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
> DoCmd.OpenQuery ("qryReferees")
> Else
> If strLetterDescription = "Referee chaser" Then
> DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
> DoCmd.OpenQuery ("qryRefereechaser")
> Else
> DoCmd.OpenQuery ("qryVolunteer")
> End If
> End If
> Else
> DoCmd.OpenQuery ("qryClient")
> End If
>
> strCurrentFileName = CurrentDb.Name
> 'Opens the document
>
> Set objApp = CreateObject("Word.Application")
> objApp.Activate
> Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
> With ObjMMMD
> .MailMerge.OpenDataSource Name:= _
> strCurrentFileName, _
> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
> AddToRecentFiles:=False, PasswordDocument:="",
> PasswordTemplate:="", _
> WritePasswordDocument:="", WritePasswordTemplate:="",
> Revert:=False, _
> Connection:= _
> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _
> ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
> Extended Properties="""";Jet OLEDB:System database=""""; _
> Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet
> OLE",
> _
> SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
> SubType:=wdMergeSubTypeAccess
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
> End With
> .Execute Pause:=False
> .Close wdDoNotSaveChanges
> End With
>
> End Sub
>
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
> --
>


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse

I can see why you would comment out

'objApp.Visible = False

But why are you commenting out all of the other lines of code?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" <Murray.Muspratt-Rouse.2fc770d DeleteThis @wordbanter.com> wrote
in message news:Murray.Muspratt-Rouse.2fc770d@wordbanter.com...
>
> I have implemented Doug's suggestions, but, while the code ran without
> error, nothing was displayed - the application returned to the form in
> MS Access from which the merge was initiated. The code that ran is
> below: -
>
> Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
> strFormName As String)
> Dim objApp As Object
> Dim objMMMD As Object
> Dim strCurrentFileName As String
> On Error Resume Next
> DoCmd.OpenQuery "qrydeleteMergeTablerows"
>
>
> 'Load data to MergeTable with a query that collects the required data
> after update
>
> If strFormName = "Volunteers" Then
> If strLetterDescription = "REFERENCE REQUEST" Then
> DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
> DoCmd.OpenQuery ("qryReferees")
> Else
> If strLetterDescription = "Referee chaser" Then
> DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
> DoCmd.OpenQuery ("qryRefereechaser")
> Else
> DoCmd.OpenQuery ("qryVolunteer")
> End If
> End If
> Else
> DoCmd.OpenQuery ("qryClient")
> End If
>
> strCurrentFileName = CurrentDb.Name
>
> Set objApp = CreateObject("Word.Application")
> 'objApp.Visible = False
> objApp.Activate
> 'Dim objMMMD As Object
> Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
>
> 'objApp.Documents.Open FileName:=strDocName,
> ConfirmConversions:=False, _
> 'ReadOnly:=False, AddToRecentFiles:=False _
> ', PasswordDocument:="", _
> 'PasswordTemplate:="", Revert:=False,
> WritePasswordDocument:="", _
> 'WritePasswordTemplate:="", XMLTransform:=""
>
> 'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
>
> With objMMMD
> MailMerge.OpenDataSource Name:=strCurrentFileName, _
> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
> _
> AddToRecentFiles:=False, PasswordDocument:="",
> PasswordTemplate:="", _
> WritePasswordDocument:="", WritePasswordTemplate:="",
> Revert:=False, _
> Connection:= _
> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
> ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
> Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
> Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
> , SQLStatement:="SELECT * FROM `mergetable`",
> SQLStatement1:="", _
> SubType:=wdMergeSubTypeAccess
> Destination = wdSendToNewDocument
> SuppressBlankLines = True
> With .DataSource
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
> End With
> Execute Pause:=False
> Close wdDoNotSaveChanges
> End With
>
>
> 'Dim intSplitName As Integer
> 'Dim intLength As Integer
> 'intLength = Len(strDocName)
> 'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
> 'strDocName = Right(strDocName, intLength - intSplitName)
>
>
> 'objApp.Windows(strDocName).Activate
> 'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
> 'objMMMD.Close SaveChanges:=False
> 'Set objMMMD = Nothing
>
>
> 'objApp.Documents.Open strDocName
>
>
> 'objApp.Visible = True
> 'objApp.Activate
>
> End Sub
>
> Murray
>
> Doug Robbins - Word MVP;360910 Wrote:
>> Try the following (watch out for line breaks that may occur in the wrong
>>
>> place.) I haven't really looked at the If... Else...End If
>> manipulations of
>> the Access data at the beginning to see if there is anything wrong with
>> it,
>> only at the Word part of the code.
>>
>> Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
>> strFormName As String)
>> Dim objApp As Object
>> Dim objMMMD As Object
>> Dim strCurrentFileName As String
>>
>> On Error Resume Next
>> DoCmd.OpenQuery "qrydeleteMergeTablerows"
>>
>>
>> 'Load data to MergeTable with a query that collects the required data
>> after update
>>
>> If strFormName = "Volunteers" Then
>> If strLetterDescription = "REFERENCE REQUEST" Then
>> DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
>> DoCmd.OpenQuery ("qryReferees")
>> Else
>> If strLetterDescription = "Referee chaser" Then
>> DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
>> DoCmd.OpenQuery ("qryRefereechaser")
>> Else
>> DoCmd.OpenQuery ("qryVolunteer")
>> End If
>> End If
>> Else
>> DoCmd.OpenQuery ("qryClient")
>> End If
>>
>> strCurrentFileName = CurrentDb.Name
>> 'Opens the document
>>
>> Set objApp = CreateObject("Word.Application")
>> objApp.Activate
>> Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
>> With ObjMMMD
>> .MailMerge.OpenDataSource Name:= _
>> strCurrentFileName, _
>> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
>> AddToRecentFiles:=False, PasswordDocument:="",
>> PasswordTemplate:="", _
>> WritePasswordDocument:="", WritePasswordTemplate:="",
>> Revert:=False, _
>> Connection:= _
>> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _
>> ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
>> Extended Properties="""";Jet OLEDB:System database=""""; _
>> Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet
>> OLE",
>> _
>> SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
>> SubType:=wdMergeSubTypeAccess
>> .Destination = wdSendToNewDocument
>> .SuppressBlankLines = True
>> With .DataSource
>> FirstRecord = .ActiveRecord
>> LastRecord = .ActiveRecord
>> End With
>> .Execute Pause:=False
>> .Close wdDoNotSaveChanges
>> End With
>>
>> End Sub
>>
>>
>> --
>> Hope this helps.
>>
>> Please reply to the newsgroup unless you wish to avail yourself of my
>> services on a paid consulting basis.
>>
>> Doug Robbins - Word MVP
>> --
>>
>
>
> +-------------------------------------------------------------------+
> +-------------------------------------------------------------------+
>
>
>
> --
> Murray Muspratt-Rouse

The reason I commented out all the other lines of code was so as to make
it easier to get back to where I was if the recommendations did not
succeed in solving the problem. I will back up the database and do it
again, removing all the code not included in your recommendations.
Then, if it fails again, I will be able to restore the module from the
backup.

Murray

Doug Robbins - Word MVP;361176 Wrote:
> I can see why you would comment out
>
> 'objApp.Visible = False
>
> But why are you commenting out all of the other lines of code?
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
>


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse

There are some significant differences in this section of the code in your
message from that which I provided, that would cause it not to compile
correctly:

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" <Murray.Muspratt-Rouse.2fc770d RemoveThis @wordbanter.com> wrote
in message news:Murray.Muspratt-Rouse.2fc770d@wordbanter.com...
>
> I have implemented Doug's suggestions, but, while the code ran without
> error, nothing was displayed - the application returned to the form in
> MS Access from which the merge was initiated. The code that ran is
> below: -
>
> Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
> strFormName As String)
> Dim objApp As Object
> Dim objMMMD As Object
> Dim strCurrentFileName As String
> On Error Resume Next
> DoCmd.OpenQuery "qrydeleteMergeTablerows"
>
>
> 'Load data to MergeTable with a query that collects the required data
> after update
>
> If strFormName = "Volunteers" Then
> If strLetterDescription = "REFERENCE REQUEST" Then
> DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
> DoCmd.OpenQuery ("qryReferees")
> Else
> If strLetterDescription = "Referee chaser" Then
> DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
> DoCmd.OpenQuery ("qryRefereechaser")
> Else
> DoCmd.OpenQuery ("qryVolunteer")
> End If
> End If
> Else
> DoCmd.OpenQuery ("qryClient")
> End If
>
> strCurrentFileName = CurrentDb.Name
>
> Set objApp = CreateObject("Word.Application")
> 'objApp.Visible = False
> objApp.Activate
> 'Dim objMMMD As Object
> Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
>
> 'objApp.Documents.Open FileName:=strDocName,
> ConfirmConversions:=False, _
> 'ReadOnly:=False, AddToRecentFiles:=False _
> ', PasswordDocument:="", _
> 'PasswordTemplate:="", Revert:=False,
> WritePasswordDocument:="", _
> 'WritePasswordTemplate:="", XMLTransform:=""
>
> 'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
>
> With objMMMD
> MailMerge.OpenDataSource Name:=strCurrentFileName, _
> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
> _
> AddToRecentFiles:=False, PasswordDocument:="",
> PasswordTemplate:="", _
> WritePasswordDocument:="", WritePasswordTemplate:="",
> Revert:=False, _
> Connection:= _
> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
> ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
> Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
> Path="""";Jet OLEDB:Database Password="""";Jet OLE" _
> , SQLStatement:="SELECT * FROM `mergetable`",
> SQLStatement1:="", _
> SubType:=wdMergeSubTypeAccess
> Destination = wdSendToNewDocument
> SuppressBlankLines = True
> With .DataSource
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
> End With
> Execute Pause:=False
> Close wdDoNotSaveChanges
> End With
>
>
> 'Dim intSplitName As Integer
> 'Dim intLength As Integer
> 'intLength = Len(strDocName)
> 'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
> 'strDocName = Right(strDocName, intLength - intSplitName)
>
>
> 'objApp.Windows(strDocName).Activate
> 'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
> 'objMMMD.Close SaveChanges:=False
> 'Set objMMMD = Nothing
>
>
> 'objApp.Documents.Open strDocName
>
>
> 'objApp.Visible = True
> 'objApp.Activate
>
> End Sub
>
> Murray
>
> Doug Robbins - Word MVP;360910 Wrote:
>> Try the following (watch out for line breaks that may occur in the wrong
>>
>> place.) I haven't really looked at the If... Else...End If
>> manipulations of
>> the Access data at the beginning to see if there is anything wrong with
>> it,
>> only at the Word part of the code.
>>
>> Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
>> strFormName As String)
>> Dim objApp As Object
>> Dim objMMMD As Object
>> Dim strCurrentFileName As String
>>
>> On Error Resume Next
>> DoCmd.OpenQuery "qrydeleteMergeTablerows"
>>
>>
>> 'Load data to MergeTable with a query that collects the required data
>> after update
>>
>> If strFormName = "Volunteers" Then
>> If strLetterDescription = "REFERENCE REQUEST" Then
>> DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
>> DoCmd.OpenQuery ("qryReferees")
>> Else
>> If strLetterDescription = "Referee chaser" Then
>> DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
>> DoCmd.OpenQuery ("qryRefereechaser")
>> Else
>> DoCmd.OpenQuery ("qryVolunteer")
>> End If
>> End If
>> Else
>> DoCmd.OpenQuery ("qryClient")
>> End If
>>
>> strCurrentFileName = CurrentDb.Name
>> 'Opens the document
>>
>> Set objApp = CreateObject("Word.Application")
>> objApp.Activate
>> Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
>> With ObjMMMD
>> .MailMerge.OpenDataSource Name:= _
>> strCurrentFileName, _
>> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
>> AddToRecentFiles:=False, PasswordDocument:="",
>> PasswordTemplate:="", _
>> WritePasswordDocument:="", WritePasswordTemplate:="",
>> Revert:=False, _
>> Connection:= _
>> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _
>> ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
>> Extended Properties="""";Jet OLEDB:System database=""""; _
>> Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet
>> OLE",
>> _
>> SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _