Error 5631 in Mail Merge

DoctorNadir · #1 12-12-2011, 01:17 PM

I developed an userform that adds records to an Excel table, saves it, then calls Word to print a mail merge of last, just added record.

The userform has worked perfectly, growing the table regularly to over 230 records, then suddenly has started to halt with a 5631 Error (data not present).
I tried to rebuild the table, record by record; I re-created from scratch the Word document and tried to relink it to the table, but it 'sees' only the first 59 records, so any new is 'not present'.
I run out of ideas, what could have happened?

macropod · #2 12-14-2011, 02:34 PM

Hi DoctorNadir,

Did you close any previous instance of the mailmerge document before attempting a merge with the new data?

DoctorNadir · #3 12-15-2011, 04:59 AM

Yes, the userform writes to the Excel table and closes it, then calls up the Word template that executes the SQL query. The userform in itself DOES NOT open the table.
The catch is that the UF worked perfectly well until now, and that same routine works well in other similar files. I thought it could be a corruption in the table, so I rebuilt it, but to no avail.

macropod · #4 12-16-2011, 04:03 AM

Hi DoctorNadir,

I know you said your routine saves the Excel wb before the mailmerge process is initiated, but I suspect there's in an error in the save process (eg it fails, or saves the file to the wrong location). See: http://support.microsoft.com/kb/828388

DoctorNadir · #5 12-16-2011, 07:42 AM

Hi macropod,
thank you for assisting me
I had already read that kb, but it should not apply to my case, because the excel table (the oDataDoc in the MS example) is closed.
The
Have time to take a look at my code? (the point rows are code of no importance)

Code:

Private Sub cmdRegistraVerbale_Click()
......................................
Workbooks.Open Filename:=archivio
......................................
numero = ActiveCell.Value
......................................
Workbooks(archivio).Save                     ' i know it's useless (could use 'true' in next)
Workbooks(archivio).Close (False)          ' but who knows...
......................................
On Error Resume Next
        
       Set wd = GetObject(, "Word.Application")
       If wd Is Nothing Then
          Set wd = CreateObject("Word.Application")
       End If
    
       On Error GoTo 0

       Set wdocSource = wd.Documents.Open(modverbale)
    
       wdocSource.MailMerge.MainDocumentType = wdFormLetters

       With wdocSource.MailMerge
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
               .FirstRecord = numero
               .LastRecord = numero
          End With
          .Execute Pause:=False                 ' error 5631 here
       End With

       wdocSource.MailMerge.DataSource.Close
    
       wd.Visible = True

       With wd.Documents(wd.ActiveDocument.FullName)
           .SaveAs ("newfile.doc")
           .PrintOut
           .Close
       End With

       wdocSource.Close SaveChanges:=False
    
       wd.NormalTemplate.Saved = True

'archivio' is an .xls file (1 sheet)
'modverbale' the .doc file in which the links to the table are embedded
'numero' the record number (the last one) to be merged.

the 'archivio' seems to be saved correctly, the last record is there.
I tried to change the name to both files, creating from scratch the new .doc and thus rebuilding the SQL link: at first, it seems to work (the MailMerge wizard sees all the records), but the sub stops at the very same place

macropod · #6 12-16-2011, 08:59 AM

Hi DoctorNadir,

With your workbook, you don't appear to define which worksheet to obtain the 'numero' value from and, moreover, you rely on the ActiveCell being the correct one. Consequently, you could be getting the value from the wrong cell in the wrong sheet! You need a better way of defining the numero value, such as:

Code:

With Workbooks(archivio)
  numero = .Worksheets("Worksheetname").Cells(.Rows.Count, 1).End(xlUp).Row - 1
  .Close SaveChanges:=True
End With

With this, you're actually retrieving the row number of the last-used row in column A (you could change the '1' in Cells(.Rows.Count, 1) for a different column). The '- 1' deducts the header row from the count, to get the record #.

Alternatively you could have the code point to a particular cell, irrespective of whether that cell is active:
numero = .Worksheets("Worksheetname").Range("A1").Value
but you should then test whether the source cell has a valid value (eg not blank or text and not out-of-bounds).

Also, unless the document has been disconnected from the datasource, you shouldn't need the line:
wdocSource.MailMerge.MainDocumentType = wdFormLetters
If the document has been disconnected from the datasource, you need more code for the SQL statement, etc. I also can't see why you'd need the line:
.NormalTemplate.Saved = True
since you're not apparently modifying the Normal template.

Aside from the above, there's no apparent errors in the code, though I'd be more inclined to use:

Code:

Private Sub cmdRegistraVerbale_Click()
'......................................
Workbooks.Open Filename:=archivio
'......................................
With Workbooks(archivio)
  numero = .Worksheets("Worksheetname").Cells(.Rows.Count, 1).End(xlUp).Row - 1
  .Close SaveChanges:=True
End With
'......................................
On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then Set wd = CreateObject("Word.Application")
'......................................
On Error GoTo 0
With wd
  Set wdocSource = .Documents.Open(modverbale)
  With wdocSource.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = numero
      .LastRecord = numero
    End With
    .Execute Pause:=False
    .DataSource.Close
    .Close SaveChanges:=False
  End With
  .Visible = True
  With .ActiveDocument
    .SaveAs ("newfile.doc")
    .PrintOut
    .Close
  End With
End With
End Sub

DoctorNadir · #7 12-16-2011, 12:17 PM

Hi macropod,
your code is much more elegant and faster than mine, and I'll remember to use it elsewhere, but the problem wasn't in the 'numero' value, that was the first thing I checked...
so if the problem is not the code, where could it be?
In the new copy of the files, the MailMerge wizard sees all the records, then I can select the fields to embed in the template, save everything and... error 5631 still jumps out! and a week ago that same code, with the original files, worked flawlessly! I don't know what to think!

macropod · #8 12-16-2011, 04:38 PM

Hi DoctorNadir,

If you're getting the same error with the mailmerge wizard, that suggests the problem is at a lower level within Office. I'd suggest repairing Office via Word Options > Resources > Diagnose as a first step, then seeing how you get on.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge error message	rec	Mail Merge	1	04-29-2011 10:30 PM
Mail merge error?	SRE	Outlook	0	01-11-2010 05:54 PM
Error: general mail failure. Quit excel restart mail system	MitchellDM	Outlook	1	12-19-2008 02:05 AM
Error Message in Mail Merge	A Bhavani	Mail Merge	0	11-26-2008 12:08 PM
Mail Merge Calculation Error	Lsbutler2000	Mail Merge	1	06-22-2007 06:33 AM