| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Office 2003 mailmerge only produces 1 letter instead of 2 or more
Having installed Office 2003 on another PC I would like to revisit a problem I have reported before. The following code processes all letter merge requests, starting with the deletion of the contents of MergeTable, which provides the merge data, and the running of Access 2003 queries to load what is required: -
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 ("qryUpdateVolunteerRefereeLetterDate") DoCmd.OpenQuery ("qryAppendVolunteerRefereedata") Else If strLetterDescription = "Referee chaser" Then DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate") DoCmd.OpenQuery ("qryVolunteerRefereechaser") Else If strLetterDescription = "TRAINING DATES" Then DoCmd.RunMacro ("Set up training dates data for merge") Else DoCmd.OpenQuery ("qryVolunteer") End If End If End If End If If strFormName = "Clients" Then If strLetterDescription = "REFERENCE CLIENT" Then DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate") DoCmd.OpenQuery ("qryAppendClientRefereedata") Else If strLetterDescription = "Client Referee chaser" Then DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate") DoCmd.OpenQuery ("qryClientRefereechaser") Else DoCmd.OpenQuery ("qryClient") End If End If End If The code which follows does the merge - I think! strCurrentFileName = CurrentDb.Name Set objApp = CreateObject("Word.Application") objApp.Visible = False 'objApp.Activate '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:= _ 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 OLEDB  atabase 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 The code which follows must be what closes the merge document and leaves the result of the merge. I'm afraid I do not understand it at all - I suppose I must get a good VBA programming guide (suggestions welcome!) 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 If the result is meant to be more than one letter only one appears. The only way to get all the letters out is to open the marge document in Word, identify the Access table - again! - and complete the merge. All suggestions gratefully received! Murray |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Office 2003 mailmerge only produces 1 letter instead of 2 or more
I would think that your problem is with the following lines of code
FirstRecord = .ActiveRecord LastRecord = .ActiveRecord Which set the first and the last record to be merged to the active record - i.e. one record. -- 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 ... Having installed Office 2003 on another PC I would like to revisit a problem I have reported before. The following code processes all letter merge requests, starting with the deletion of the contents of MergeTable, which provides the merge data, and the running of Access 2003 queries to load what is required: - 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 ("qryUpdateVolunteerRefereeLetterDate") DoCmd.OpenQuery ("qryAppendVolunteerRefereedata") Else If strLetterDescription = "Referee chaser" Then DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate") DoCmd.OpenQuery ("qryVolunteerRefereechaser") Else If strLetterDescription = "TRAINING DATES" Then DoCmd.RunMacro ("Set up training dates data for merge") Else DoCmd.OpenQuery ("qryVolunteer") End If End If End If End If If strFormName = "Clients" Then If strLetterDescription = "REFERENCE CLIENT" Then DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate") DoCmd.OpenQuery ("qryAppendClientRefereedata") Else If strLetterDescription = "Client Referee chaser" Then DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate") DoCmd.OpenQuery ("qryClientRefereechaser") Else DoCmd.OpenQuery ("qryClient") End If End If End If The code which follows does the merge - I think! strCurrentFileName = CurrentDb.Name Set objApp = CreateObject("Word.Application") objApp.Visible = False 'objApp.Activate '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:= _ 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 OLEDB atabase 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 The code which follows must be what closes the merge document and leaves the result of the merge. I'm afraid I do not understand it at all - I suppose I must get a good VBA programming guide (suggestions welcome!) 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 If the result is meant to be more than one letter only one appears. The only way to get all the letters out is to open the marge document in Word, identify the Access table - again! - and complete the merge. All suggestions gratefully received! Murray -- Murray Muspratt-Rouse |
|
#3
|
|||
|
|||
|
[quote=Doug Robbins - Word MVP;373990]I would think that your problem is with the following lines of code
FirstRecord = .ActiveRecord LastRecord = .ActiveRecord Which set the first and the last record to be merged to the active record - i.e. one record. -- 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 Thank you for that, Doug. That's what I thought! Can I just use quotes to remove "With .DataSource FirstRecord = .ActiveRecord LastRecord = .ActiveRecord End With"? Not a good idea! It locked the database... The odd thing is that running the merge under Office 2007 on my PC (which the client insisted that I install, despite the fact that the database is installed under Office 2003) I am able to display the recipients and print the letters - all of them! Perhaps the best solution would be to leave the main document open, so that the users can complete the merge themselves? How would that change the code? Murray Last edited by Murray Muspratt-Rouse : October 25th 08 at 09:21 AM |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Office 2003 mailmerge only produces 1 letter instead of 2 or more
Use an apostrophe at the beginning of each line to convert it into a
comment. -- 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 ... Doug Robbins - Word MVP;373990 Wrote: I would think that your problem is with the following lines of code FirstRecord = .ActiveRecord LastRecord = .ActiveRecord Which set the first and the last record to be merged to the active record - i.e. one record. -- 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 Thank you for that, Doug. That's what I thought! Can I just use quotes to remove "With .DataSource FirstRecord = .ActiveRecord LastRecord = .ActiveRecord End With"? Not a good idea! It locked the database... The odd thing is that running the merge under Office 2007 on my PC (which the client insisted that I install, despite the fact that the database is installed under Office 2003) I am able to display the recipients and print the letters - all of them! Perhaps the best solution would be to leave the main document open, so that the users can complete the merge themselves? How would that change the code? Murray -- Murray Muspratt-Rouse |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Office 2003 mailmerge only produces 1 letter instead of 2 or more
Try
With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False 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" wrote in message ... That's just what I did! Please note what I said about the same code producing all the letters when run with Word 2007. I have ordered a copy of Access 2003 Vba Programmer's Reference (Wrox Press) (Programmer to Programmer) in the hopes of learning a bit more about VBA. Learn something new every day - even when you are as old as me - a few days short of 79! Murray Doug Robbins - Word MVP;374051 Wrote: Use an apostrophe at the beginning of each line to convert it into a comment. -- 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 ...- Doug Robbins - Word MVP;373990 Wrote:- I would think that your problem is with the following lines of code FirstRecord = .ActiveRecord LastRecord = .ActiveRecord Which set the first and the last record to be merged to the active record - i.e. one record. -- 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 Thank you for that, Doug. That's what I thought! Can I just use quotes to remove "With .DataSource FirstRecord = .ActiveRecord LastRecord = .ActiveRecord End With"? Not a good idea! It locked the database... The odd thing is that running the merge under Office 2007 on my PC (which the client insisted that I install, despite the fact that the database is installed under Office 2003) I am able to display the recipients and print the letters - all of them! Perhaps the best solution would be to leave the main document open, so that the users can complete the merge themselves? How would that change the code? Murray- -- Murray Muspratt-Rouse - -- Murray Muspratt-Rouse |
|
#7
|
|||
|
|||
|
Doug, I have tried your suggestion. "wdDefaultFirst/LastRecord" seemed to prevent the display of any result. Removing objapp from "With objapp.ActiveDocument.MailMerge" had the same effect. Having run the Access process to get only the first copy I then opened the document with Word, asked for the merge data to be loaded, and got all the letters. That is why I said I thought I would try to leave the Word window open, so that after Access had returned to the form from which the process is triggered it would be easy to produce all the letters.
I have suggested to the client that we should install Office 2007! Murray [quote=Doug Robbins - Word MVP;374110]Try With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False 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 |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Office 2003 mailmerge only produces 1 letter instead of 2 or more
Or just
With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .Execute 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 "Doug Robbins - Word MVP" wrote in message ... Try With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False 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" wrote in message ... That's just what I did! Please note what I said about the same code producing all the letters when run with Word 2007. I have ordered a copy of Access 2003 Vba Programmer's Reference (Wrox Press) (Programmer to Programmer) in the hopes of learning a bit more about VBA. Learn something new every day - even when you are as old as me - a few days short of 79! Murray Doug Robbins - Word MVP;374051 Wrote: Use an apostrophe at the beginning of each line to convert it into a comment. -- 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 ...- Doug Robbins - Word MVP;373990 Wrote:- I would think that your problem is with the following lines of code FirstRecord = .ActiveRecord LastRecord = .ActiveRecord Which set the first and the last record to be merged to the active record - i.e. one record. -- 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 Thank you for that, Doug. That's what I thought! Can I just use quotes to remove "With .DataSource FirstRecord = .ActiveRecord LastRecord = .ActiveRecord End With"? Not a good idea! It locked the database... The odd thing is that running the merge under Office 2007 on my PC (which the client insisted that I install, despite the fact that the database is installed under Office 2003) I am able to display the recipients and print the letters - all of them! Perhaps the best solution would be to leave the main document open, so that the users can complete the merge themselves? How would that change the code? Murray- -- Murray Muspratt-Rouse - -- Murray Muspratt-Rouse |
|
#9
|
|||
|
|||
|
Doug, I realised I had to add another reference to VBA - the Word OLB. I also had to 'compact and repair' the database. Now it works with wdDefaultFirstRecord and wdDefaultLastRecord.
Thank you again for all your efforts. Murray [quote=Doug Robbins - Word MVP;374138]Or just With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .Execute 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 "Doug Robbins - Word MVP" wrote in message ...[color=blue][i] Try With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False 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 Last edited by Murray Muspratt-Rouse : October 27th 08 at 10:47 AM |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mailmerge Problem with Word in Office 2003 | Mailmerge | |||
| Mailmerge Word 2003 - multipage letter first page only to print on letterhead | Mailmerge | |||
| Mailmerge Errors with Office 2003 | Mailmerge | |||
| Office 97 mailmerge vs. Office 2003 | Mailmerge | |||
| mailmerge word.basic in Office 2003 | Mailmerge | |||
Linear Mode
