# Word 2007/2010 Mail Merge to save to individual PDF files



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## kryptonian

Here's what I got running so far taken from the net <forgot which one since I opened a lot of 'em>.  It is able to save the file as a PDF but I need to do these 2 things:

1. Splilt it to different PDF files
2. Save each PDF with a unique name, i.e., name field in the list + date




		Code:
__


Public Sub MergePDF()
Dim pdfName As String
Dim oDoc As Document
Dim oMerged As Document
Dim oRng As Range
Dim iPages As Long
Dim iRecords As Long
' This tries to speed up the macro.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set oDoc = GetObject("C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\Test 1.docm")
With oDoc.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:= _
                    "C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\List.xlsx", _
                    ConfirmConversions:=False, _
                    ReadOnly:=True, _
                    LinkToSource:=True, _
                    AddToRecentFiles:=False, _
                    PasswordDocument:="", _
                    PasswordTemplate:="", _
                    WritePasswordDocument:="", _
                    WritePasswordTemplate:="", _
                    Revert:=False, _
                    Format:=wdOpenFormatAuto, Connection:= _
                    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\excelsource.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLE", _
                    SQLStatement:="SELECT * FROM `Sheet1$`", _
                    SQLStatement1:="", _
                    SubType:=wdMergeSubTypeAccess
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute False
End With
Set oMerged = ActiveDocument
Set oRng = oMerged.Range
oRng.Start = oRng.End
iPages = oRng.Information(wdActiveEndPageNumber)
iRecords = ActiveDocument.Sections.Count - 1
' This prints to a PDF file and saves it to a designated folder.
pdfName = "C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
oMerged.ExportAsFixedFormat pdfName, 17 'Path?
oMerged.Close 0
Open "C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\LogFile.txt" For Append As #1
Write #1, "Pages - " & iPages & "  Records - " & iRecords  ' Write data."
Close #1 ' Close file.
Documents.Open oDoc.FullName
End Sub


----------



## Cindy Ellis

Hi,
I have used the following macro to save mail merge output to individual files based on the value of a field in the datasource...maybe you can modify it to do what you need, or add in the logic into your macro that merges the data one at a time.  It uses a different approach than the macros I found on the web.  Instead of merging then separating, it loops through the datasource, merging and saving one record at a time.
The macro allows the user to select a directory for saving the files.  It depends on an existing mail merge main document that already contains merge fields and a link to the datasource.
Hope this helps, 



		Code:
__


Sub merge1record_at_a_time()'
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog


    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd


        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems


                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem


        Next vrtSelectedItem


        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With


    'Set the object variable to Nothing.
    Set fd = Nothing


Application.ScreenUpdating = False


MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docname = .DataFields("ASP_Print").Value & ".docx"      ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
    ActiveDocument.SaveAs FileName:=docname, FileFormat:= _
        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    ActiveWindow.Close


    Windows(MainDoc).Activate
    Next i
Application.ScreenUpdating = True


End Sub


----------



## kryptonian

Thanks, Cindy! I'll give it a try when I get to work tomorrow and will get back to you.


----------



## kryptonian

Hi Cindy! With a little tweak of your code, I was able to do a mail merge to save as PDF files! Thank you very much for the assistance! Sharing the tweak I made below for reference to those who might have the same concern.

Also changed extension of docName from *.docx to *.pdf

Part of original code:


		Code:
__


    ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    ActiveWindow.Close


Changed to:


		Code:
__


ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ 'set OpenAfterExport to False so the PDF files won't open after mail merge
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False


----------



## Cindy Ellis

Thanks for the feedback and for replying with a working solution.  I think others will benefit from your solution.


----------



## ashleys

Hi Cindy - 

I am trying to use your code, and I am a very new newbie. I managed to get it to save the first file, but then it says there is a problem with this line of code:
End With
.Execute Pause:=False

Just wondering how much I need to personalize other than the name that I want the document to have.

Thanks for any help!

Ashley


----------



## Cindy Ellis

Hello Ashleys, and welcome to Mr. Excel!
I'm not sure what the problem would be that would save the 1st file but no files after that.  If you do the word merge without the macro (merging to a single file rather than a printer) do you get a file that includes all of the records merged in sequence?
Did you update the macro with the field name that holds the filename to save to?
Is there anything unique about the data?
Also, if your intention is to save to pdf, are you using the modification from kryptonian?

It's a bit more difficult for me to troubleshoot word macros than excel (just due to more experience with Excel), but I'll give it a try 

Hoping to help,


----------



## ashleys

When I do a regular mail merge I get the whole large document. I did update the field name of what I want the files to save as, and it worked for my first document, which is a unique thing for each document. 
Not sure what you mean about having something unique about the data, it's just letters we are sending out, with each letter being customized for that person, including an ID number (which is our unique identifier). 
We are saving to word, so that's working fine.

I am using word 2007, if that would make a difference. I get a runtime error 5631. I've been here: You receive a to see if I could figure out how to fix it, but I'm not good enough at macros to really know where I should be adding this information. Any help would be greatly appreciated! 

Thanks!! 

Ashley


----------



## ashleys

Hi Cindy - 
I figured out my problem. Because I wasn't selecting all the records from my excel file as part of the mail merge the recoord numbers weren't 1, 2, 3, 4, 5 etc. So I just edited my excel file to only include the records I want, and the macro works great. It's not perfect, but for the amount of time I am doing this, it works well for me.

Thanks again,

Ashley


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## MarkKn

Cindy Ellis said:


> Thanks for the feedback and for replying with a working solution.  I think others will benefit from your solution.



Hi Cindy.  I am trying to use your code with Kryptonian's modification.  The issue I seem to be encountering is Word trying to save out a new copy of the word document after saving the PDF.  Any ideas?  I can post the code I have inserted if that would be useful.

Thanks 
Mark


----------



## Macropod

I suspect the problem is with this line:
Documents.Open oDoc.FullName
It appears to be trying to re-open the already-open document. Try commenting-out the line and see if you get the desired results.


----------



## MarkKn

Macropod said:


> I suspect the problem is with this line:
> Documents.Open oDoc.FullName
> It appears to be trying to re-open the already-open document. Try commenting-out the line and see if you get the desired results.




Thanks Paul!!  I was actually using the other code, but you got me pointing in the right direction.  I actually needed to make two tweaks:  remove the line _Windows(MainDoc).Activate _AND change the line _ActiveWIndow.Close _to include _
SaveChanges:=False
_

It's working great now!  Will be so much faster than their old way of exporting the entire document to PDF, breaking it into individual docs, and then renaming each of those individual docs.


----------



## JMcCauley

This is a great thread.  Thanks.  I've run and saved individual .pdfs file types based on my merge data and using Cindy's code.  The only problem now is that I cannot open my files.  I receive the message that the file has been damaged. I've modified the docName and changed the extension to .pdf

Any ideas?  


		Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog




    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd




        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems




                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem




        Next vrtSelectedItem




        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With




    'Set the object variable to Nothing.
    Set fd = Nothing




Application.ScreenUpdating = False




MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docName = .DataFields("ID") & ".pdf"     ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
    ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    ActiveWindow.Close


    Windows(MainDoc).Activate
    Next i
Application.ScreenUpdating = True




End Sub


----------



## Cindy Ellis

Hi JMcCauley, and Welcome to Mr.Excel!
As shown in the modification from Kryptonian, for this to save as a pdf, you also need to set the appropriate parameters to create the pdf, not just save it as a word document with a pdf extension.
Try changing this section 


		Code:
__


    ActiveDocument.SaveAs FileName:=docname, FileFormat:= _        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    ActiveWindow.Close


to the following:


		Code:
__


ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ 'set OpenAfterExport to False so the PDF files won't open after mail merge
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ 
        BitmapMissingFonts:=True, UseISO19005_1:=False

Hope that helps,


----------



## JMcCauley

That worked!  Thanks Cindy!

I must be doing something small wrong, but now I have to manually indicate that I don't want to save the word version.  I'm a rookie at Macros, but I thought I knew how to fix that, but then I went down a dead end, so I went back and just declined to save each one....  This is very interesting stuff though!  Looking forward to picking up some more pointers.  Thank you so much for your help!


----------



## Macropod

After the 'ActiveDocument.ExportAsFixedFormat' code, insert a new line with 'ActiveDocument.Saved = True'. That will stop the save prompts. Alternatively, to close the document via code, use 'ActiveDocument.Close SaveChanges:=False' instead of 'ActiveWindow.Close'.


----------



## anthonyy

*Encrypted PDF*

Is it possible to create using same code a password protected PDF with Owner Password and user password.


----------



## Macropod

*Re: Encrypted PDF*



anthonyy said:


> Is it possible to create using same code a password protected PDF with Owner Password and user password.


