Mail merge with multiple records

Question

This Word VBA code which runs correctly:

Sub Macro2()
    
    Documents.Open Filename:="testing.docx", AddToRecentFiles:=False
    strSourceDoc = ActiveDocument.Path & "" & "fixedcharge.xls"
    ActiveDocument.MailMerge.OpenDataSource Name:=strSourceDoc,Format:=wdOpenFormatAuto, Connection:= "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & _
      strSourceDoc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";" & _
      "Jet OLEDB:System database="""";Je", _
      SQLStatement:="SELECT * FROM ''Sheet$1''", SQLStatement1:="", _
      SubType:=wdMergeSubTypeAccess
        
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
            
        With .DataSource
            .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
        End With
            
        .Execute Pause:=False
    End With

End Sub

My problem is that my Excel sheet has more than one record. When a Word document is saved to "SOW1.docx", only one record is saved.

**I'm confused:** does it *`not work properly`* or does it *`run correctly`* ? — ashleedawg, Mar 25 '18 at 16:11
it works but i have seven rows in my excel sheet only row 1 data is shown in the word document .The remaining six are not shown — rakesh seebaruth, Mar 25 '18 at 16:50
After the macro has finished there should be a new document with the results of all the records. Is no new document generated? You should see two documents: the one your code opens and the new one. — Cindy Meister, Mar 25 '18 at 20:35
Please note that a new word document is opened by the name of Form Letters 1 only one record is shown the other records are not shown in the document. — rakesh seebaruth, Mar 26 '18 at 12:46

sunsetsurf · Accepted Answer · 2018-03-26T19:06:40.497

0

Here is the whole of the code I used:

Sub MacroTest()
Documents.Open FileName:=ActiveDocument.Path & "\" & "Labels.docx", AddToRecentFiles:=False
strSourceDoc = ActiveDocument.Path & "\" & "Addresses.xlsx"
ActiveDocument.MailMerge.OpenDataSource Name:= _
    strSourceDoc _
    , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & strSourceDoc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:E" _
    , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
    wdMergeSubTypeAccess

With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True

    With .DataSource
        .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
        .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
    End With

.Execute Pause:=False
End With

ActiveDocument.SaveAs FileName:="AllTogether.docx", FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False
End Sub

Sub AllSectionsToSubDoc()

Dim currentSection  As Long
Dim sections        As Long
Dim doc             As Document

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set doc = ActiveDocument
sections = doc.sections.Count
For currentSection = sections - 1 To 1 Step -1
    doc.sections(currentSection).Range.Copy
    Documents.Add
    ActiveDocument.Range.Paste
    ActiveDocument.SaveAs (doc.Path & "\" & currentSection & ".doc")
    ActiveDocument.Close False
Next currentSection

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

edited Mar 26 '18 at 19:06

answered Mar 25 '18 at 18:38

sunsetsurf

582
4
8

i will check it and revert to u – rakesh seebaruth Mar 26 '18 at 02:54
i have tried your vba codes it works but a new word document is opened by the name of Form Letters 1 ,only one record is shown the other records are not shown in the document. Your print screen above pertains to label my mine are letters address to customers.Can i send my files on dropbox ? – rakesh seebaruth Mar 26 '18 at 12:49
@rakeshseebaruth, try the new code first and the let me know how it goes and we will take it from there. – sunsetsurf Mar 26 '18 at 19:07
See here for an example of how to use a field in the data source for the filename. https://stackoverflow.com/questions/12594828/how-to-split-a-mail-merge-and-save-files-with-a-merge-field-as-the-name – sunsetsurf Mar 26 '18 at 19:15
i have tried your vba codes and it's working.But alas your print screen differs from mine.Only one letter is shown in mine. There are seven rows in my excel sheet meaning seven letters in word. I don't know where the problem lies.Can i send my files on drop box please. – rakesh seebaruth Mar 27 '18 at 01:17
You used the newest version of the code? You scrolled through the document? Did it save the file? If so, did you open it and see all the letters there? In my screenshot I reduced the zoom drastically. Sure. – sunsetsurf Mar 27 '18 at 01:27
yes i have used the newest version of the code I have scrolled down through the document only page 1 is displayed .I even saved the document and re open it the result is the same. I also reduced the zoom.Actually i am at work before coming to work i tried it at home i am only getting page 1 only, at work also i am getting page1 only. Can i send my files on drop box please. – rakesh seebaruth Mar 27 '18 at 04:21
https://www.dropbox.com/s/47r0gnrljz5xknq/abc1.xlsx?dl=0 https://www.dropbox.com/s/q1iswiuu96bapo3/abc1.docx?dl=0 – rakesh seebaruth Mar 27 '18 at 07:56
Linked the return files in dropbox. All's working file on my end. – sunsetsurf Mar 27 '18 at 14:41
it works perfectly. According to you what was wrong in my files. I have noticed that you have saved my word document as macro enabled document. Is it compulsory to save it to that format ? Last but not least i want to change the directory and save the word document by the name of Fixed with today's date and in the folder C:\Users\rakesh\Desktop\mailmerge2018 – rakesh seebaruth Mar 27 '18 at 16:30
You are welcome, glad to help. Did you get them? Did they work? If so, could you vote up my answer and mark it as the correct answer, I'm trying to earn reputation. Thank you. – sunsetsurf Mar 27 '18 at 16:58
yes i did get them it works perfectly you are a boss – rakesh seebaruth Mar 27 '18 at 17:17

score 0 · Answer 2 · answered Mar 25 '18 at 21:22

0

Your code only looks at the active record. You should allow it to look at all records:

With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
End With

and, if appropriate, combine that with a filter (via additional arguments to the SQLStatement) to limit the output to whatever records match your criteria.

answered Mar 25 '18 at 21:22

macropod

12,757
2
9
21

I have changed the FirstRecord and Last record with yours . When i execute the program i am getting error word not responding despite page 1 is displayed .Can you comine that with a filter as suggested above. Your help will be sincerely appreciated. – rakesh seebaruth Mar 26 '18 at 12:57
That's not because of the change I suggested. I suspect it's because your "testing.docx" document has been saved as a mailmerge main document and there is nothing in your code to cope with that. You need to have 'Application.DisplayAlerts = wdAlertsNone' at the start of the code and 'Application.DisplayAlerts = wdAlertsAll' at the end. – macropod Mar 26 '18 at 20:21

Mail merge with multiple records

2 Answers2