Mail Merge Template

Litmus · #1 01-09-2019, 05:53 AM

Hello
I have a set of student grades in a spreadsheet.

Not all students study the same subjects. I'd like to run a mail merge reports for individual students that removes the subjects (hence rows in a table) that aren't studied and keep those that are i.e. those with grades.

See attachment.

Apologies if there is already a thread - I found related ones but not a specific one.

macropod · #2 01-09-2019, 01:46 PM

Try adding the following macro to your mailmerge main document; when you click on Finish & Merge>Edit Individual Documents, the macro will automatically delete all table rows where the Grade is empty.

Code:

Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      For r = .Rows.Count To 3 Step -1
        With .Rows(r)
          If Len(.Cells(2).Range.Text) = 2 Then .Delete
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = False
End Sub

Litmus · #3 01-10-2019, 12:17 AM

Thank you for taking the time to do this Paul. It's very much appreciated.
I shall give this a go. A quick question - will it work with more columns of data re:
For r = .Rows.Count To 3 Step -1
if I had say six columns maybe - please excuse my VBA ignorance.
Kind regards
Julie

Litmus · #4 01-10-2019, 12:48 AM

Paul
This is the Mail Merge Template.The table that needs to change with the macro is Unit 1 Assessment. All of the others are static.

macropod · #5 01-10-2019, 01:28 PM

Quote:

Originally Posted by Litmus

A quick question - will it work with more columns of data re:
For r = .Rows.Count To 3 Step -1
if I had say six columns maybe - please excuse my VBA ignorance

The line:
For r = .Rows.Count To 3 Step -1
refers to rows, not columns. As coded, the macro tests column 2 (via the .Cells(2) reference). Provided that column is empty, the row will be deleted; the number of columns (provided there are at least 2) is of no consequence.

NoRest2Day · #6 08-23-2019, 02:53 PM

Paul, this code is magic. Just curious, can you think of any way to do the same thing for an email merge. Realize the macro as written works on the merged file as an individual document.

Appreciate any help you can offer as I have been looking for a solution to this challenge for quite a while!

macropod · #7 08-23-2019, 03:38 PM

Quote:

Originally Posted by NoRest2Day

Paul, this code is magic. Just curious, can you think of any way to do the same thing for an email merge.

No, because there's no post-merge 'document' to process.

Depending on what you're doing and how your data are configured, though, you might be able to
• use a DATABASE field. See, for example: Many to one email merge using tables - Microsoft Community ; or
• use IF tests with multiple versions of the table embedded within. See, for example: https://www.msofficeforums.com/128352-post6.html

NoRest2Day · #8 08-24-2019, 05:35 PM

Thanks for the quick reply and great ideas, Paul! Is it possible to parse the Individual Documents into an email merge by adding the email address --a two step process?

macropod · #9 08-24-2019, 10:06 PM

Mailmerge automation could be used to generate individual documents and send them as email attachments or as the email body. If that's what you're after, I suggest you start a new thread.

Novice2020 · #10 11-21-2020, 02:21 AM

Thanks so much for this. The macro works if there is only the table in question. I have more table above this where I won't need to apply this macro to tables above - is there a way I can only apply the macro to a specific table on word before mail merging?

macropod · #11 11-21-2020, 03:03 AM

No, that's not possible, because the tables concerned don't exist in a populated form until after the merge has completed. Instead, the code could be re-written to process every nth table. For example:

Code:

Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim t As Long, r As Long
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For t = 1 To .Tables.Count Step 2
    With .Tables(t)
      For r = .Rows.Count To 3 Step -1
        With .Rows(r)
          If Len(.Cells(2).Range.Text) = 2 Then .Delete
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = False
End Sub

where the '1' indicates the table to start at, and the '2' indicates that every second table from the table to start at is to be processed. Thus, as coded, the 1st, 3rd, 5th, etc. tables would be processed.

Richystab · #12 12-02-2020, 04:53 AM

Hi Please could someone help me re create this. I cannot get it to work?

macropod · #13 12-03-2020, 04:22 PM

In what way can't you get it to work?

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro

Richystab · #14 12-04-2020, 02:28 AM

Quote:

Originally Posted by macropod

In what way can't you get it to work?

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro

I managed in the end using this

Code:

Sub HideRowWhereSecondColumnBlank()
  Dim Pwd As String
  Dim oRow As Integer
  Dim oTable As Table
  Dim iTable As Table
  Dim pState As Boolean
  With ActiveDocument
    pState = False

    If .Tables.Count > 0 Then
      For Each iTable In .Tables
        If iTable.Tables.Count > 0 Then
          For Each oTable In iTable.Tables
            For oRow = oTable.Rows.Count To 1 Step -1
              If Len(Replace(oTable.Cell(oRow, 2).Range.Text, Chr(13) & Chr(7), vbNullString)) = 0 Then
                 On Error Resume Next 'skip vertically merged cells
                 oTable.Rows(oRow).Delete
              End If
            Next oRow
          Next oTable
        End If
      Next iTable
    End If
    If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd
      pState = False
    Pwd = ""
  End With
 
End Sub

macropod · #15 12-05-2020, 03:03 PM

That still doesn't tell me anything about your particular problem. In any event, the code you posted is overkill for a problem such as this - much of it is simply irrelevant. Did you actually add the macro in post #12 to your mailmerge main document?

I see you've now started a duplicate thread: https://www.msofficeforums.com/mail-...l-merging.html
Kindly don't do that. In any event the problem as described there is quite different: it's not only column 2 that might be empty.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to remove almost a full page of blank space in between Word table rows	garrisonsdad	Word Tables	4	10-18-2018 09:09 PM
Mail merge: remove empty rows when a field is not showed	Yarikh	Mail Merge	4	09-23-2018 12:17 AM
Remove blank table rows AFTER mailmerge	Formd	Mail Merge	5	05-11-2018 03:43 PM
How to remove blank spaces between rows in a table,	Jamal NUMAN	Word	2	04-28-2017 12:59 PM
Mail merge into table with two colums and several rows	kathriiin	Mail Merge	3	03-30-2015 07:06 AM