none
Dynamically display textbox or text in letters RRS feed

  • Question

  • I am using Word 2007. I have an Excel worksheet with field called "Branch". Using this worksheet to perform mail merge, the letter in MS Word will display a textbox with certain text if the branch="ABC", else it will not display the textbox.

    I would like to ask how I could do it with MS Word Macro? If this is too complex, I could use simple text instead of textbox, but is it still feasible?

    Thank you.

    Thursday, February 11, 2016 2:37 AM

Answers

  • You asked how to conditionally add a textbox as part of a mailmerge, which is what I provided advice for. This has nothing to do with VBA; it relies solely on field coding.

    Your reply indicates that you are instead trying to add the textbox via VBA, which is quite unnecessary.

    The reason your VBA code is not working is that you're adding the textbox to the mailmerge main document and not to the output. To conditionally add the textbox to the output using VBA, you'd need code like:

    Sub PrintMailMerge()
    Application.ScreenUpdating = False
    Dim MainDoc As Document, i As Long, bTxtBox As Boolean, Box As Shape
    Set MainDoc = ActiveDocument
    With MainDoc
      For i = 1 To .MailMerge.DataSource.RecordCount
        bTxtBox = False
        With .MailMerge
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            If Trim(.DataFields("Branch")) = "ABC" Then bTxtBox = True
          End With
          .Execute Pause:=False
        End With
        With ActiveDocument
          If bTxtBox = True Then
            Set Box = .Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
              Left:=50, Top:=50, Width:=100, Height:=100)
            Box.TextFrame.TextRange.Text = "Textbox text"
          End If
          .PrintOut
          .Close SaveChanges:=False
        End With
      Next i
    End With
    Application.ScreenUpdating = True
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by George Tang Monday, February 15, 2016 3:07 AM
    Saturday, February 13, 2016 10:26 PM
  • For question 2, I may want to set the text inside the box as arial, bold, and some text in italics. Sorry I dont get the meaning of "configure the mailmerge main document so that it has the fully-formatted textbox/table in place so all the macro need do is delete it when not required." . Do you have an example for this one?

    It means you should insert your textbox into the mailmerge main document, with the required content and formatting. I don't have an example of this, and you shouldn't really need one. After all, it's your textbox and all the macro would be doing is deleting it when not required.

    That said, you really should be doing the conditional inclusion of the textbox via field coding, as per my first reply. It's simple and effective and means your entire merge can be run without recourse to a macro. For a demonstration, see: https://www.dropbox.com/s/xurixnjvi6y0o7e/Demo.docx?dl=0
    Simply attach the document to your mailmerge data source and execute the merge. You can reposition the textbox in the mailmerge main document, so that it gets output where you require it. Do note that you might need to do a bit of experimentation, as the horizontal positioning is relative to 'character' and the vertical postioning is relative to 'paragraph' (you could change this one to 'page', or 'margin' though).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by George Tang Monday, February 15, 2016 3:07 AM
    Sunday, February 14, 2016 11:09 PM
  • I will include the textbox in the letter template, and use field coding to remove it once the condition is not matched, right?

    Sorry that it seems the demonstration file demo.docx is a blank file, I can't see anything there.

    Evidently, then, you didn't attach it to your data source and execute the merge, which is what I said you should do. Had you done so, you would have obtained an output with textboxes only for the records where Branch = ABC.

    The field code can be seen by pressing Alt-F9, which toggles Word's field code display on/off.


    Cheers
    Paul Edstein
    [MS MVP - Word]



    • Marked as answer by George Tang Monday, February 15, 2016 3:07 AM
    • Edited by macropodMVP Monday, February 15, 2016 3:39 AM typo
    Monday, February 15, 2016 1:00 AM

