Hi all, my apologies as this is actually being used in a .Word doc, but hoping it's okay to post it here as perhaps this is something that someone may tackle in Excel.
I've created a large file using a mail merge. The large file is a combination of multiple 3pg sets of information, with each 3pg set specific to 1 employee and accompanied by a custom footer from the mail merge. My goal was to run a Macro to split that large file into each employees own 3 pages and use the footer to name each new PDF document that was generated. My code is below.
Something appears to be wrong with the ".SaveAs FileName :=fname.Text..." line of the code but I can't figure it out. Any suggestions or resources I can reference? Thanks so much in advance for your help!!
I've created a large file using a mail merge. The large file is a combination of multiple 3pg sets of information, with each 3pg set specific to 1 employee and accompanied by a custom footer from the mail merge. My goal was to run a Macro to split that large file into each employees own 3 pages and use the footer to name each new PDF document that was generated. My code is below.
Something appears to be wrong with the ".SaveAs FileName :=fname.Text..." line of the code but I can't figure it out. Any suggestions or resources I can reference? Thanks so much in advance for your help!!
Code:
Sub NewMacroForTesting()
'
' NewMacroForTesting Macro
'
'
Dim i As Long, Source As Document, Target As Document, Letter As Range
Dim fname As Range
Set Source = ActiveDocument
With Source
For i = 1 To .Sections.Count
Set fname = .Sections(i).Footers(wdHeaderFooterPrimary).Range
fname.End = fname.End - 1
Set Letter = .Sections(i).Range
Letter.End = Letter.End - 1
Set Target = Documents.Add
With Target
.Range.FormattedText = Letter.FormattedText
.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = ""
.SaveAs FileName:=fname.Text & " - Contributions", FileFormat:=wdFormatPDF
.Close wdDoNotSaveChanges
End With
Next i
End With
End Sub