When using a 'Master' template, if number of records change, subsequent records will not be read

MailMergeConfused · #1 07-25-2016, 04:22 AM

Background:

I have a simple excel report each day containing 10 - 30 rows of data, and each of those rows contain a email address which that row of data needs to be emailed to. I setup a simple word document to read this report, and have a VBA macro that takes the word document and feeds it into outlook so each recipient receives an email with a custom subject and the data they require.

Problem:

As I have some not so tech savvy colleagues, things are setup so they can open the template excel file, paste in their data, open the word document, hit a button and the macro does everything else for them.

I've found though that if the report one day only contains say 5 rows of data, and after they are done they hit save on the word document rather than closing without saving, the next time they open the word document containing the mail merge, it is expecting only 5 records, so even if the excel file has had 50 lines of data pasted into it, only the first 5 will be read.

I cannot seem to get word to read the additional rows, even if I unlink the data source and relink it again. The only workaround is to save the excel file under a different filename and have my word document read it as a new data source.

Attempted short version of problem:

If a word mailmerge document is expecting an excel file with 5 rows, but it has been recently amended and now contains 20 rows, word will only read the first 5 rows.

I feel like there should be an easy fix for this but cannot for the life of me figure it out, so any help is appreciated!

DougMVP · #2 07-28-2016, 12:42 AM

It may help if you post the code that you have in the macro as the behaviour that you report is definitely not normal.

MailMergeConfused · #3 07-28-2016, 01:27 AM

Quote:

Originally Posted by DougMVP

It may help if you post the code that you have in the macro as the behaviour that you report is definitely not normal.

Code:

Dim WithEvents wdapp As Application
Dim EMAIL_SUBJECT As String
Dim FIRST_RECORD As Boolean


Private Sub Document_Open()

    Set wdapp = Application
    ThisDocument.MailMerge.ShowWizard 1
   
End Sub

Private Sub Document_Close()

    Set wdapp = Nothing
    
End Sub


Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
    Dim i As Integer
    
    With ActiveDocument.MailMerge
    
        If FIRST_RECORD = True Then
            EMAIL_SUBJECT = .MailSubject
            FIRST_RECORD = False
        Else: .MailSubject = EMAIL_SUBJECT
        End If

        i = .DataSource.DataFields.Count
        
        Do While i > 0
            .MailSubject = Replace(.MailSubject, "<" & .DataSource.DataFields(i).Name & ">", .DataSource.DataFields(i).Value, , , vbTextCompare)
            i = i - 1
        Loop

    End With
    
     
End Sub


Private Sub wdapp_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)

    FIRST_RECORD = True
    
End Sub

Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)

    ActiveDocument.MailMerge.MailSubject = EMAIL_SUBJECT
     
End Sub

DougMVP · #4 07-28-2016, 03:24 AM

It does not appear that the code that you posted is actually executing the merge.

You might consider using the Merge with Attachments (attachments are not necessary) facility on my Merge Tools Add-in that you can download from:

http://bit.ly/1hduSCB
Do NOT open the MergeTools – 20150422.dotm file. (There are no user serviceable parts inside and it should NOT be used as the basis for creating your mail merge main document.)
The MergeTools – 20150422.dotm file needs to be saved in the Word Startup folder. In Windows Vista and Windows 7, 8 or 8.1, and 10, the default location for that folder is

C:\Users\[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

If you do not see the AppData folder: -

In Windows 7, - In Windows Explorer, click on the Organize drop down and then on Folder and search options and in the Folder Options dialog, go to the View tab and select the item "Show hidden files, folders, and drives". While there, it is a good idea to uncheck the box for "Hide extensions for known file types".

In Windows 8,8.1 or 10, in the File Explorer, click on Options on the View tab of the ribbon and then on the View tab in the dialog that appears and select the item "Show hidden files, folders, and drives". While there, it is a good idea to uncheck the box for "Hide extensions for known file types".

When that has been done and Word is started\re-started, a MergeTools tab will be added to the Ribbon:

The requirements for using the system are:
1. The mail merge main document must be of the Letter type, though that does not mean that the output cannot be sent as an e-mail message.

2. For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left. For the Chart Merge utility, download the Mail Merging with Charts document that is also on that page of my OneDrive for additional requirements of the data source for use with that utility

3. For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.

4. For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

5. For a data source in the form of an Excel worksheet, the data must start in the second row of the worksheet and there should be no empty rows within the range of data that is to be processed.

6. If there are multiple addressees in either the To or CC fields of the data source, the email addresses need to be separated by a semi-colon.

NOTE: The MergeTools applications cannot handle “Compound” MergeFields such as the «AddressBlock» or «GreetingLine».Instead of using those fields, you will need to insert the individual merge fields.
You may also want to download:
1. the Merging with Attachments document that is also on that page which explains how the system is used. It is not actually necessary to have separate attachments as the facility can be used to send just the documents created by the merge itself as attachments, either as the body of the message itself or in the form of Word files or .pdf files.

2. the Mail Merging with Charts document that is also on that page. That document explains how you must set up the Excel Data Source and the Mail Merge Main document to be able to execute a merge with a Chart that is unique to each record in the data source.

3. the Using the Many to One Facility document that describes how to use that facility.

MailMergeConfused · #5 07-28-2016, 03:31 AM

Upon merging using the merge wizard, it merges to emails and writes a custom subject field based upon the merge fields. The problem isn't with the VBA code though, if I disable the VBA code, it still has the same problem behaviour, that of when connecting to a previously used file, if the contents of that file has changed, it doesn't seem to read all the rows, only the number of rows upto the maximum of the previous merge, i.e if the last merge had 5 lines, it will only read the first 5 lines even though the excel data source now contains 10 lines.

My users can work around this by saving to a new filename and choosing that as a new data source, but I don't understand why this behaviour is happening.

macropod · #6 07-28-2016, 05:07 PM

Quote:

Originally Posted by MailMergeConfused

when connecting to a previously used file, if the contents of that file has changed, it doesn't seem to read all the rows, only the number of rows upto the maximum of the previous merge, i.e if the last merge had 5 lines, it will only read the first 5 lines even though the excel data source now contains 10 lines.

My users can work around this by saving to a new filename and choosing that as a new data source, but I don't understand why this behaviour is happening.

If the changed file was not saved before the mailmerge main document was opened, it won't see the new records.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Grouping records by supplier	Franci	Mail Merge	2	07-19-2016 10:25 PM
if address is repeated in subsequent records	paulys	Mail Merge	7	12-08-2014 04:10 PM
Combining two different records into one	JemmyRi	Mail Merge	1	10-17-2014 05:29 AM
Change handout master in PowerPoint custom template	porland	PowerPoint	0	10-02-2014 01:55 PM
Records paragraph break	ervilhaman	Mail Merge	14	04-09-2013 02:55 PM