Start MailMerge for 3 different Word documents in Excel depending on value in column A

[Kalü]

Hi guys

I am trying to make a button in my excel-sheet that simply starts a MailMerge in word (for that my colleagues can use my work, too).

Depending on the value in row A it has to open 3 different word docs and do the mail merge:
- if value in A = 1 then doe the mailmerge for "sb1.docx"
- if value in A = 2 then doe the mailmerge for "sb2.docx"
- if value in A = 3 then doe the mailmerge for "sb3.docx"

Because we need single word docs for every entry in excel, I already use a macro, when i open the word docs and start mail merge manually (sadly my colleagues dont have enough knowhow to do that, thats why I try to create the button in excel):

Sub aaaaSerienbrief()
    ' set variables
    Dim iBrief As Integer, sBrief As String
    Dim AppShell As Object
    Dim BrowseDir As Variant
    Dim Path As String
    
    ' catch any errors
    On Error GoTo ErrorHandling
    
    ' determine path
    Set AppShell = CreateObject("Shell.Application")
    Set BrowseDir = AppShell.BrowseForFolder(0, "Speicherort für Serienbriefe auswählen", 0, 16) _
 _
 _
 _

    
    If BrowseDir = "Desktop" Then
        Path = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    Else
        Path = BrowseDir.items().Item().Path
    End If
    
    If Path = "" Then GoTo ErrorHandling
        
    Path = Path & "\Serienbrief-" & Format(Now, "dd.mm.yyyy-hh.mm.ss") & "\"
    MkDir Path
    
    On Error GoTo ErrorHandling
        
    ' hide application for better performance
    MsgBox "Serienbriefe werden exportiert. Dieser Vorganag kann einige Minuten dauern -  _
Microsoft Word wird während dieser Zeit ausgeblendet", vbOKOnly + vbInformation
    Application.Visible = False
 
    ' create bulkletter and export as docx
    With ActiveDocument.MailMerge
        .DataSource.ActiveRecord = 1
        Do
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = .ActiveRecord
                .LastRecord = .ActiveRecord
                sBrief = Path & .DataFields("ID").Value & ".docx"
            End With
            .Execute Pause:=False
        
            If .DataSource.DataFields("ID").Value > "" Then
                ActiveDocument.SaveAs FileName:=sBrief, FileFormat:=wdFormatdocx
            End If
            ActiveDocument.Close False
        
            If .DataSource.ActiveRecord < .DataSource.RecordCount Then
                .DataSource.ActiveRecord = wdNextRecord
            Else
                Exit Do
            End If
        Loop
    End With
    
    ' error handling
ErrorHandling:
    Application.Visible = True
 
    If Err.Number = 76 Then
        MsgBox "Der ausgewählte Speicherort ist ungültig", vbOKOnly + vbCritical
    ElseIf Err.Number = 5852 Then
        MsgBox "Das Dokument ist kein Serienbrief"
    ElseIf Err.Number = 4198 Then
        MsgBox "Der ausgewählte Speicherort ist ungültig", vbOKOnly + vbCritical
    ElseIf Err.Number = 91 Then
        MsgBox "Exportieren von Serienbriefen abgebrochen", vbOKOnly + vbExclamation
    ElseIf Err.Number > 0 Then
        MsgBox "Unbekannter Fehler: " & Err.Number & " - Bitte Makro erneut ausführen.",  _
vbOKOnly + vbCritical
    Else
        MsgBox "Serienbriefe erfolgreich exportiert", vbOKOnly + vbInformation
    End If
End Sub

Can I somehow integrate this word-vba-code into excel and make a button that automatically merges all entries into the right word docs?

[Kenneth Hobson]

This should give you a good start. You will need to change the sheet name and add the column A loop counter. Both are trivial. If you need help with that, post back. Try it for one run first.

Sub Test_MergeRun()
  'Const wdocOutput As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Macros\ATT_Looper.txt"
  Const wdocForm As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Mail Merges\ATT_NE_MM_orig.docx"
  Const xlData As String = "\\Sasquatch\Common IS$\GP\Transfer Attendance\Mail Merge Creator.xlsm"
  Const StrSQL As String = "SELECT * FROM 'NonExempt$'"
  
  MergeRun wdocForm, xlData, StrSQL
End Sub