All replies

  • You could use a field coded as:
    {IF{MERGEFIELD Branch}= "ABC" "Conditional content here"}
    or:
    {IF«Branch»= "ABC" "Conditional content here"}
    where your textbox is anchored where the above has 'Conditional content here'.

    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); 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, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the 'Insert Merge Field' dropdown. The spaces represented in the field construction are all required.


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Proposed as answer by David_JunFeng Friday, February 12, 2016 6:12 AM
    • Unproposed as answer by George Tang Saturday, February 13, 2016 5:20 PM
    • Edited by macropodMVP Saturday, February 13, 2016 10:26 PM typo
    Thursday, February 11, 2016 7:39 AM
  • I tried to do something like this. but it seems that whenever there is record hitting the condition, all the letter will get the textbox. I am wondering because the textbox is added into the word template, causing it to show it on every page. But i just want to add it in the specific letter that hitting the condition, can anybody advice me how to do it, please?
    Sub PrintMailMerge()
        Dim CA As String
        Dim totalMailings As Long
            
        With ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource _
            Name:="C:\Users\George\Documents\testmm.xlsx", _
            ConfirmConversions:=False, _
            ReadOnly:=True, _
            LinkToSource:=True, _
            AddToRecentFiles:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
            "Data Source=C:\Users\George\Documents\testmm.xlsx;Mode=Read;" & _
            "Extended Properties=""HDR=YES;IMEX=1;"";", _
            SQLStatement:="SELECT * FROM `'PTEST$'`", _
            SubType:=wdMergeSubTypeAccess
         End With
            
         totalMailings = ActiveDocument.MailMerge.DataSource.RecordCount
         '
               
        For i = 1 To totalMailings Step 1
        ActiveDocument.MailMerge.DataSource.ActiveRecord = i
            CA = ActiveDocument.MailMerge.DataSource.DataFields("testNo").Value
            If InStr(1, CA, "012") > 0 Then
             Call addBox
            End If
        Next i
             
        With ActiveDocument.MailMerge
            .Destination = wdSendToPrinter
            .Execute
        End With
           
    End Sub

    Sub addBox()
        Dim Box As Shape
        Set Box = ActiveDocument.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, Left:=50, Top:=50, Width:=100, Height:=100)
        Box.TextFrame.TextRange.Text = "Test"
    End Sub
    Thank you.



    • Edited by George Tang Saturday, February 13, 2016 6:14 PM
    Saturday, February 13, 2016 12:22 PM
  • You asked how to conditionally add a textbox as part of a mailmerge, which is what I provided advice for. This has nothing to do with VBA; it relies solely on field coding.

    Your reply indicates that you are instead trying to add the textbox via VBA, which is quite unnecessary.

    The reason your VBA code is not working is that you're adding the textbox to the mailmerge main document and not to the output. To conditionally add the textbox to the output using VBA, you'd need code like:

    Sub PrintMailMerge()
    Application.ScreenUpdating = False
    Dim MainDoc As Document, i As Long, bTxtBox As Boolean, Box As Shape
    Set MainDoc = ActiveDocument
    With MainDoc
      For i = 1 To .MailMerge.DataSource.RecordCount
        bTxtBox = False
        With .MailMerge
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            If Trim(.DataFields("Branch")) = "ABC" Then bTxtBox = True
          End With
          .Execute Pause:=False
        End With
        With ActiveDocument
          If bTxtBox = True Then
            Set Box = .Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
              Left:=50, Top:=50, Width:=100, Height:=100)
            Box.TextFrame.TextRange.Text = "Textbox text"
          End If
          .PrintOut
          .Close SaveChanges:=False
        End With
      Next i
    End With
    Application.ScreenUpdating = True
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by George Tang Monday, February 15, 2016 3:07 AM
    Saturday, February 13, 2016 10:26 PM
  • Thank you.

    I have thought of doing this letter by letter.  But I don't want to print it one by one as the no of records could be huge. Is it possible it to get all the letters in a word file and print it all at once?

    Thank you.


    • Edited by George Tang Sunday, February 14, 2016 2:15 AM
    Sunday, February 14, 2016 2:09 AM
  • I don't want to print it one by one as the no of records could be huge. Is it possible it to get all the letters in a word file and print it all at once?


    Printing all at once is effectively what the code I posted does - even though the records are printed as separate print jobs. In the final analysis, it makes little difference to the printer whether each record is printed as a separate job or they're all printed as a single job.

    Sure, you could add code to aggregate all the output into a single document before printing, but that just increases your overheads. The only reason I can see for wanting to do that would be if you needed to send the output file off-site for printing, but that's not what you're doing - your own code sends the output direct to the printer.

    Cross-posted at: http://www.excelforum.com/word-formatting-and-general/1126796-adding-textbox-in-specific-letter-in-mail-merge.html
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Sunday, February 14, 2016 6:27 AM Cross-post
    Sunday, February 14, 2016 2:40 AM
  • yes, actually i need to print a pdf to offsite for printing. that's why i need a file containing all the letters.

    Is that i shall append all the contents of ActiveDocument in the loop to a specific file? Or is there any other simpler method?

    Thank you.

    Sunday, February 14, 2016 8:50 AM
  • It's a mystery, then, why you coded your macro to send the output direct to printer, rather than to a new document, since anything you send direct to printer would be for on-site printing only.

    Before proceeding, though:
    1. Do you intend having any mergefields in the textbox? If so, you'll have to take a different approach, as mergefields won't work in a textbox. You'd need to use a table instead.
    2. Do you intend having any formatting other than plain text in the textbox/table, using whatever Word's Normal Style defaults to? If so, you'd do better to use either the field code solution I posted, with all the formatting in place, or configure the mailmerge main document so that it has the fully-formatted textbox/table in place so all the macro need do is delete it when not required. That's far easier for you to work with code-wise and is easier for anyone to maintain.
    3. Does your mailmerge main document contain any headers/footers?


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, February 14, 2016 10:10 AM
  • because i want to use the print function to print the pdf for testing purpose. My testing pc is not connected to any printer. Sorry that I am not very familiar with macro, so i may act very rookie to code this function.

    For your question 1 and 3, i dont have mergefields in textbox and header/footers.

    For question 2, I may want to set the text inside the box as arial, bold, and some text in italics. Sorry I dont get the meaning of "configure the mailmerge main document so that it has the fully-formatted textbox/table in place so all the macro need do is delete it when not required." . Do you have an example for this one?

    Really thanks for your help as I can't quite find similar resources in interenet.

    Sunday, February 14, 2016 12:52 PM
  • For question 2, I may want to set the text inside the box as arial, bold, and some text in italics. Sorry I dont get the meaning of "configure the mailmerge main document so that it has the fully-formatted textbox/table in place so all the macro need do is delete it when not required." . Do you have an example for this one?

    It means you should insert your textbox into the mailmerge main document, with the required content and formatting. I don't have an example of this, and you shouldn't really need one. After all, it's your textbox and all the macro would be doing is deleting it when not required.

    That said, you really should be doing the conditional inclusion of the textbox via field coding, as per my first reply. It's simple and effective and means your entire merge can be run without recourse to a macro. For a demonstration, see: https://www.dropbox.com/s/xurixnjvi6y0o7e/Demo.docx?dl=0
    Simply attach the document to your mailmerge data source and execute the merge. You can reposition the textbox in the mailmerge main document, so that it gets output where you require it. Do note that you might need to do a bit of experimentation, as the horizontal positioning is relative to 'character' and the vertical postioning is relative to 'paragraph' (you could change this one to 'page', or 'margin' though).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by George Tang Monday, February 15, 2016 3:07 AM
    Sunday, February 14, 2016 11:09 PM
  • Thanks.

    I will include the textbox in the letter template, and use field coding to remove it once the condition is not matched, right?

    Sorry that it seems the demonstration file demo.docx is a blank file, I can't see anything there.

    Thanks again for your great help.

    Monday, February 15, 2016 12:54 AM
  • I will include the textbox in the letter template, and use field coding to remove it once the condition is not matched, right?

    Sorry that it seems the demonstration file demo.docx is a blank file, I can't see anything there.

    Evidently, then, you didn't attach it to your data source and execute the merge, which is what I said you should do. Had you done so, you would have obtained an output with textboxes only for the records where Branch = ABC.

    The field code can be seen by pressing Alt-F9, which toggles Word's field code display on/off.


    Cheers
    Paul Edstein
    [MS MVP - Word]



    • Marked as answer by George Tang Monday, February 15, 2016 3:07 AM
    • Edited by macropodMVP Monday, February 15, 2016 3:39 AM typo
    Monday, February 15, 2016 1:00 AM
  • Great, it works!

    Thank you so much!

    Monday, February 15, 2016 3:08 AM