Solved: Loop VBA for Mail Merge Document

[cfluegel]

Hello all,

I am writing a Macro in word (have done plenty in excel, not so much in MS Word). The Macro will take a mail merge document, open the first merge record as a new document, update all fields (to get the image to update) and then export as a PDF.

I have 2 updates that need to be made to this code in order for it to work perfectly.

1st I need to loop this process to move on to the next merge record in the main mail merge document, open as a new document and export as a PDF and so on until all Merge records have generated an individual PDF file.

2nd is I need the file name of the PDF to change based on a merge field (employee ID for example).

The end result would be multiple PDF documents saved to a folder with file names based on the employee's ID.

What I have so far is:

Sub Macro1()
'
' Macro1 Macro
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
ActiveWindow.ActivePane.VerticalPercentScrolled = 64
Selection.WholeStory
Selection.Fields.Update
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"Q:\GENERIC FILE PATH" & ".pdf", ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, FROM:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False
ActiveWindow.Close
End Sub

Any help with the two items above would be GREATLY appreciated!!!

Thanks for taking the time to look

[cfluegel]

I know if I change:
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord

to:
.LastRecord = ActiveDocument.MailMerge.DataSource.LastRecord

It will run the merge function on all records in the range, but it saves all of them to one single PDF document and I need them to be saved individually with the EE ID as the file name

[cfluegel]

I'm close on the "Name file based on Merge Field" one as well with:
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"Q:\FILE PATH\" & _
ActiveDocument.MailMerge.DataSource.DataFields("File_Name").Value & ".pdf"

Only problem with that is the PDF, with the correct file name now does not display the actual merge data but rather the merge fields on the PDF

[macropod]

Have you had a look at: http://www.gmayor.com/individual_merge_letters.htm

[cfluegel]

Thanks macropod, indeed I have used that add-in, only downside to it is it does not perform the data update that I have written into mine so the images do not update. If I could get into the actual macro in the add-in I could add that piece and it would be done, but the macro is not able to be updated. :(

You're on the right track for sure though. I tried both that add-in and the Chart Merge Add-in from Doug Robbins. The Chart Merge add-in works well, but the images (pictures of any sort) come out SUPER blurry. Probably some down-sampling when converting to PDF.

Thanks for offering your input, I certainly appreciate it!

[cfluegel]

I did end up finding a way to save the PDF with a file name based on the mail merge data. Updated code is below. Only step left is to figure out how to loop this for all rows in the merge data.


Sub Macro1()
'
' Macro1 Macro

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
ActiveWindow.ActivePane.VerticalPercentScrolled = 64
Selection.WholeStory
Selection.Fields.Update
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"Q:\FILE PATH\" & _
Documents("MasterTemplate.doc").MailMerge.DataSource.DataFields("File_Name").Value & ".pdf", ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, FROM:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False
ActiveWindow.Close

End Sub

[cfluegel]

Have you had a look at: http://www.gmayor.com/individual_merge_letters.htm

Well, Apparently I just needed to try again! Thinking the issue was that last time I tried this add-in the chart was located inside a text box. If it is not in a text box the pic updates just fine!

This add-in will work afterall!

Thanks for bringing it back up Macropod!

[bhrt.rthd]

Hello all,

I am writing a Macro in word (have done plenty in excel, not so much in MS Word). The Macro will take a mail merge document, open the first merge record as a new document, update all fields (to get the image to update) and then export as a PDF.

I have 2 updates that need to be made to this code in order for it to work perfectly.

1st I need to loop this process to move on to the next merge record in the main mail merge document, open as a new document and export as a PDF and so on until all Merge records have generated an individual PDF file.

2nd is I need the file name of the PDF to change based on a merge field (employee ID for example).

The end result would be multiple PDF documents saved to a folder with file names based on the employee's ID.

What I have so far is:

Sub Macro1()
'
' Macro1 Macro
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
ActiveWindow.ActivePane.VerticalPercentScrolled = 64
Selection.WholeStory
Selection.Fields.Update
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"Q:\GENERIC FILE PATH" & ".pdf", ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, FROM:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False
ActiveWindow.Close
End Sub

Any help with the two items above would be GREATLY appreciated!!!

Thanks for taking the time to look

==============================================

Hello Buddy, I am not sure if you have figured it out how to loop through the records in your code.

I have done example and it works best for me...i am not a hardcore coder, however I have tried my best... hope this helps!
In the below code, I have set "I" as user's input which is purely my project's requirement. once user enters the number, loop will start with that numbered line of record and generates new document until its done with generating documents.



Dim I As Integer 'Merge Record
Dim StartPoint As Integer 'Enter from which point you want to run certificate
Dim Num As Integer 'Number of Certificate Counts
Dim sText As String 'Blank text
Dim YesNo As String 'Yes No message box
Dim setPath As FileDialog 'SaveAs folder
Dim ActPath As String 'Set Path

'Warning Message before running following codes

YesNo = MsgBox("Are you sure you want to run thi Macro", vbYesNo + vbExclamation, "MailMerge Macro")
If YesNo = vbYes Then GoTo Line1 Else GoTo Line2


Line1:


'Take certificate counts from user

StartPoint = InputBox(Prompt:="Start Generating Certificate from: ", Title:="Start Certificate Number")
Num = InputBox(Prompt:="Generate Certificates till: ", Title:="End Certificate Number")


'Merge Individual Document in simultaneously

For I = StartPoint To Num

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = I
.LastRecord = I
End With
.Execute Pause:=False
End With