'If Word found locked fields shows after a merge, see this workaround:
'http://support.microsoft.com/kb/292155 - due to inline text in Autoshape layout.
'DataSources, https://support.office.com/en-us/article/Data-sources-you-can-use-for-a-mail-merge-9de322a6-f0f9-448d-a113-5fab317d9ef4

'MergeRun frmDoc, datFile, "SELECT * FROM `Sheet1$`"
'Requires Tools > References > Microsoft Word 11.0 Object Library
Sub MergeRun(frmFile As String, datFile As String, _
  SQL As String, _
  Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _
  Optional iNoCopies As Integer = 1)
  
  Dim wdApp As Word.Application
  Dim myDoc As Word.Document
  
  'Tell user what file is missing and exit.
  If Dir(frmFile) = "" Then
    MsgBox "Form file does not exist." & vbLf & frmFile, _
      vbCritical, "Exit - Missing Form File"
  End If
  If Dir(datFile) = "" Then
    MsgBox "Data file does not exist." & vbLf & datFile, _
      vbCritical, "Exit - Missing Data File"
  End If
  If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
  
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then
      Set wdApp = CreateObject("Word.Application")
  End If
  On Error GoTo errorHandler
  
  With wdApp
   On Error GoTo errorHandler
    wdApp.Application.DisplayAlerts = wdAlertsNone
    
    'Open form file and associate data file
    Set myDoc = .Documents.Open(frmFile, False, True, False)
    .ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    .ActiveDocument.MailMerge.OpenDataSource Name:=datFile, _
      ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
      AddToRecentFiles:=False, PassWordDocument:="", PasswordTemplate:="", _
      WritePassWordDocument:="", WritePasswordTemplate:="", Revert:=False, _
      Format:=wdOpenFormatAuto, Connection:="", SQLStatement:=SQL, SQLStatement1 _
      :="", SubType:=wdMergeSubTypeOther
    'Merge to a new document
    With wdApp.ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    .Visible = True
    
    If bPrint = True Then
      .Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
    End If
    
    If bClose = True Then
      .ActiveDocument.Close False
      .ActiveDocument.Close False
    End If

    wdApp.Application.DisplayAlerts = wdAlertsAll
  End With
     
errorExit:
    On Error Resume Next
    myDoc.Close False
    Set myDoc = Nothing
    Set wdApp = Nothing
    Exit Sub
 
errorHandler:
    MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
    Resume errorExit
End Sub

[macropod]

You are going about this the wrong way, IMHO. You can have all three letters in the same mailmerge main document and use IF fields to determine which one to generate for a given record. No complicated looping etc. is required. As for generating the individual documents, see:
Send Mailmerge Output to Individual Files; and
Run a Mailmerge from Excel, Sending the Output to Individual Files,
in the Mailmerge Tips and Tricks 'Sticky' thread at the top of the Word mailmerge forum: https://www.msofficeforums.com/mail-...ps-tricks.html

[Kalü]

Hello and thank you very much for your replies!

I think I found out how to change the document paths in Kenneth Hobbson's VBA code:

Const wdocForm As String = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\Mail Merges\ATT_NE_MM_orig.docx"
  Const xlData As String = \\Sasquatch\Common IS$\GP\Transfer Attendance\Mail Merge Creator.xlsm

is this right? And can you tell me where to put the paths of all three different word documents and how to add the column A loop?

The problem of macropod's answer is that the 3 letters have completely different text and a different amount of sites. All they have in common is the merge fields...

[macropod]

Originally Posted by Kalü

The problem of macropod's answer is that the 3 letters have completely different text and a different amount of sites

Those issues are quite irrelevant. All you need to manage that is three IF fields for the whole document - one for each letter.

[Kalü]

I dont think that can work. Word doc nomber one has 6 pages. Word doc 2 has 8 pages. Word doc 3 has 12 pages and it is completely different, I also added some different text boxes and other stuff to that three documents... How can I ever manage to make one document out of that?

[Kenneth Hobson]

Sub Test_MergeRun()
  Dim dat$, sql$, p$, doc$, c As Range, r As Range
  
  p = "c:\MyFiles\Word\sb\sb"
  dat = "c:\MyFiles\Excel\Dat\dat.xls"
  sql = "SELECT * FROM 'Sheet1$'"
  Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  
  For Each c In r
    doc = p & c & ".docx"
    MergeRun doc, dat, sql
  Next c
End Sub

[Kalü]

