Readdy Write

Automatically create form letters from Excel with filter

02.10.2018 (👁13241)


 

This example shows how to create form letters in Word with little effort from Excel.

The raw address data are stored in Excel. The data is filtered via a small Pivot and key then the filtered form letters are created.

 

Code: vba

Subject: Excel, Pivot Word Mail Merge MailMerge

Files attached for download

 

 

 

 

 

address data

The data is collected as raw data in an Excel file.

Here in the excel sheet: raw data from cell A1 to G10000

 

Serienbrief_Adressen

In the worksheet: Mail Merge_Addresses, the raw data is combined with a classic pivot table.

It turns off the pivot table on pivot properties: classic view and all subtotals and totals.

 

Filter and Select Data:

in Pivot Table, you can now filter and customize the output using the drop-down boxes in the table header cells.

These changes are then automatically filtered in the mail merge output.

 

Start form letter

With the button: Word Mail Merge a macro will be started, which will open the Word file and create a serial letter in the preview.

 

 

Word output

If you go to the open Word document Serienbrief1, then you will find a page for each address as a form letter.

Important: the output should now only contain the addresses that have been filtered in the Excel file.

 

The Word output document will then contain an output page for all addresses.

 

Mail merge template

The original form letter word file is only opened at the beginning of the vba macro and the address fields are automatically linked to the opening Excel file.

 

 

files:

Both files, the Excel file with the addresses and the start button, as well as the mail merge master file are in the same directory.


 

 

Vba macro code

With Alt-F11 you get in the Excel file on the attached macro code.

The actual vba code is in the folder: from Module-> Module1

Here you can adjust the Word file name in the header area and the name of the pivot table

Private Const sWord_Document_Name As String = "Serienbriefe_aus_Excel.docx"

Private Const Table_with_Adresses As String = "Serienbrief_Adressen"

 

 

Vba code

 

'------< SETUP >------

Private Const sWord_Document_Name As String = "Serienbriefe_aus_Excel.docx"

Private Const Table_with_Adresses As String = "Serienbrief_Adressen"

'------< SETUP >------

 

 

 

 

 

Sub Erstelle_Word_Serienbrief_als_Vorschau()

    '--------------------< Word_oeffnen() >--------------------

 

    '< Path >

    Dim sCurrent_Path As String

    sCurrent_Path = ActiveWorkbook.Path

 

    Dim sFull_Path_of_Word_File

    sFull_Path_of_Word_File = sCurrent_Path & "\" & sWord_Document_Name

    '</ Path >

 

    '< Word starten >

    '< with word_refernece >

    'Dim app As Word.Application              '*Verweis Word-dll

    'Set app = New Word.Application           '*Verweis Word-dll

    '</ with word_refernece >

 

    '< with late-binding >

    Dim app As Object                           '*late-binding

    Set app = CreateObject("Word.Application")  '*late-binding

    '</ with late-binding >

    

    app.Visible = True

    app.Activate

    '</ Word starten >

 

    '</ Word Document oeffnen >

    'Dim doc As Object      '*late-binding

    Dim doc As Word.Document 'word-dll

    Set doc = CreateObject("Word.Document")

    Set doc = app.Documents.Open(sFull_Path_of_Word_File, ConfirmConversions:=FalseReadOnly:=False, AddToRecentFiles:=False)

    '</ Word Document oeffnen >

    

    Dim wb As Workbook

    Set wb = ThisWorkbook

    

    Dim sExcel_Filename As String

    sExcel_Filename = ThisWorkbook.FullName

 

    '< Datenquelle einstellen >

    '*versions 2007+

    '*Datenquelle für den Seriendruck

    doc.MailMerge.MainDocumentType = wdFormLetters

    doc.MailMerge.OpenDataSource Name:="" & sExcel_Filename & "",

    ReadOnly:=False, LinkToSource:=True,

    Format:=wdOpenFormatAuto,

    Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & sExcel_Filename & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;" _

    , SQLStatement:="SELECT * FROM `" & Table_with_Adresses & "$`",

    SubType:=wdMergeSubTypeAccess

    '</ Datenquelle einstellen >

 

 

 

 

    '< output >

    doc.MailMerge.Destination = wdSendToNewDocument

    doc.MailMerge.Execute Pause:=False

    '</ output >

 

    doc.Close SaveChanges:=False

    Set doc = Nothing

    Set app = Nothing

    '--------------------</ Word_oeffnen() >--------------------

End Sub

 

 

 

 

 


0,00 €