Solved Mail merge from Excel to PDF's with specific naming of files

February 15, 2013 at 00:42:34
Specs: Windows 7
Hi Guys,

I am all new to writing macros and VBA, so I need a bit of support now since I seem to have lost overview.

What I am trying to do is a mail merge directly from Excel (a word file with layout and mergefields are already done, I just wish to call it from Excel) with output as PDF files, one for each letter (each having their information in a row in Excel as usually for mail merge).
Moreover, I would like to name the PDF file with part of the mail merge - in specific the name of the receiver of the letter (stated in column A).
Finally, I would like to add a piece of code making sure that I would not have to manually click "ok" to the "Opening this document will run the following SQL command: SELECT * FROM 'Sheet1$' ".

Can you brilliant guys please help me?

The code I have for now is:


Option Explicit

Sub PrintCPIP()
' Sub that hopefully will make mail merges of several data as pdf (should end up with several pdf's) _
and moreover name the pdf-files after the values of the first 3 columns in the merge.

Dim wdOutputName, wdInputName, PDFFileName As String
Dim x As Long
PDFFileName = ThisWorkbook.Path & "\Letter " & x & ".pdf" 'I would like the name of the file to include _
the first and middle name of the person
wdInputName = ThisWorkbook.Path & "\Example.docx"
Const wdFormLetters = 0, wdOpenFormatAuto = 0
Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = 3

' open the mail merge layout file
Dim wdDoc As Object
Set wdDoc = GetObject(wdInputName, "Word.document")

wdDoc.Application.Visible = False

For x = 1 To 5

With wdDoc.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

' show and save output file
wdDoc.Application.Visible = False
wdDoc.ExportAsFixedFormat PDFFileName, 17 ' This line saves a .pdf-version of the mail merge

Next x

' cleanup
wdDoc.Close SaveChanges:=False
Set wdDoc = Nothing

MsgBox "Your pdf('s) has now been saved!"

End Sub


See More: Mail merge from Excel to PDFs with specific naming of files

Report •


#1
February 15, 2013 at 00:52:06
An example of both Excel and Word documents should be available as .zip via the link below for the next 7 days:

http://www.fileconvoy.com/dfl.php?i...


Report •

#2
February 15, 2013 at 03:00:21
✔ Best Answer
Hi

Here is a version that will get you the names in the file name. Getting rid of the Word messages will take some work and I'm not sure if I can get around it nt being that knowledgeable in Word.

Sub PrintCPIP()
' Sub that hopefully will make mail merges of several data as pdf (should end up with several pdf's) _
and moreover name the pdf-files after the values of the first 3 columns in the merge.

 Dim wdOutputName, wdInputName, PDFFileName As String
 Dim x As Long
 Dim nRows As Long
 
    wdInputName = ThisWorkbook.Path & "\Example.docx"
    Const wdFormLetters = 0, wdOpenFormatAuto = 0
    Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = 3
   
  nRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row - 1   'This will get you the number of records "-1" accounts for header
    
    ' open the mail merge layout file
    Dim wdDoc As Object
    
    Set wdDoc = GetObject(wdInputName, "Word.document")
    
    wdDoc.Application.Visible = False

    
    For x = 1 To nRows

    With wdDoc.MailMerge
         .MainDocumentType = wdFormLetters
         .Destination = wdSendToNewDocument
         .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
         .Execute Pause:=False
    End With

    ' show and save output file
 
'cells(x+1,1) and (x+1,2) references the first and second cells starting in row 2 and increasing by 1 row with each loop
 PDFFileName = ThisWorkbook.Path & "\Letter " & Sheets(1).Cells(x + 1, 1) & " " & Sheets(1).Cells(x + 1, 2) & ".pdf" 'I would like the name of the file to include _

    wdDoc.Application.Visible = False
    wdDoc.ExportAsFixedFormat PDFFileName, 17   ' This line saves a .pdf-version of the mail merge
    
    Next x

    ' cleanup
    wdDoc.Close SaveChanges:=False
    Set wdDoc = Nothing

MsgBox "Your pdf('s) has now been saved!"

End Sub


Report •

#3
February 15, 2013 at 06:14:08
Hi AlteK

Your solution for naming the files works perfectly! Thx!
It is quite ok with the message from Word, it's not something important, it's just annoying but I think I'll survive :-)

However, I need to somehow "update" the mail merge during every run since the content of all 5 letters is the same... You have any idea of how to do that?

/ Stivdie


Report •

Related Solutions

#4
February 15, 2013 at 09:56:11
What do you mean when you say "update the mail merge"?

Report •

#5
February 19, 2013 at 05:13:41
I mean that it should "swich" to the information in next row in the Excel sheet. Right now all the pdf's show the information in 1st row (after the row with headlines).

Report •

#6
March 7, 2013 at 04:47:17
Nobody's able to help make the last part of the mail merge work, so I won't get identical letters with different names?

Report •


Ask Question