Word mailmerge - Visual Basic experts please help!

I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray


+-------------------------------------------------------------------+
|Filename: modMIBFunctions.zip |
|Download: http://www.wordbanter.com/attachment...ttachmentid=71 |
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse

What exactly are you trying to do.

Copy and paste the code from the module into the body of the message that
you post to the newsgroup.

--
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" wrote
in message ...

I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray


+-------------------------------------------------------------------+
|Filename: modMIBFunctions.zip |
|Download: http://www.wordbanter.com/attachment...ttachmentid=71 |
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse

Murray Muspratt-Rouse;358691 Wrote:
I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray

To explain: - I have recently been asked by a UK charity to help update
their system. I have 'inherited' what has been constructed for them in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that would
break the rules. I append it below.

Murray


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



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

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
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

' 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="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase 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




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
.Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


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



--
Murray Muspratt-Rouse

If you remove the

objApp.Visible = False

and perhaps replace it with

obj.App.Activate

though that may not be necessary, I think that the result of the merge will
be visible

I do not understand the following part of your code:

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

If you want to close the mail merge main document without saving it, just
use:

strDocName.Close wdDoNotSaveChanges

--
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" wrote
in message ...

Murray Muspratt-Rouse;358691 Wrote:
I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray

To explain: - I have recently been asked by a UK charity to help update
their system. I have 'inherited' what has been constructed for them in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that would
break the rules. I append it below.

Murray


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



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

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
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

' 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="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase 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




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


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



--
Murray Muspratt-Rouse

Broadly speaking you will be better off making a reference to the mail
merge main document when you open it, e.g.

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


Unless an error document has been created, when you merge to a new document,
the new document becomes the ActiveDocument, so you should be able to do
something like

objApp.Visible = True
objApp.Activate

at the end of your code. However, I cannot say I have actually tested that
with your code here.


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...

Murray Muspratt-Rouse;358691 Wrote:
I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray

To explain: - I have recently been asked by a UK charity to help update
their system. I have 'inherited' what has been constructed for them in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that would
break the rules. I append it below.

Murray


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



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

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
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

' 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="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase 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




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


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



--
Murray Muspratt-Rouse

I agree with Doug about the code he does not understand - since I am not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
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
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray

Doug Robbins - Word MVP;359242 Wrote:
If you remove the

objApp.Visible = False

and perhaps replace it with

obj.App.Activate

though that may not be necessary, I think that the result of the merge
will
be visible

I do not understand the following part of your code:

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

If you want to close the mail merge main document without saving it,
just
use:

strDocName.Close wdDoNotSaveChanges

--
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"
wrote
in message ...-

Murray Muspratt-Rouse;358691 Wrote:-
I do not speak VBA! I have managed to stop Word from displaying Save
As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying
it
as text into Word came out too large.

Many thanks in advance for your help

Murray-

To explain: - I have recently been asked by a UK charity to help
update
their system. I have 'inherited' what has been constructed for them
in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the
mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of
the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that
would
break the rules. I append it below.

Murray


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



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

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
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

' 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="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase 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




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


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



--
Murray Muspratt-Rouse -


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



--
Murray Muspratt-Rouse

Actually, I did know what the code was doing, but did not understand why.

However, I suggest that you follow the advice given by Peter Jamieson and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
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" wrote
in message ...

I agree with Doug about the code he does not understand - since I am not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
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
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray

Doug Robbins - Word MVP;359242 Wrote:
If you remove the

objApp.Visible = False

and perhaps replace it with

obj.App.Activate

though that may not be necessary, I think that the result of the merge
will
be visible

I do not understand the following part of your code:

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

If you want to close the mail merge main document without saving it,
just
use:

strDocName.Close wdDoNotSaveChanges

--
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"
wrote
in message ...-

Murray Muspratt-Rouse;358691 Wrote:-
I do not speak VBA! I have managed to stop Word from displaying Save
As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying
it
as text into Word came out too large.

Many thanks in advance for your help

Murray-

To explain: - I have recently been asked by a UK charity to help
update
their system. I have 'inherited' what has been constructed for them
in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the
mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of
the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that
would
break the rules. I append it below.

Murray


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



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

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
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

' 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="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase 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




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


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



--
Murray Muspratt-Rouse -


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



--
Murray Muspratt-Rouse

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

Doug Robbins - Word MVP;359552 Wrote: [color=blue][i]
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
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"
wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
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
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray



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



--
Murray Muspratt-Rouse

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" wrote
in message ...[color=blue][i]

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

Doug Robbins - Word MVP;359552 Wrote:
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
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"
wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
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
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray



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



--
Murray Muspratt-Rouse

I am very grateful to Peter for his advice - this morning I realised
that I should have googled objMMMD. When I did that I found some of his
postings on the web and started looking for Microsoft Office 12.0 Object
Library, Trying to reference it showed it to be missing, so I tried to
find its name and location. What I saw was cut off at c:\Program
Files\Microsoft Office\Office 12\M - and I could not make the pop up
any wider!

I have tried Dim objMMMD As Object - the result was a compile error
with a syntax error on Set objMMMD = objApp.Documents.Open
FileName:=strDocName etc. I must locate the Object Library and
regioster it, I think.

Murray

Peter Jamieson;359803 Wrote: [color=blue][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"
wrote
in message ...-

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

Doug Robbins - Word MVP;359552 Wrote:-
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead
of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
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"
wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
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
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray
-


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



--
Murray Muspratt-Rouse -


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



--
Murray Muspratt-Rouse