Not with Word 2010 (which I use) or earlier. Word 2013 _may_ be different.


----------



## JMcCauley

Beautiful.  Thanks Macropod.


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## comebakqueen

Hi Guys!

Firstly, wanted to say a HUGE thank you for this code; saved me SO much time trying to figure it out! 


  Just had a question regarding the Dialog prompter. My files will be saved in the same path every time; is there a way to tell it to automatically go there every time I run the macro so that I don’t have to go through the whole selection process again… I know this might seem lazy but this is a weekly report that I have to generate and the extra few minutes will save me so much time!


  Thanking you in advance!


----------



## Macropod

Try:


		Code:
__


Sub merge1record_at_a_time()
Application.ScreenUpdating = False
Dim StrPath As String, StrName As String, MainDoc As Document
StrPath = "C:\File path\"
Application.ScreenUpdating = False
Set MainDoc = ActiveDocument
With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrName = .DataFields("ID")
      End With
      .Execute Pause:=False
    End With
    With ActiveDocument
      .SaveAs2 FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

Naturally, you'll need to use your own path for the StrPath variable and, if you're saving the outputs as PDF files, change the '.SaveAs2' line to:


		Code:
__


      .SaveAs2 FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False


----------



## comebakqueen

Heya Macropod!

Thank you for the reply! I’m still struggling to get it to work (Give me Excel VBA any day)!

At this stage the code is:

Application.ScreenUpdating = False
  Dim StrPath As String, StrName As String, MainDoc As Document
  StrPath = "R:\Operations\Australia\Reports\DIFOT\Supplier Reports"
 Application.ScreenUpdating = False
  Set MainDoc = ActiveDocument
 With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
     With .MailMerge
       .Destination = wdSendToNewDocument
       .SuppressBlankLines = True
        With .DataSource
         .FirstRecord = i
         .LastRecord = i
         .ActiveRecord = i
         StrName = .DataFields("Supplier")
       End With
       .Execute Pause:=False
     End With
     With ActiveDocument
  .ExportAsFixedFormat OutputFileName:=StrPath & StrName & ".pdf", ExportFormat:=wdExportFormatPDF, _
         OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
         Item:=wdExportDocumentContent, IncludeDocProps:=True, CreateBookmarks:=wdExportCreateNoBookmarks, _
         KeepIRM:=True, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False
     End With
  ActiveDocument.Close SaveChanges:=False
    Next i
 End With
 Application.ScreenUpdating = True

End Sub

But it’s saving into the filepath "R:\Operations\Australia\Reports\DIFOT" and is adding "Supplier Reports" before the field name. What am I doing wrong?

(ALSO: How do get the coding in a groovy box?)


----------



## Macropod

You should have a '\' after 'Supplier Reports'.
As for the code tags, you insert them via the menus on the 'Go Advanced' window.


----------



## Soramanego

This is a version that function for .pdf



		Code:
__


Sub Merge_to_pdf() '
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog


    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd


        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems


                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem


        Next vrtSelectedItem


        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With


    'Set the object variable to Nothing.
    Set fd = Nothing


Application.ScreenUpdating = False


MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"      ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
    ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        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 SaveChanges:=False
    

    Next i
Application.ScreenUpdating = True

End Sub


----------



## acedanger

Hi There, I'm a complete newbie for this. I have added the below macro in to word 2010 and clicked run, but all that happens is a prompt to enter a location where the files are to be saved, no PDF's are created for me.

Can someone maybe give me the "dummy" explanation? 

-Do i need to change the code in any fashion?
- Do I need to number my excel spreadsheet in any way?

Thanks a bunch.




Soramanego said:


> This is a version that function for .pdf
> 
> 
> 
> Code:
> __
> 
> 
> Sub Merge_to_pdf() '
> ' merge1record_at_a_time Macro
> '
> '
> Dim fd As FileDialog
> 
> 
> 'Create a FileDialog object as a Folder Picker dialog box.
> Set fd = Application.FileDialog(msoFileDialogFolderPicker)
> With fd
> 
> 
> 'Use the Show method to display the Folder Picker dialog box and return the user's action.
> 'The user pressed the button.
> If .Show = -1 Then
> For Each vrtSelectedItem In .SelectedItems
> 
> 
> 'vrtSelectedItem is aString that contains the path of each selected item.
> 'You can use any file I/O functions that you want to work with this path.
> 'This example displays the path in a message box.
> SelectedPath = vrtSelectedItem
> 
> 
> Next vrtSelectedItem
> 
> 
> Else
> MsgBox ("No Directory Selected.  Exiting")
> Exit Sub
> End If
> End With
> 
> 
> 'Set the object variable to Nothing.
> Set fd = Nothing
> 
> 
> Application.ScreenUpdating = False
> 
> 
> MainDoc = ActiveDocument.Name
> ChangeFileOpenDirectory SelectedPath
> For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = i
> .LastRecord = i
> .ActiveRecord = i
> docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"      ' ADDED CODE
> End With
> .Execute Pause:=False
> Application.ScreenUpdating = False
> 
> End With
> ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
> 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 SaveChanges:=False
> 
> 
> Next i
> Application.ScreenUpdating = True
> 
> End Sub


----------



## Macropod

What document are you running the macro from? It's intended to be run from your mailmerge main document, after you have attached the data-source as you'd do for any other mailmerge.


----------



## acedanger

Hi Macropod, I'm running the macro from the mailmerge document Word 2010 macro enabled document. I've gone through the step by step mail merge wizard through to step 6 of 6, then attempted to run the macro. As I said I get the option to choose a folder, but that's it. I've also tried running the macro after selecting "edit individual letters" once the merge is complete.



Macropod said:


> What document are you running the macro from? It's intended to be run from your mailmerge main document, after you have attached the data-source as you'd do for any other mailmerge.


----------



## Macropod

The code, as posted, generates PDF files prefixed with "Lettera_UMTS_01_2014_" and includes the value of a datafield in the source, named "PK_Assegnatario". Have you changed the line:
docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"
to whatever you require? The macro can't produce the correct output if this is wrongly specified.


----------



## acedanger

No I haven't, can you give me a quick description of what each of those references items mean? I'm not bad with computers, but this coding stuff is totally new to me!


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## comebakqueen

Macropod said:


> You should have a '\' after 'Supplier Reports'.
> As for the code tags, you insert them via the menus on the 'Go Advanced' window.



THANK YOU! It works perfectly *bows to Macropod*


----------



## Macropod

You have to supply them. To get you started, try:
docName = "MergeLetter_" & i & ".pdf"
That should be enough to generate individually-numbered PDFs, prefixed with "MergeLetter_"


----------



## acedanger

Thanks for all your help, but I'm still not getting any further it seems...I've pasted my two merge documents as a public link from Dropbox, can anyone tell from my files what I'm doing wrong with the code? If possible, fix the code and link to it again?

https://dl.dropboxusercontent.com/u/18508700/Share%20Cert.docm

https://dl.dropboxusercontent.com/u/18508700/Shares Email.xlsx




Macropod said:


> You have to supply them. To get you started, try:
> docName = "MergeLetter_" & i & ".pdf"
> That should be enough to generate individually-numbered PDFs, prefixed with "MergeLetter_"


----------



## Macropod

There seemed to be some corruption in your dropbox document that kept locking up my system. Nevertheless, I was able to extract the certificate and add it to a new document that I could attach to your datasource.

Rather than persisting with the other code (which works OK), I've revised my earlier code, coupling it with a folder browser. Once you've fixed any corruption that may be in the document, reattached the datasource and added the macro to it, the macro should run fine. Your output files will have the client names as the filenames.


		Code:
__


Sub Merge_to_pdf()
'merges one record at a time to the chosen output folder
Application.ScreenUpdating = False
Dim strFolder As String, StrName As String, MainDoc As Document, i As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set MainDoc = ActiveDocument
With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Last_Name")) = "" Then Exit For
        StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
      End With
      .Execute Pause:=False
    End With
    With ActiveDocument
      .SaveAs2 FileName:=strFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function


----------



## comebakqueen

Thanks Paul!

That worked a treat but now it's giving me Run time error '5853' once  all the PDF's are generated and when I go to debug it says it's because  of this line:



		Code:
__


.ActiveRecord = i


