Directory MailMerge with Category Grouped Lists on Labels

screech · #1 06-16-2014, 09:12 AM

Hello all,

First time here. I've searched around this forum for my answer and I have found a nice tutorial by Paul Edstein that describes how to create category-based lists. I have had success recreating the work in the tutorial and also applying it to my own list however I seem to be stuck with how to apply the categories to a document designed for labels.

I have an Excel table with a list of samples. The table includes a Box #, Project #, Sample #s, and a Date. I am hoping to use MailMerge as a tool to select which labels I would like to print from the directory, and then merge them on the Word Label Template and then print.

I have created a couple of test files for this and I am attaching them to this message. The XLS file is an example table of the data. I am also attaching the word document I using to Finish and Merge to a new document and create the labels.

The problem is that I cannot get the second sample information on the same box label. It always pushes it to the next label and it does not combine them. What I want it to look like is in the second Word attachment, called "Desired Result".

Is there a way to manipulate the form fields with this label template to group the samples by box on a single label as in the desired result? I feel that I have tried a combination of fields but still can't get the desired result. Thank you!

macropod · #2 06-16-2014, 05:37 PM

The directory/catalogue merge tools won't combine multiple records into a single table cell. If you want that, you'll need to re-organise the source data, so that each record (i.e. 'box') has all its data on the same row, with different project, sample & date headers for each item. Either that, or you'll need to use a macro-driven process in Excel to automate & populate the Word document, without recourse to mailmerge.

screech · #3 06-18-2014, 05:26 AM

Thank you very much for your answer, Paul. I'm disappointed that I cannot do this through MailMerge but thank you for offering an alternative with a macro that could run in the Excel file to push the information to a label template in Word. I'm very familiar with creating and running macros in Excel but I don't have a lot of experience with cross-program code. I will look around the internet later for some examples, but do you have any examples of code or good resources in mind that will get me started down the right road?

macropod · #4 06-18-2014, 11:41 PM

FWIW, an alternative approach would be to forget about using Word and doing the lot from within Excel. For example, the following macro creates all your labels on Sheet 2 of your existing workbook:

Code:

Sub Demo()
Dim lRow As Long, lCol As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim WkShtSrc As Worksheet, WkShtTgt As Worksheet
k = 0
Set WkShtSrc = ThisWorkbook.Worksheets("Sheet1")
Set WkShtTgt = ThisWorkbook.Worksheets("Sheet2")
With WkShtTgt
  .UsedRange.ClearContents
  .Columns("B:D").ColumnWidth = 43.3
  .Columns("A").ColumnWidth = 10
  .Columns("C").ColumnWidth = 10
  .Rows.RowHeight = 240
  With .Columns("A:D").Font
    .Name = "Arial"
    .Size = 12
  End With
End With
With WkShtSrc
  lRow = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
  lCol = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Column
  For i = 2 To lRow
    If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
      With WkShtTgt.Cells(m, l)
        .Value = .Text & Chr(10)
        For j = 2 To lCol
          .Value = .Text & Chr(10) & WkShtSrc.Cells(i, j)
        Next
      End With
    Else
      l = (k Mod 2 + 1) * 2: k = k + 1: m = -Int(-k / 2)
      With WkShtTgt.Cells(m, l)
        .Value = "BOX " & WkShtSrc.Cells(i, 1) & Chr(10)
        For j = 2 To lCol
          .Value = .Text & Chr(10) & WkShtSrc.Cells(i, j)
        Next
      End With
    End If
  Next
End With
End Sub

You will probably need to adjust the sheet margins and, perhaps, the values used for the columns A & C widths to get the exact alignment you require but, once that's done, there's no need to involve Word.

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
SQL MailMerge Labels	Mark J Rees	Mail Merge	2	05-10-2013 06:00 PM
Pictorial directory using mailmerge	JohnFinNC	Mail Merge	6	10-04-2012 06:36 PM
Directory Mailmerge	rohitjain80	Mail Merge	1	02-12-2012 03:42 AM
Excel Multi Level Category labels	Spay	Excel	0	03-14-2011 01:05 PM
MailMerge Labels Question	trims30	Mail Merge	0	08-04-2010 05:21 PM