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:=False, ReadOnly:=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
|