! It's been working fine since your previous assistance so was just  wondering if you had any idea what I'm doing wrong (you're so  knowledgable )

Thank you!
Tams =D


----------



## Macropod

I suspect it's because your worksheet has some empty, but used, rows at the bottom.


----------



## comebakqueen

That's what I thought too (and I deleted them)... I shall try again tomorrow; VBA has not been my friend today haha.

Thank you for all your help!


----------



## TBromley

Cindy, thank you for providing this code. I have a Word mail merge and it works as advertised for naming the individual pdf files. I'm having a problem with the mail merge and am hoping you can assist. The mail merge has an ASK field with the \o switch. When I run the mail merge directly from Word, the prompt asks only once. When I run the mail merge using your code, the prompt asks for each document. Does vba not recognize the /o switch? Is there a work around? Thank you.


----------



## Macropod

Cross-posted at: Ask prompt \o switch doesn't work in macro
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters


----------



## Cindy Ellis

TBromley, was your problem resolved on the other forum?  If not, let me know and I'll try to find a solution.


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## TBromley

Yes, it was.  Thank you for following up and for supplying the code to get me started.


----------



## diederikslot

Awesome help Macropot, i've been looking for this quite some time...


----------



## ConnDublin

First: Many thanks to Cindy for the massive start her code provided. 

Like the contributor *ashleys *we wanted to run this using Excel ranges as a source, where not all records were included in the dataset. We were getting problems because of the stepping through the records using the fixed pointer i. 

We made the following changes to Cindy's code on the first page of this thread, which now allows us to have selected records in the Excel dataset without any issues in the output:
 Before the For i loop starts, added:


		Code:
__


ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstDataSourceRecord

This moves the pointer to the first record.
Changed the following lines


		Code:
__


.FirstRecord = i
.LastRecord = i

To


		Code:
__


 .FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord

First time through it reads the first record selected before the loop starts. Each time around, it is moved on by the next change.

We moved the 
	
	
	
	
	
	




		Code:
__


.ActiveRecord = i

 to just before the Next i and it now moves to the next available record rather than record(i), which might not be selected for inclusion. It now reads:


		Code:
__


 ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextDataSourceRecord

For our full code (adapted from Cindy's):


		Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
' Adapted from:
'http://www.mrexcel.com/forum/general-excel-discussion-other-questions/713478-word-2007-2010-mail-merge-save-individual-pdf-files.html
    Dim fd As FileDialog
    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems
                    'vrtSelectedItem is aString that contains the path of each selected item.
                    'You can use any file I/O functions that you want to work with this path.
                    'This example displays the path in a message box.
                    selectedpath = vrtSelectedItem
                Next vrtSelectedItem
            Else
            MsgBox ("No Directory Selected.  Exiting")
            Exit Sub
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing

Application.ScreenUpdating = False
mainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory selectedpath
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstDataSourceRecord
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
         With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = .ActiveRecord
                .LastRecord = .ActiveRecord
                'moved the .ActiveRecord to just before the Next i
                docname = .DataFields("Site").Value & ".pdf"      ' ADDED CODE
            End With
            .Execute Pause:=False
            Application.ScreenUpdating = False
        End With
        'This next code is used if you want Word documents.
        '     ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
                wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
                :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
                :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
                SaveAsAOCELetter:=False
        'This next code is used if you want PDF documents.
        'set OpenAfterExport to False so the PDF files won't open after mail merge
        ActiveDocument.ExportAsFixedFormat OutputFileName:=docname, _
          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
        
        ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
        'Windows(mainDoc).Activate (works for us WITH pdfS when this is commented out - ConnDublin
        ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextDataSourceRecord
    Next i
        
Application.ScreenUpdating = True
End Sub

I hope this helps anyone wishing to make individual files from selected set of records held in Excel (or similar).


----------



## klingen2

I've used the code and created a macro that saves files individually which solves much of my requirements. One more that I can't seem to resolve. I need to concatenate all files for a department into one file. My input spreadsheet contains a department number and will be sorted by department number. On change in that department number I need to write the file and create a new one for the next file. Thanks for all your help.

Rick


----------



## ConnDublin

Hi!

Could you post a simplified example of the structure you are working with an I would be happy to work with a practical example as  reply. (Naturally, no real data should be contained therein.

In essence, I imagine this involves just one extra loop, looking out for a change in department code (or whatever).


----------



## klingen2

Thanks for the quick reply. I'm just not experienced with VB in word to figure out the loops. Anything from Cobol to Java would be much easier. Here is s sample of the data I would expect to have.


Org_IdFirst_NameLast_NameAmount10070642TestBody$5,000.0010070642SecondTest$5,100.0010080290First NewUnit$3,000.0010080290SecondNewUnit$3,500.0010080290ThirdNewUnit$3,100.00

I would want to create two pdfs with this input file. One named including 10070642 with two merged document pages and one named including 1008029 with three merged document pages. I'm sure it's not hard and I'm surprised there are not posts for doing something like this I can't find.

Rick


----------



## Macropod

You may want to try one of the Many-to-One Mail Merge add-in by Doug Robbins at http://bit.ly/1hduSCB

In addition to a 'Many to One' merge, the addin handles:
• Merge with Charts
• Duplex Merge
• Merge with FormFields
• Merge with Attachments
• Merge to Individual Documents
• Merge, Print and Staple


----------



## ConnDublin

Dear Rick,

Just gone 22:45hrs Dublin (Ireland) time so forgive me - will get to this Wed. 

Cheers.


----------



## mcway

Hey All,

Great thread and super helpful. I signed up to try and figure out this one problem I'm having. I'm using the pdf version of the code and while it seems to work fine, I am never getting more than a few iterations into the merge before I get a runtime error. *Run-time '-2147467259 (80004005)': The file is in use by another application or user.*

I've tried several iterations of the code mentioned on this thread. My latest version below. I first thought the issue was with Adobe pdf, but when I keep the function on to see the save changes prompt, it is when closing the word doc that the error is triggered and the macro stops. I don't know enough about Word's inner workings to know what causes the error, but it doesn't seem to be consistent and yesterday when I first tried all this, it seem to run through without any issues (though I couldn't open the pdf's when I switched the code which has been talked about on the thread).

The word version of the macro seems to work, and I don't know enough about the code itself to know what I might tweak in the pdf version to fix it or if that's even the issue.

Really appreciate any ideas you all might have.



		Code:
__


Sub merge1record_at_a_time() '' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog








    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd








        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems








                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem








        Next vrtSelectedItem








        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With








    'Set the object variable to Nothing.
    Set fd = Nothing








Application.ScreenUpdating = False








MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docName = .DataFields("Chargeback_Letter_Name") & ".pdf"
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
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
        ActiveDocument.Close SaveChanges:=False




    Windows(MainDoc).Activate
    Next i
Application.ScreenUpdating = True








End Sub


----------



## Macropod

I have posted code here that does work a number of times. See also 'Send Mailmerge Output to Individual Files' at:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
or:
Mailmerge Tips & Tricks


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## mcway

I've used Macropad and Cindy's codes, which work fine with the code for generating .docx files, but when I try the different coding for .pdf, I get the run-time error.

When the error occurs, the debugger highlights the section of the code you've said to paste over the SaveAs2 line for pdf files. I have Adobe Acrobat X Pro on my computer and just playing around with the code, I removed the ".pdf" from the OutputFileName command. It created non-pdf files, but it did run through and create the whole list, which makes me think the problem is with Adobe PDF.

Any ideas?
Thanks


----------



## Macropod

The code I have posted here (e.g. http://www.mrexcel.com/forum/genera...-save-individual-pdf-files-3.html#post3694494, http://www.mrexcel.com/forum/genera...-save-individual-pdf-files-4.html#post3710126) and at the links in my last post _works_. The only thing you might need to change in the posts here is from SaveAs2 to SaveAs, for Word 2007.


----------



## mcway

Thanks Paul,

I'm sure the problem I'm having is not your code. I am running it in the vba and if I use it to save individual word files, I have no problem. But this pesky runtime error that says the application is in use is persisting when I try it with pdf and I'm not sure how to fix it.

Sorry if I'm being a little thick here.


----------



## Macropod

But the code you're using is also not based on code that I have posted...


----------



## mcway

I had tried your codes that were on this thread, then went back to Cindy's. I've since gone back and tried the codes in the links you attached. This was the last code I tried, doing nothing more than changing your data source "Last_Name" to mine "Chargeback_Letter_Name". But in every instance I get the same error and the debugger refers to the same piece of the code dealing with saving the pdf.



		Code:
__


Sub Merge_To_Individual_Files()
'Merges one record at a time to the folder containing the mailmerge main document.
' Sourced from: http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks
Application.ScreenUpdating = False
Dim strFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
With MainDoc
  strFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Chargeback_Letter_Name")) = "" Then Exit For
        StrName = .DataFields("Chargeback_Letter_Name")
      End With
      .Execute Pause:=False
    End With
    For j = 1 To 255
      Select Case j
        Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
        StrName = Replace(StrName, Chr(j), "")
      End Select
    Next
    StrName = Trim(StrName)
    With ActiveDocument
      '.SaveAs FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      ' and/or:
.SaveAs FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
.Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub


----------



## Macropod

In that case, it seems you have a duplicate 'Chargeback_Letter_Name' record and, at the time Word's trying to save the duplicate, it hasn't finished closing the original. Even if timing were not an issue, you risk ending up with the duplicate overwriting the original. You apparently need a better way to differentiate your filenames.


----------



## soccerjon1013

This is what I have tried to use, and have got it to work somewhat in word 2003.  I have some basic questions and a problem though.  

1. Why does it ask for a location when it starts?  It seems to do the same thing no matter what I do
2. Is it possible to have it save it to a specific location based on text and fields (ie: c:\errors\'name'\CC Errors 'date') with 'name' and 'date' being data pulled from the mail merge?
3.  When I run this, I get an Run-time error '5853':  Invalid parameter
Debug highlights this line:     ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord

I am very new at all this, can you please help me?




		Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog




    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd




        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems




                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem




        Next vrtSelectedItem




        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With




    'Set the object variable to Nothing.
    Set fd = Nothing




Application.ScreenUpdating = False




MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount




    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
    
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord


    Windows(MainDoc).Activate
    Next i


Application.ScreenUpdating = True




End Sub


----------



## Macropod

soccerjon1013 said:


> 1. Why does it ask for a location when it starts?  It seems to do the same thing no matter what I do


Because that's what you've coded it to do, via:
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
...
End With


soccerjon1013 said:


> 2. Is it possible to have it save it to a specific location based on text and fields (ie: c:\errors\'name'\CC Errors 'date') with 'name' and 'date' being data pulled from the mail merge?


Yes. You could delete all the code relating to the File Dialog and instead use code like that shown in post #55. Indeed, if its OK to have .doc files instead of PDFs, you could almost use that code as is, with nothing more than a change to the field name used to populate the StrName and a change to the SaveAs code to save in the .doc format. As it is, your code makes no use of the data collected via the FileDialogFolderPicker and its not apparent you're doing anything with a PDF printer driver that might be able to make use of it.


----------



## soccerjon1013

I took that part out, and it no longer asked to pull the file location.  What was the purpose of it being there or not, it seemed to yield the same results either way?

I am unable to save it as a .doc format, it has to be as a pdf, since my default printer is the cute pdf, I'm assuming it always bring that up.

When I run this macro, it now brings up a save function for each record to print as pdf, however it still comes up with the run-time error, is there a way to fix it?

and please forgive me ignorance, but I really know very little about all this.  What code would I need, and where would I put it to save it in the location: c:\errors\'name'\CC Errors 'date') with 'name' and 'date' being data pulled from the mail merge?

Your assistance is GREATLY appreciated!


----------



## Macropod

AFAIK you cannot use VBA to pass a filename to the normal version of CutePDF. As a workaround, the following code copies the proposed filename from the mailmerge to the windows clipboard, from where you can paste it into the CutePDF dialog box.

*Note:* This code need a reference to the MS Forms 2.0 Object Library, which you set in the VBE via Tools|References.


		Code:
__


Sub Merge_Print_To_Individual_Files()
Application.ScreenUpdating = False
'Note: This code need a reference to the MS Forms 2.0 Object Library
Dim strFolder As String, StrName As String, i As Long, MyData As DataObject, MainDoc As Document
Set MyData = New DataObject: Set MainDoc = ActiveDocument
With MainDoc
  strFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Name")) = "" Then Exit For
        StrName = strFolder & Trim(.DataFields("Name")) & "-" & Format(.DataFields("Date"), "YYYY_MM_DD")
        MyData.SetText StrName
        MyData.PutInClipboard
      End With
      .Execute Pause:=False
    End With
  Next i
End With
Set MyData = Nothing: Set MainDoc = Nothing
Application.ScreenUpdating = True
End Sub


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## soccerjon1013

This gives me a runtime error '5941'  The requested member of the collection does not exist.  The debug highlights:

StrName = strFolder & Trim(.DataFields("Name")) & "-" & Format(.DataFields("Date"), "YYYY_MM_DD")

Does it matter that I'm using word 2003?  I did also make sure ms forms 2.0 object library is checked.


----------



## Macropod

The error is because your data source does not have fields named 'Name' and/or 'Date', per your previous advice:


> 'name' and 'date' being data pulled from the mail merge


You'll have to modify the code to match whatever your field names are.


----------



## kilogranules

Dear all

Thank you Cindy Ellis and kryptonian!!

Thank you so much for this thread, based on your code I have been able to successfully perform email merge and send with a unique attachment for each message. (Previously staff have been creating and emailing documents one by one...!!)

However while the macro works fine on the independent office laptop running Windows 8, it does not work on the networked desktop PC running Windows 7 Enterprise SP1 (64 bit), with Word and Outlook v. 14.0.7117.5001 (32 bit). The macro seems to run as normal but at the end it says "0 messages sent" and indeed no messages are sent. There are no error messages. I have tried changing the security settings in Outlook but to no avail.

There is also another PC running XP SP3 with Word 2003 v.11.8411.8405 and Outlook 2003 v.11.8339.8405 (don't laugh!) - I can also use this PC which is not networked, in case network settings are the problem. Running the macro on this system produces the same result - no error messages, outcome of "0 messages sent".

I'm mystified by the lack of error messages and the "0 messages sent" outcome.

This is a real pain since the laptop belongs to my boss and she takes it away frequently on meetings - not much use when there is a lot of processing to be done.

What changes do I need to make to persuade the macro to run on the desktop PC?

Many thanks
Keren

Code based on the initial posts in this thread:


		Code:
__


Sub Email_merge_with_attachments()
    Dim Source As Document, Maillist As Document, TempDoc As Document
    Dim Datarange As Range
    Dim i As Long, j As Long
    Dim bStarted As Boolean
    Dim oOutlookApp As Outlook.Application
    Dim oItem As Outlook.MailItem
    Dim mysubject As String, message As String, title As String
    Set Source = ActiveDocument
    ' Check if Outlook is running.  If it is not, start Outlook
    On Error Resume Next
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If
' Open the catalog mailmerge document
With Dialogs(wdDialogFileOpen)
    .Show
End With
    Set Maillist = ActiveDocument
    'Could change this to automatically set email subject
    ' Show an input box asking the user for the subject to be inserted into the email messages
    message = "Enter the subject to be used for each email message."    ' Set prompt.
    title = " Email Subject Input"    ' Set title.
    ' Display message, title
    mysubject = InputBox(message, title)
    ' Iterate through the Sections of the source Excel document and the rows of the directory mailmerge document,
    ' extracting the information to be included in each email.
For j = 1 To Source.Sections.Count - 1
    Set oItem = oOutlookApp.CreateItem(olMailItem)
    With oItem
        .Subject = mysubject
        .Body = Source.Sections(j).Range.Text
        Set Datarange = Maillist.Tables(1).Cell(j, 1).Range
        Datarange.End = Datarange.End - 1
        .To = Datarange
        For i = 2 To Maillist.Tables(1).Columns.Count
            Set Datarange = Maillist.Tables(1).Cell(j, i).Range
            Datarange.End = Datarange.End - 1
            .Attachments.Add Trim(Datarange.Text), olByValue, 1
        Next i
        .Send
    End With
    Set oItem = Nothing
Next j
Maillist.Close wdDoNotSaveChanges
'  Close Outlook if it was started by this macro.
If bStarted Then
    oOutlookApp.Quit
End If
MsgBox Source.Sections.Count - 1 & " messages have been sent."
'Clean up
Set oOutlookApp = Nothing
End Sub


----------



## dgr

Hi Cindy,
This line is yellow:


		Code:
__


docname = .DataFields("ASP_Print").Value & ".docx"      ' ADDED CODE

How do I debug this?


----------



## wormfan

kilogranules said:


> Dear all
> 
> Thank you Cindy Ellis and kryptonian!!
> 
> Thank you so much for this thread, based on your code I have been able to successfully perform email merge and send with a unique attachment for each message. (Previously staff have been creating and emailing documents one by one...!!)


 
I would also like to pass along a huge thanks to both *kryptonian *and *Cindy Ellis*. I have absolutely zero knowledge of VB, however using the posted code I have been able to generate unique PDFs for each record in my source (an Access Query) saved to a folder chosen when macro first runs, with a unique filename which is taken from a field in the data source.

Like *kilogranules *I am trying to use this macro to email the result. What I would really like to do is to be able to email the saved PDF as an attachment with the email address «contactemail», and email subject "Here is your attachment «batch»" taken from the data source and include a static text body in the email: "Hi, here is the document you requested"

The code I am using to generate and save PDF for each record is as follows:



		Code:
__


 Public Sub CreatePDF()
 '
 ' CreatePDF Macro
 '
 '
 ' From: [URL]http://www.mrexcel.com/forum/general-excel-discussion-other-questions/713478-word-2007-2010-mail-merge-save-individual-pdf-files.html[/URL]
  
     Dim fd As FileDialog
 
    'Create a FileDialog object as a Folder Picker dialog box.
     Set fd = Application.FileDialog(msoFileDialogFolderPicker)
     With fd
 
        'Use the Show method to display the Folder Picker dialog box and return the user's action.
         'The user pressed the button.
         If .Show = -1 Then
                 For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is aString that contains the path of each selected item.
                 'You can use any file I/O functions that you want to work with this path.
                 'This example displays the path in a message box.
         SelectedPath = vrtSelectedItem
 
        Next vrtSelectedItem
 
        Else
         MsgBox ("No Directory Selected.  Exiting")
         Exit Sub
         End If
     End With
 
    'Set the object variable to Nothing.
     Set fd = Nothing
 
            
 Application.ScreenUpdating = False
 
ThisDoc = ActiveDocument.Name
     ChangeFileOpenDirectory SelectedPath
     For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
         With ActiveDocument.MailMerge
             .Destination = wdSendToNewDocument
             .SuppressBlankLines = True
 ' MsgBox "Press OK"
             With .DataSource
                 .FirstRecord = i
                 .LastRecord = i
                 .ActiveRecord = i
                 docname = .DataFields("Batch").Value & ".pdf"
             End With
             .Execute Pause:=False
     Application.ScreenUpdating = True
     
             
         End With
         ActiveDocument.ExportAsFixedFormat OutputFileName:=docname, _
         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 False
 '    Windows(ThisDoc).Activate
     Next i
 Application.ScreenUpdating = True
 End Sub

 
I would be extremely appreciative if someone could show me how to acheive the next big step in automating the painfully repetitive task of emailing the unique PDF attachments out.

Thanks


----------



## Saved

Cindy and Macropod,

Thank you (albeit a late one) for the original macro to merge into PDF.  I have been successfully using it for the last 18 months without a problem.

However, I ran into a new situation today, the file I usually output has evolved into two pages now and the macro somehow doesn't output the fields in the second page.  I still get two pages in the output file, but the second page doesn't show up any of the fields.  I am sure this needs a small tweak, but I'm unable to find out what.

Please help!


		Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog




    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd




        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems




                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem




        Next vrtSelectedItem




        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With




    'Set the object variable to Nothing.
    Set fd = Nothing




Application.ScreenUpdating = False




MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docName = .DataFields("Roll_no").Value & ".PDF"      ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
    ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        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 SaveChanges:=False


    
    Next i
Application.ScreenUpdating = True




End Sub


----------



## Saved

Saved said:


> Cindy and Macropod,
> 
> Thank you (albeit a late one) for the original macro to merge into PDF.  I have been successfully using it for the last 18 months without a problem.
> 
> However, I ran into a new situation today, the file I usually output has evolved into two pages now and the macro somehow doesn't output the fields in the second page.  I still get two pages in the output file, but the second page doesn't show up any of the fields.  I am sure this needs a small tweak, but I'm unable to find out what.
> 
> Please help!
> [/code]



I figured out what the problem was, turns out it had nothing to do with the macro.  My file has background images on both pages and the one on the second page somehow got in front of the mergefields.  Rearranged it and now everything is perfect!  Thank you once again everyone, this is a great site!


----------



## WaxonWaxov

Thanks. this is Exactly what I needed.


----------



## Tannedbrit

WoW! Thanx guys thats amaizing!  I've been trying to figure this out for a year!
The macro works perfectly


----------



## Massive79

Hi.

Is there anyway to add this solution with a specific password protection for each document printed in PDF from mail merge ?


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## jbndylan

Hi Cindy, I'm a newby, I saw your code and it looks good.  I wonder if there is a way to ,instead of a file dialog, save a mergefield as part of the field name?





Cindy Ellis said:


> Hi,
> I have used the following macro to save mail merge output to individual files based on the value of a field in the datasource...maybe you can modify it to do what you need, or add in the logic into your macro that merges the data one at a time.  It uses a different approach than the macros I found on the web.  Instead of merging then separating, it loops through the datasource, merging and saving one record at a time.
> The macro allows the user to select a directory for saving the files.  It depends on an existing mail merge main document that already contains merge fields and a link to the datasource.
> Hope this helps,
> 
> 
> 
> Code:
> __
> 
> 
> Sub merge1record_at_a_time()'
> ' merge1record_at_a_time Macro
> '
> '
> Dim fd As FileDialog
> 
> 
> 'Create a FileDialog object as a Folder Picker dialog box.
> Set fd = Application.FileDialog(msoFileDialogFolderPicker)
> With fd
> 
> 
> 'Use the Show method to display the Folder Picker dialog box and return the user's action.
> 'The user pressed the button.
> If .Show = -1 Then
> For Each vrtSelectedItem In .SelectedItems
> 
> 
> 'vrtSelectedItem is aString that contains the path of each selected item.
> 'You can use any file I/O functions that you want to work with this path.
> 'This example displays the path in a message box.
> SelectedPath = vrtSelectedItem
> 
> 
> Next vrtSelectedItem
> 
> 
> Else
> MsgBox ("No Directory Selected.  Exiting")
> Exit Sub
> End If
> End With
> 
> 
> 'Set the object variable to Nothing.
> Set fd = Nothing
> 
> 
> Application.ScreenUpdating = False
> 
> 
> MainDoc = ActiveDocument.Name
> ChangeFileOpenDirectory SelectedPath
> For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = i
> .LastRecord = i
> .ActiveRecord = i
> docname = .DataFields("ASP_Print").Value & ".docx"      ' ADDED CODE
> End With
> .Execute Pause:=False
> Application.ScreenUpdating = False
> 
> End With
> ActiveDocument.SaveAs FileName:=docname, FileFormat:= _
> wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
> :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
> :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
> SaveAsAOCELetter:=False
> ActiveWindow.Close
> 
> 
> Windows(MainDoc).Activate
> Next i
> Application.ScreenUpdating = True
> 
> 
> End Sub


----------



## hsolsc

Hi Kryptonian and Cindy.
Thanks for this thread - it's been really helpful. I'm stuck on the last hurdle. I used Kryptonian's new code so that the PDFs don't open but am left with a pile of merged word docs open. I've tried a few variations on this:




		Code:
__


ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        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

[/QUOTE]

But that doesn't work. Any tips welcome.
Hans


----------



## hsolsc

Solved my own problem! 

I made a typo when I posted my problem. I had meant to say that "ActiveDocument.Close" didn't work, but mistakenly typed "ActiveWindow.Close". I realized I hadn't tried that so put it in and hey presto!

Final code for anyone else:         



		Code:
__


ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        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 SaveChanges:=wdDoNotSaveChanges


Hans


----------



## hminney

Just wanted to say "Thank you so much" for putting up such a useful thread!

Now how can i get it to:
1) merge to PDF and then attach to an email and send the email
2) instead of looping through each document, loop through each change of company name and print to PDF all records for that company name, then attach to email and send the email.

Thanks everyone!


----------



## Macropod

That is rather beyond the capabilities of anything discussed in this thread. I believe it can be done with the Many-to-One Mail Merge add-in by Doug Robbins, available at http://bit.ly/1hduSCB


----------



## vickilou

HELP!!! I am fairly new to VBA etc and im trying to copy the macro into with Cindy's added bit too. I don't seem to having any luck - are you able to provide an idiots step by step guide of when I should be creating/adding the macro - how I should be completing the merge etc.


----------



## Macropod

For PC macro installation & usage instructions, see: Installing Macros


----------



## vickilou

Macropod said:


> For PC macro installation & usage instructions, see: Installing Macros



Thank you for your link - didn't quite answer my question ( I no how to add it in) just in unsure as to what stage in adding it in and when I should be running it on terms of the mail merge wizard steps


----------



## Macropod

You add the macro to your mailmerge main document and, instead of the usual 'Finish' stage, run the macro. Alternatively, if you rename the macro to MailMergeToDoc, clicking on 'Finish & Merge>Edit individual Documents' will run it automatically.


----------



## vickilou

Macropod said:


> You add the macro to your mailmerge main document and, instead of the usual 'Finish' stage, run the macro. Alternatively, if you rename the macro to MailMergeToDoc, clicking on 'Finish & Merge>Edit individual Documents' will run it automatically.



When I run the macro I get the following message:

Complile error: Variable not defined and it highlights the first line Sub merge1record_at_a time ()


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## Macropod

I suggest you use my version, which you'll find under the heading _Send Mailmerge Output to Individual Files_, in the *Mailmerge Tips and Tricks *thread at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge


----------



## Samashan

Hi guys I'm new to the forum but I've been using the tips here for around 2 years now. I'm wondering if anyone knows why i get the error "Directory name is not valid"? It seems no matter what version I use, it throws the same runtime error.


----------



## Macropod

Without knowing which 'version' you're using or how your system's folder structure compares with the code you're using, no one tell you what specific changes to make. Evidently, though, the folder your code specifies doesn't exist.


----------



## Samashan

Hi Macropod, I managed to solve this late last night. Turns out the datafield I was pulling the value from contained a special character, as it worked when I used Date() as the filename. Maybe this will help somebody with the same issue, as the error was way of its target


----------



## Saved

Hi All,

I've been using this macro for the last three years and thanks again to the people who contributed.  I have a small problem though, the macro gets slower and slower over time.  It runs at one pdf file per second for around an hour and then gradually starts slowing down, until it's almost one file per seven seconds.  This significantly slows down the entire exercise as my files run into tens of thousands.  This could have nothing to do with the macro itself, but I would be grateful if someone tells me a workaround...thanks.

If someone knows of a tool that can do the same function of creating pdf files from spreadsheets (along with images from image paths in the spreadsheet), please let me know.  I can pay for it...


----------



## Macropod

Saved said:


> I've been using this macro for the last three years and thanks again to the people who contributed.  I have a small problem though, the macro gets slower and slower over time.


Which of the many different macros in this thread do you mean? Have you tried the later iterations - especially the macro in either of the links I supplied in post #81?


----------



## Saved

Oops, sorry.  The code I'm using is as below.  It works perfectly, apart from the slowing down part.
----------------------------------------------------------


		Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog
    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems
                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem

        Next vrtSelectedItem
Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With
'Set the object variable to Nothing.
    Set fd = Nothing
Application.ScreenUpdating = False

MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docName = .DataFields("Candidate_Regn_ID").Value & ".PDF"      ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
    ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        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 SaveChanges:=False
Next i
Application.ScreenUpdating = True
End Sub


----------



## Macropod

Although the code in the links I provided in post #81 is more efficient, the most likely reason for the significant slowdown with so many records is that Word isn't being given any time for its housekeeping. To that end, you could replace your redundant second instance of:
Application.ScreenUpdating = False
with:
If i Mod 100 = 0 Then DoEvents

You might also replace:


		Code:
__


    ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
        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 SaveChanges:=False

with:


		Code:
__


    With ActiveDocument
      .SaveAs FileName:=docName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With


----------



## Saved

I did the above and the macro has stopped working now, Word just hangs...please help.


----------



## Macropod

I missed that you're already adding ".pdf" to docName. Try changing:
.SaveAs FileName:=docName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
to:
.SaveAs FileName:=SelectedPath & "" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
and deleting:
ChangeFileOpenDirectory SelectedPath


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## Saved

Tried it Paul, no luck. Word crashes


----------



## Macropod

I ran a test on a file with 700 records using the suggested changes and the code ran to completion with no errors. That suggests an error in your implementation.


----------



## Saved

Would you be kind enough to post the code, I'm somehow not able to get it running.  Tried numerous times.


----------



## Macropod

The mods are pretty basic. Attached is the revised code. I've made a couple of other adjustments - like making use of your otherwise unused 'MainDoc' variable and tidying the code structure - but none of that has any effect on whether the code runs or crashes (which it doesn't).


		Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
'
Dim fd As FileDialog, vrtSelectedItem, SelectedPath As String
'Create a FileDialog object as a Folder Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
    'Use the Show method to display the Folder Picker dialog box and return the user's action.
    'The user pressed the button.
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            'vrtSelectedItem is aString that contains the path of each selected item.
            'You can use any file I/O functions that you want to work with this path.
            'This example displays the path in a message box.
            SelectedPath = vrtSelectedItem
        Next vrtSelectedItem
    Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
    End If
End With
'Set the object variable to Nothing.
Set fd = Nothing
Application.ScreenUpdating = False
Dim MainDoc As Document, i As Long, docName As String
Set MainDoc = ActiveDocument
For i = 1 To MainDoc.MailMerge.DataSource.RecordCount
    With MainDoc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            docName = .DataFields("Candidate_Regn_ID").Value & ".PDF"      ' ADDED CODE
        End With
        .Execute Pause:=False
    End With
    If i Mod 100 = 0 Then DoEvents
    With ActiveDocument
      .SaveAs FileName:=SelectedPath & "\" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
Next i
Set MainDoc = Nothing
Application.ScreenUpdating = True
End Sub

PS: You might want to experiment with changing the 100 in 'i Mod 100' to a lower or higher number for possible improvements in performance.


----------



## Saved

Still not working and I'm now sure it's a problem at my side.  I'll try it on another system and it should hopefully work.  Thanks a ton!


----------



## Saved

Tried it on another system and it now works well, thanks again!  Does slow down a bit, but only after many hours of running.  It resumes speed once I restart Word, guess I'll just have to make do with it now.
I'm also noticing that the macro continues running even if the image is not inserted into the document, is there a way to get it to stop when an image is not found?


----------



## Macropod

Saved said:


> I'm also noticing that the macro continues running even if the image is not inserted into the document, is there a way to get it to stop when an image is not found?


There is nothing in the code that has anything to do with images, so it's a bit hard to tell it to do that. You might also check and ensure that the data source doesn't have empty used rows that add to processing time without producing any output.


----------



## petiokos

Hi,
I am using this macro successfully for PDF since a while now. I am not an experienced macro user sadly thus I would need some help on how the macro could be transformed in order to create .doc or .docx as output format instead of pdf. The macro in the post #3 does not work for me. I would be really thankful for any help.




Soramanego said:


> This is a version that function for .pdf
> 
> 
> 
> Code:
> __
> 
> 
> Sub Merge_to_pdf() '
> ' merge1record_at_a_time Macro
> '
> '
> Dim fd As FileDialog
> 
> 
> 'Create a FileDialog object as a Folder Picker dialog box.
> Set fd = Application.FileDialog(msoFileDialogFolderPicker)
> With fd
> 
> 
> 'Use the Show method to display the Folder Picker dialog box and return the user's action.
> 'The user pressed the button.
> If .Show = -1 Then
> For Each vrtSelectedItem In .SelectedItems
> 
> 
> 'vrtSelectedItem is aString that contains the path of each selected item.
> 'You can use any file I/O functions that you want to work with this path.
> 'This example displays the path in a message box.
> SelectedPath = vrtSelectedItem
> 
> 
> Next vrtSelectedItem
> 
> 
> Else
> MsgBox ("No Directory Selected.  Exiting")
> Exit Sub
> End If
> End With
> 
> 
> 'Set the object variable to Nothing.
> Set fd = Nothing
> 
> 
> Application.ScreenUpdating = False
> 
> 
> MainDoc = ActiveDocument.Name
> ChangeFileOpenDirectory SelectedPath
> For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = i
> .LastRecord = i
> .ActiveRecord = i
> docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"      ' ADDED CODE
> End With
> .Execute Pause:=False
> Application.ScreenUpdating = False
> 
> End With
> ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
> 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 SaveChanges:=False
> 
> 
> Next i
> Application.ScreenUpdating = True
> 
> End Sub


----------



## Macropod

Using the code in post #94, change:
docName = .DataFields("Candidate_Regn_ID").Value & ".PDF"      ' ADDED CODE
to:
docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".docx"      ' ADDED CODE
and change:
.SaveAs FileName:=SelectedPath & "" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
to:
.SaveAs FileName:=SelectedPath & "" & docName, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False


----------



## petiokos

Thank you! This was very helpful!



Macropod said:


> Using the code in post #94, change:
> docName = .DataFields("Candidate_Regn_ID").Value & ".PDF"      ' ADDED CODE
> to:
> docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".docx"      ' ADDED CODE
> and change:
> .SaveAs FileName:=SelectedPath & "" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
> to:
> .SaveAs FileName:=SelectedPath & "" & docName, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## kryptonian

Wow! This thread is almost 4 years old and still helping folks with their mail merge to doc/pdf problems!

I'm doing a couple of projects again with mail merge and had to revert back to this thread after a couple of years and I'm happy this helped more than just me when I posted this question.


----------



## kryptonian

Let's take this up a notch...

So, I have an Excel macro to open Word and run a Mail Merge macro in Word.  The problem is I am able to open the file but the Word macro is not starting.  I'm sure that there is no problem with the Word macro as I can run this with no issues in Word.  I think the issue lies when opening Word from Excel macro.  When opening the file in Word, I get prompted with:

"Opening this document will run the following SQL command: 

SELECT * FROM 'Raw$' Data fro

Data from your database will be placed in this document. Do you want to continue?"

To which I would need to click YES.

I don't get this prompt when I open Word via Excel Macro so I assume this is an automatic No selected and hence the Word macro is not running.

Any suggestions??? TIA!!!


----------



## Macropod

Automating a mailmerge from Excel requires a different approach.

The following macro automates a mailmerge from Excel. The code assumes you have a document named 'MailMergeMainDocument.docx' stored in the same folder as the Excel workbook. That document should be saved as an ordinary, macro-free, document, _not_ as a mailmerge main document. As coded, the macro also assumes a standard query, processing all records from Sheet1. Change the sheet references, as appropriate. If you're using filtering, you'd have to add that to the macro's SQLStatement, too. Each record's output is sent to a new file in the same folder as the Excel workbook, using the 'Last_Name' & 'First_Name' fields in the data source for the filenames (change these to suit your requirements). PDF & DOCX save formats are catered for. Illegal filename characters (i.e. "*./\:?|) are replaced with underscores.

For testing purposes, you might want to comment-out the line 'wdApp.Visible = False'.


		Code:
__


Sub RunMerge()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String
Dim i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone 
StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & "\"
StrMMDoc = StrMMPath & "MailMergeMainDocument.doc"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `Sheet1$`"
    For i = 1 To .DataSource.RecordCount
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("LAST_NAME")) = "" Then Exit For
        StrName = .DataFields("LAST_NAME") & "_" & .DataFields("FIRST_NAME")
      End With
      .Execute Pause:=False      
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With wdApp.ActiveDocument
        .SaveAs Filename:=StrMMPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        '.SaveAs Filename:=StrMMPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    Next i
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Close SaveChanges:=False
End With
wdApp.DisplayAlerts = wdAlertsAll
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub

If you already have a mailmerge main document set up with (or without) filtering, you could use the following Word macro to retrieve the SQL statement:


		Code:
__


Sub GetSQL()
With ActiveDocument.MailMerge
  If .MainDocumentType <> wdNotAMergeDocument Then
    MsgBox "Mail Merge Query String:" & vbCr & .DataSource.QueryString
  Else
    MsgBox "Not A Merge Document"
  End If
End With
End Sub


----------



## kryptonian

Thanks, Paul!

I'm trying out your code but I'm getting flagged off the bat at "wdApp As New Word.Application".

When I comment this line out, I get flagged at this line -- "Set wdDoc = wdApp.Documents.Open(FileName:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)"

...and just to confirm, I place this in Excel, right?


----------



## Macropod

Did you read the first line in the macro? And yes, you'd run the macro in Excel.


----------



## kryptonian

kryptonian said:


> Thanks, Paul!
> 
> I'm trying out your code but I'm getting flagged off the bat at "wdApp As New Word.Application".
> 
> When I comment this line out, I get flagged at this line -- "Set wdDoc = wdApp.Documents.Open(FileName:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)"
> 
> ...and just to confirm, I place this in Excel, right?



Solved this.  I had to set the reference from Tools>References>Microsoft Word for it to work. That worked but now I'm hanging mid code at the .mailmerge part.


----------



## Macropod

Provided you've-
• actually done the mailmerge setup in your mailmerge main document;
• referred to it correctly (e.g. do you need to change the document name: 'MailMergeMainDocument.doc' and is it in the same folder as the Excel workbook?);
• used the correct SQL statement (e.g. is the worksheet named 'Sheet1' - note the necessary inclusion of $ in the SQL statement?); and
• chosen the correct field(s) for the filename (e.g. do you need to change 'StrName = .DataFields("LAST_NAME") & "_" & .DataFields("FIRST_NAME")'?),
I can see no reason for it hanging. It works just fine when I run it with the correct parameters.


----------



## kryptonian

Finally was able to make it work! Thanks for the assist Paul!


----------



## Macropod

One thing to be aware of when running a mailmerge from the workbook that is also the datasource is that, since this means having the workbook open, some fields might not produce the desired output. Times, for example, may appear as decimal numbers. There are two options for dealing with such peculiarities:
1. Run the merge from a different workbook, preferably with code that ensures the workbook used for the datasource is closed; or
2. Use field coding to convert the data to the correct format. With times, for example, the field code might be:
{QUOTE{SET Time {MERGEFIELD TIMEOFDAY}}{SET ss{=Time*86400 \# 0}}{SET hr {=INT(ss/3600) \# 0}}{SET mn {=INT((ss-hr*3600)/60) \# 0}}{SET AMPM{IF{hr}< 12 "AM" "PM"}}"{=MOD(hr+11,12)+1 \# 0}:{mn \# 00} {REF AMPM}"}
where 'TIMEOFDAY' is the field name.


----------



## Mistolo

Anyways anyone is still helping on this?


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## Mistolo

Hi Cindy,

I'm looking to do something similar to this however i'm a super newbie with macros and need extreme help! Hopefully i'm not too many years late in requesting this?


----------



## Macropod

Too late in requesting what? The thread contains a detailed discussion and whatever your requirements are have most likely already been discussed.


----------



## Mistolo

I've tried going through the Form but honestly I don't entirely understand everything. I'm a super NEWBIE. From what i've read here what i'm looking for can be done but I don't even understand how it can be implemented or understanding reading the codes.

Here's what I need:
I'm looking for a way to run a mail merge, using an excel document that contains data and the word document from the mail merge. I've been able to run the merge but it then makes all of the files show in one singular document. I'd like to save the documents as individual files, and name each file with a specific name that is generated from one of the columns in the excel spread sheet. Ideally if I can gave all of the files that are named with that specific name be located in their own folder that would be great as well.

I hope I was able to be clear enough and this can lead to some help? I would really need babysteps through this or even at least highlighted steps in the code showing where to plug in he file name/destination or how to save the file with a specific name which is generated from a column in excel. 

Thank you so much in advance


----------



## Macropod

Without knowing exactly what you've tried, it's impossible to provide advice on that. Nevertheless, in post #81 I supplied links to generic code designed to do exactly what you say you want to achieve.


----------



## Mistolo

Thank you so much! As you can tell i'm way too much of a NEWBIE that I even didn't fully explain what I've/tried. I'll go back to the very beginning and refer to post #81, try with those generic codes and report back on how it goes. Thank you again!


----------



## JPJensen

Thanks for the fine answers
I need to be able to make mailmerge to pdf conditional on cell a2 <> ""
I got this code working
Now I would like it to merge to pdf only when they line in excel (cell A2) Do While Ark1.Cells (x, 1) <> "" or marked with x
The rest of the lines should not be merged

Does it make sense



> Sub merge1record_at_a_time() '
> ' merge1record_at_a_time Macro
> ' Adapted from:
> 'http://www.mrexcel.com/forum/general-excel-discussion-other-questions/713478-word-2007-2010-mail-merge-save-individual-pdf-files.html
> Dim fd As FileDialog
> 'Create a FileDialog object as a Folder Picker dialog box.
> Set fd = Application.FileDialog(msoFileDialogFolderPicker)
> With fd
> 'Use the Show method to display the Folder Picker dialog box and return the user's action.
> 'The user pressed the button.
> If .Show = -1 Then
> For Each vrtSelectedItem In .SelectedItems
> 'vrtSelectedItem is aString that contains the path of each selected item.
> 'You can use any file I/O functions that you want to work with this path.
> 'This example displays the path in a message box.
> selectedpath = vrtSelectedItem
> Next vrtSelectedItem
> Else
> MsgBox ("No Directory Selected.  Exiting")
> Exit Sub
> End If
> End With
> 'Set the object variable to Nothing.
> Set fd = Nothing
> 
> 
> 
> 
> Application.ScreenUpdating = False
> mainDoc = ActiveDocument.Name
> ChangeFileOpenDirectory selectedpath
> ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstDataSourceRecord
> 'xxxxxxxx
> x = 2 ' added code
> 
> 
> Do While Ark1.Cells(x, 1) <> "" ' added code
> 'xxxxxxxx
> For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = .ActiveRecord
> .LastRecord = .ActiveRecord
> 'moved the .ActiveRecord to just before the Next i
> docname = .DataFields("no").Value & "_" & .DataFields("Adress").Value & ".pdf"      ' ADDED CODE
> End With
> .Execute Pause:=False
> Application.ScreenUpdating = False
> End With
> 'This next code is used if you want Word documents.
> '     ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
> wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
> :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
> :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
> SaveAsAOCELetter:=False
> 'This next code is used if you want PDF documents.
> 'set OpenAfterExport to False so the PDF files won't open after mail merge
> ActiveDocument.ExportAsFixedFormat OutputFileName:=docname, _
> 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
> 
> ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
> 'Windows(mainDoc).Activate (works for us WITH pdfS when this is commented out - ConnDublin
> ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextDataSourceRecord
> Next i
> 'xxxxxx
> Loop 'added code
> 'xxxxxx
> 
> 
> Application.ScreenUpdating = True
> End Sub


----------



## Macropod

You don't need any code for that; all you need do is define the appropriate mailmerge filters in the mailmerge main document. The links in post 81 (https://www.mrexcel.com/forum/gener...vidual-pdf-files-post4551045.html#post4551045) point to all the code all you need - unless you want to force the user to choose the output folder each time.


----------



## JPJensen

Thank you 
I missed that part


----------



## mac232007

Hi Guys!

First of all, thank you so much Cindy for your "merge to pdf" code, it saved me many hours!

However I have an issue I can't seem to wrap my head around. I am trying to mail merge records but to have the data updated between each record prior to merging. 

Let me explain: 

I used one of my merge field (DataFields("HM_VR")) from my word doc and copied it as OLE link into a separate excel sheet. 
All data in the excel sheet is updated and dependent on the reference shown through the OLE link. 
I then did a pasted link of the excel table from the excel sheet into the original word document. 
Thus, when I manually preview each record, the HM_VR data changes and the excel updates and then shows back the correct data in word. (Sometimes it needs to be jump started and have to manually update the link). 
I have two macros (see under), which both work when run separately. I am trying to integrate the first one into the second one so that the ideal macro (call it macro 3) merges the first record (macro 2), then updates the links (macro 1), then merge the second record (macro 2), and so on. I've done everything I can with my limited knowledge and am completely stuck 

Macro 1:  updates all the links without fault. 


		VBA Code:
__


Sub Update()

With Options
        .UpdateFieldsAtPrint = True
        .UpdateLinksAtPrint = True
    End With
    ActiveDocument.Fields.Update
End Sub


Macro 2: Simply merges each record into separate pdfs (thanks Cindy). 


		VBA Code:
__


Sub Merge_to_pdf() '
' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog

    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd

        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem

        Next vrtSelectedItem

        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

Application.ScreenUpdating = False

MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.Mailmerge.DataSource.RecordCount
        With ActiveDocument.Mailmerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                DocName = "DOC NAME -" & .DataFields("HM_VR").Value & ".pdf"      ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = True
        End With
        
    ActiveDocument.ExportAsFixedFormat OutputFileName:=DocName, _
        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 SaveChanges:=False
    

    Next i
Application.ScreenUpdating = True

End Sub



Thanks for the help!!
Luke


----------



## Saved

Cindy Ellis said:


> Hi,
> I have used the following macro to save mail merge output to individual files based on the value of a field in the datasource...maybe you can modify it to do what you need, or add in the logic into your macro that merges the data one at a time.  It uses a different approach than the macros I found on the web.  Instead of merging then separating, it loops through the datasource, merging and saving one record at a time.
> The macro allows the user to select a directory for saving the files.  It depends on an existing mail merge main document that already contains merge fields and a link to the datasource.
> Hope this helps,
> 
> 
> 
> Code:
> __
> 
> 
> Sub merge1record_at_a_time()'
> ' merge1record_at_a_time Macro
> '
> '
> Dim fd As FileDialog
> 
> 
> 'Create a FileDialog object as a Folder Picker dialog box.
> Set fd = Application.FileDialog(msoFileDialogFolderPicker)
> With fd
> 
> 
> 'Use the Show method to display the Folder Picker dialog box and return the user's action.
> 'The user pressed the button.
> If .Show = -1 Then
> For Each vrtSelectedItem In .SelectedItems
> 
> 
> 'vrtSelectedItem is aString that contains the path of each selected item.
> 'You can use any file I/O functions that you want to work with this path.
> 'This example displays the path in a message box.
> SelectedPath = vrtSelectedItem
> 
> 
> Next vrtSelectedItem
> 
> 
> Else
> MsgBox ("No Directory Selected.  Exiting")
> Exit Sub
> End If
> End With
> 
> 
> 'Set the object variable to Nothing.
> Set fd = Nothing
> 
> 
> Application.ScreenUpdating = False
> 
> 
> MainDoc = ActiveDocument.Name
> ChangeFileOpenDirectory SelectedPath
> For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = i
> .LastRecord = i
> .ActiveRecord = i
> docname = .DataFields("ASP_Print").Value & ".docx"      ' ADDED CODE
> End With
> .Execute Pause:=False
> Application.ScreenUpdating = False
> 
> End With
> ActiveDocument.SaveAs FileName:=docname, FileFormat:= _
> wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
> :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
> :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
> SaveAsAOCELetter:=False
> ActiveWindow.Close
> 
> 
> Windows(MainDoc).Activate
> Next i
> Application.ScreenUpdating = True
> 
> 
> End Sub



I've used this code for years now and it works amazing. I used it to generate PDFs and upload them onto a server where the users can go and download their file. It so happens that I now need to take this task into my website. Basically, there will be a UI on the website with two fields where a user enters his information; if both match with his details on the database, his respective PDF has to be generated and given to him as a download. I did my best trying to find a tool online, but couldn't really locate anything. Would someone be able to help with this? I am willing to post it on the consulting page too if required.


----------



## kryptonian

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files.  Currently, we do it the long way, saving it one PDF at a time using Save As.  This works if it only a handful of letters but it becomes a pain when we're processing hundreds!  Data source is an Excel file.

Any help would be greatly appreciated!


----------



## Saved

Macropod said:


> You don't need any code for that; all you need do is define the appropriate mailmerge filters in the mailmerge main document. The links in post 81 (Word 2007/2010 Mail Merge to save to individual PDF files) point to all the code all you need - unless you want to force the user to choose the output folder each time.



Paul, is there a way to specify the pdf quality in this code? I run 1000s of files via this code and they all end up getting saved with print quality (big file size), whereas I just need them with minimal screen viewing quality. I know there are two quality options in Word when we save as pdf, but how do we do that in the code please?


----------



## Macropod

No. To do that, you'd need to use the ExportAsFixedFormat Method with OptimizeFor:=wdExportOptimizeForOnScreen. For some sample ExportAsFixedFormat code, see post #73 in this thread.


----------



## Saved

Thank you for replying Paul, unfortunately I have been using this macro with just a 'copy-paste' approach all these years and it works perfect to this day. I am nowhere close to writing/editing the code myself. Below is the code I use. Would be very grateful if you could help me out.



		VBA Code:
__


Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
'
Dim fd As FileDialog, vrtSelectedItem, SelectedPath As String
'Create a FileDialog object as a Folder Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
    'Use the Show method to display the Folder Picker dialog box and return the user's action.
    'The user pressed the button.
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            'vrtSelectedItem is aString that contains the path of each selected item.
            'You can use any file I/O functions that you want to work with this path.
            'This example displays the path in a message box.
            SelectedPath = vrtSelectedItem
        Next vrtSelectedItem
    Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
    End If
End With
'Set the object variable to Nothing.
Set fd = Nothing
Application.ScreenUpdating = False
Dim MainDoc As Document, i As Long, docName As String
Set MainDoc = ActiveDocument
For i = 1 To MainDoc.MailMerge.DataSource.RecordCount
    With MainDoc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            docName = .DataFields("reg_no").Value & ".PDF"      ' ADDED CODE
        End With
        .Execute Pause:=False
    End With
    If i Mod 100 = 0 Then DoEvents
    With ActiveDocument
      .SaveAs FileName:=SelectedPath & "\" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
Next i
Set MainDoc = Nothing
Application.ScreenUpdating = True
End Sub


----------



## Macropod

Change:


		VBA Code:
__


    With ActiveDocument
      .SaveAs FileName:=SelectedPath & "\" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With

to:


		VBA Code:
__


    With ActiveDocument
      .ExportAsFixedFormat OutputFileName:=SelectedPath & "\" & docName, _
          ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, _
          OptimizeFor:=wdExportOptimizeForOnScreen, Range:=wdExportAllDocument, _
          From:=1, To:=1, Item:=wdExportDocumentContent, IncludeDocProps:=True, _
          KeepIRM:=True, CreateBookmarks:=wdExportCreateNoBookmarks, _
          DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False
      .SaveAs FileName:=SelectedPath & "\" & docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With


----------



## Saved

Thank you again Paul, I implemented this and the macro runs perfect, but for some reason the file sizes are the same as before (around 1.5 MB). However, if I manually save as pdf in Word with minimal quality it is showing 800 kb.


----------



## Macropod

You might try setting some/all of the other 'True' properties to 'False', in case the added metadata is increasing the file sizes. I can't see that making a dramatic difference, though.


----------

