Microsoft Office Forums - View Single Post - [Solved] Mail Merge from Excel to Separate Documents and Different Folders

chunkyfresh · 01-04-2020, 04:12 PM

Hello,

Thanks for the article. I'm having a difficult time understanding a lot of it since my knowledge of VBA is basic. Sorry about that. I have a few questions.

Where do I indicate the Data Source in this script? Is there a way I can call it directly by it? For example, I tell the script the data source is file of name "Data Source" and it is located at X path.

Code:

Sub Merge_To_Individual_Files()

Application.ScreenUpdating = False

Dim StrFolder As String

Dim StrName As String 

Dim MainDoc As Document 

Dim i As Long 

Dim j As Long

Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  'StrFolder = .Path & "\"
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrFolder = .DataFields("Supervisor") & "\"
        StrName = i & "-" & .DataFields("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
        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
End Sub