Where do I have to put this loop in teh entire code?
And do I have to make 3 of this loops for every word doc?
or is this:
p = "c:\MyFiles\Word\sb\sb"
numbering itself?

Sorry that I am such a noob with VBA

[Kenneth Hobson]

You have to change the code to suit your needs. I had to make guesses.

 p = "c:\MyFiles\Word\sb\sb"
  dat = "c:\MyFiles\Excel\Dat\dat.xls"
  sql = "SELECT * FROM 'Sheet1$'"
  Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))

p is the path to your docx files with the first 2 letters of your docx filename at the end, sb.

Dat is the full path to your excel data file.

sql gets the data from all of sheet1 in your dat file.

r is the range A2 and down from the current sheet with the suffix docx filenames, 1,2,3, etc.

The code goes into a Module as does the MergeRun() code.

[macropod]

Originally Posted by Kalü

I dont think that can work. Word doc nomber one has 6 pages. Word doc 2 has 8 pages. Word doc 3 has 12 pages and it is completely different, I also added some different text boxes and other stuff to that three documents... How can I ever manage to make one document out of that?

Clearly you haven't bothered to even try it. The number of pages and their content is irrelevant. A single IF field could span hundreds of pages containing text, graphics, tables, links to other documents, etc.

[Kalü]

I didnt know word can handle if functions that refer to a value in excel. Not everyone is so clever and skilled like you. Thats why I am asking my question in this forum
So thanks for your help ---- NOT

[macropod]

Originally Posted by Kalü

I didnt know word can handle if functions that refer to a value in excel.

They don't. Word IF fields can be used to test the mergefield value of each record in your column A.
The fields would be constructed along the lines of:
{IF{MERGEFIELD LetterType}= "1" "All the content for letter 1 goes here"}{IF{MERGEFIELD LetterType}= "2" "All the content for letter 2 goes here"}{IF{MERGEFIELD LetterType}= "3" "All the content for letter 3 goes here"}
or:
{IF«LetterType»= "1" "All the content for letter 1 goes here"}{IF«LetterType»= "2" "All the content for letter 2 goes here"}{IF«LetterType»= "3" "All the content for letter 3 goes here"}

If the letters have the same opening/closing content, that content can be left outside the fields so there's only one version to maintain.

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required. The only potentially-significant limitation is that the content inside the double quotes cannot include more double quotes, but even that can be worked around via the use of paired single quotes.

As for:

Originally Posted by Kalü

So thanks for your help ---- NOT

If you want to be rude, just go away. It is you, after all, who keeps making dumb statements about what you think Word can and cannot do without bothering to accept or try the advice you've been given by those even you admit are more skilled than you.

[Kalü]

How can I see the if-fields after reopening the document?
How can I say the excel macro to not merge entries with 0 in column A?

I found out that the if-fields are already there but kind of hidden and can hardly been seen...

IS there a possibility to save the different textes in something like auto-text-blocks or so, for that the text is not shown in detail when i press CTRL+F9?

Because the text is changing every 14 days and it is really hard to keep an overview if all the texts are together in one.

[macropod]

You can see what's in all the fields by pressing Alt-F9 to toggles the field code display on/off.

The field coding I suggested already won't merge any record that has a 0 in column A.

Pressing Ctrl-F9 creates a set of field braces; it has nothing to do with viewing fields or auto-text. Moreover, embedding your content in an IF field has no effect on that content's formatting.

As for content changing every 14 days, you're going to have that issue whichever approach you take. You might even have a column in your workbook into which you can insert whatever that variable text is, so you can reference it via a mergefield instead of having to edit the mailmerge main document.

[Kalü]

The excel-vba code for mailmerge single documents I copied from your suggested sticky thread is giving me word docs for every entry, also for those that have value 0 in column A (although they are kind of empty, they are still there) and I dont know how to change that

The IF-fields are kind of hidden in the text that should actually follow after the IF... see here for example: https://imgur.com/a/DupAVkf

That makes it kind of hard to keep an overview... Sadly I cannot paste the text into excel cells because of formatting and length At least i tried that before and it didnt take all text into word only the first sentences.

I tried to save Auto-Text and insert that after the "{IF{MERGEFIELD Anz}= "1"- expressions but it inserts only the text. Would be great of there would just be any fieldcode/field/cross reference or whatever and the full text only be shown when exit the Alt-F9 view by pressing F9.