Word Macro Help mailmerge

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
Apologies if this is posted in the wrong section - please advise

I have a word script to where I am pulling data from excel spreadsheet 'ADO' and creating a basic mail merge (saving each letter in a folder) but not sending them... Lets say on Sheet ADO I have 10 rows of data the script below will stop on the 11th row and because its empty it will open a separate blank template? I want it to stop on the row where the data ends and not open the final word document.

Also, is there any way to close the word document once the macro has been run?

Cheers in advance

VBA Code:
Option Explicit
   
    Const FOLDER_SAVED As String = "PATH_HERE"
    Const SOURCE_FILE_PATH As String = "FILE_HERE"
   
    Sub TestRun()
    Dim MainDoc As Document, TargetDoc As Document
    Dim dbPath As String
    Dim recordNumber As Long, totalRecord As Long
   
    Set MainDoc = ActiveDocument
    With MainDoc.MailMerge
       
            '// if you want to specify your data, insert a WHERE clause in the SQL statement
            .OpenDataSource Name:=SOURCE_FILE_PATH
               
            totalRecord = .DataSource.RecordCount
   
            For recordNumber = 1 To totalRecord
           
                With .DataSource
                    .ActiveRecord = recordNumber
                    .FirstRecord = recordNumber
                    .LastRecord = recordNumber
                End With
               
                .Destination = wdSendToNewDocument
                .Execute False
               
                Set TargetDoc = ActiveDocument
   
                TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("File_Name").Value & ".pdf", wdFormatDocumentDefault
                TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("File_Name").Value & ".pdf", exportformat:=wdExportFormatPDF
               
                TargetDoc.Close True
               
                Set TargetDoc = Nothing
                       
            Next recordNumber
   
    End With
   
    Set MainDoc = Nothing
    End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
Also, if it helps I get a Run time error '4198' on line

VBA Code:
 TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("File_Name").Value & ".pdf", wdFormatDocumentDefault
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,490
If your code is running on after the last record, that can only be because the worksheet has additional 'in-use' rows. Use Ctrl-End to find the last row, then delete all empty rows between there and your last data row (don't just delete the cell contents). That said, for some code that does stop at the last data row, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at: Mailmerge Tips & Tricks
 

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
If your code is running on after the last record, that can only be because the worksheet has additional 'in-use' rows. Use Ctrl-End to find the last row, then delete all empty rows between there and your last data row (don't just delete the cell contents). That said, for some code that does stop at the last data row, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at: Mailmerge Tips & Tricks

Hi Paul,

I understand what you're saying however the worksheet is a basically data for a mail merge which I input into and then it sends the email out to the recipients therefore the amount of rows of data can be 15 or 18 the next day - I hope this makes sense

But if there isn't a way to get around this no worries... Any insight on the error on the code above?

Cheers
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,490

ADVERTISEMENT

Regardless, the code in the link I posted stops at the last data row. It does so by testing whether a particular field has any date before continuing. You could add such a test for yourself but, hey, the hard work's already been done for you...
 

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
Regardless, the code in the link I posted stops at the last data row. It does so by testing whether a particular field has any date before continuing. You could add such a test for yourself but, hey, the hard work's already been done for you...

Hi Paul,

Brilliant info mate, just a slight issue I'm unsure how to connect to my data file using macro rather than doing it manually?

Is there any way to merge the two below?

VBA Code:
 Const SOURCE_FILE_PATH As String = "Data_File_Here"
.OpenDataSource Name:=SOURCE_FILE_PATH

'Merged with the below code?
'

Sub MailMerge()

Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long

Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = "File_Name"
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
     
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("First_Name")) = "" Then Exit For
        'StrFolder = .DataFields("Folder") & "\"
        StrName = .DataFields("File_Name")
       End With
      .Execute Pause:=False
      If Err.Number = 5631 Then
        Err.Clear
        GoTo NextRecord
      End If
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
     
      With ActiveDocument
        '.SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
     
NextRecord:
    Next i
   
  End With
End With
Application.ScreenUpdating = True
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,490
Unless you're using automation, I'm not sure why you'd not already have the document configured as a mailmerge main document that's already (manually) connected to the data source. Having to connect to the data source also means you should be supplying the SQL statement to at least tell the mailmerge which worksheet to use (per the next example from the link - Run a Mailmerge from Excel, Sending the Output to Individual Files), but that's missing from the code you posted. That said, you can just insert your:
.OpenDataSource Name:=SOURCE_FILE_PATH
line immediately before or after:
.Destination = wdSendToNewDocument
 

Watch MrExcel Video

Forum statistics

Threads
1,118,121
Messages
5,570,306
Members
412,318
Latest member
angoeyuan
Top