Excel 2020 vba mailmerge

A classic!

Mail Merge in Excel 2020 using VBA is a powerful feature that allows you to automate the process of combining data from an Excel worksheet with a document template to create multiple output documents. Here's a step-by-step guide to get you started:

Prerequisites:

1. Excel 2020 with VBA enabled (File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable all macros)
2. A document template (e.g., Word document, PDF, or text file)
3. A worksheet with data to be merged (e.g., names, addresses, etc.)

Step 1: Create a new VBA module

1. Open the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic in the ribbon.
2. In the Editor, click Insert > Module to create a new module.

Step 2: Declare variables and set up the Mail Merge

1. Declare variables to store the document template path, data range, and output folder:

   
   Dim docTemplate As String
   Dim dataRange As Range
   Dim outputFolder As String

docTemplate = "C:\Path\To\DocumentTemplate.docx" dataRange = Range("A1:E10") ' adjust to your data range outputFolder = "C:\Path\To\Output\Folder"

2. Set up the Mail Merge object:
```vb
Dim mm As Object
Set mm = CreateObject("Word.Application")

Step 3: Open the document template and set up the Mail Merge

1. Open the document template using the mm object:

   mm.Documents.Open docTemplate

2. Set up the Mail Merge:

   mm.MailMerge.OpenDataSource dataRange, _
    wdMergeSourceExcel, _
    wdMergeUseFirstRowAsHeader

   Step 4: Perform the Mail Merge

3. Perform the Mail Merge:

   mm.MailMerge.Execute

   Step 5: Save the output documents

4. Loop through the output documents and save them to the specified output folder:

   Dim doc As Object
   For Each doc In mm.Documents
    doc.SaveAs outputFolder & "\Output_" & doc.Name, wdFormatDocument
   Next doc

   Step 6: Clean up

5. Close the document template and release the mm object:

   mm.Documents.Close
   Set mm = Nothing

   Full code:

   Sub MailMerge()
    Dim docTemplate As String
    Dim dataRange As Range
    Dim outputFolder As String
    Dim mm As Object
   
    docTemplate = "C:\Path\To\DocumentTemplate.docx"
    dataRange = Range("A1:E10") ' adjust to your data range
    outputFolder = "C:\Path\To\Output\Folder"
   
    Set mm = CreateObject("Word.Application")
   
    mm.Documents.Open docTemplate
    mm.MailMerge.OpenDataSource dataRange, _
        wdMergeSourceExcel, _
        wdMergeUseFirstRowAsHeader
    mm.MailMerge.Execute
   
    Dim doc As Object
    For Each doc In mm.Documents
        doc.SaveAs outputFolder & "\Output_" & doc.Name, wdFormatDocument
    Next doc
   
    mm.Documents.Close
    Set mm = Nothing
   End Sub

   Tips and Variations:

• Adjust the dataRange variable to match your data range.
• Customize the output folder and file naming convention.
• Use wdMergeUseHeaderRowAsFirstRecord instead of wdMergeUseFirstRowAsHeader if your data has a header row.
• Use wdFormatPDF instead of wdFormatDocument to save the output documents as PDF files.
• You can also use this code as a starting point and modify it to suit your specific needs, such as adding error handling or customizing the Mail Merge settings.

I hope this helps you get started with Mail Merge in Excel 2020 using VBA!