Word Macro Help mailmerge

[Mike2502]

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

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

 

[Mike2502]

Also, if it helps I get a Run time error '4198' on line

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

 

[Macropod]

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]

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]

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]

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?

 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]

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