Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / May 2008

Tip: Looking for answers? Try searching our database.

How can I use outline headings as the fields for a Mail Merge?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
hueffmea - 29 Apr 2008 20:22 GMT
I receive documents with outline formating, and I want to make labels out of
the headings.  How can I do that with a mail merge?
Doug Robbins - Word MVP - 29 Apr 2008 20:39 GMT
That cannot be done directly with mail merge.  It should however be possible
to use a macro to either create a data source that contained the headings or
transferred them directly to a label type template.

How many levels of headings are involved?  Do they have to appear on the
labels in the order in which they appear in the document, or, assuming that
there are multiple levels, could all of the Heading 1's appear on the labels
together, followed by all of the Heading 2's etc.?
Are the heading numbers required to appear on the labels as well, or just
the text of the heading?

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I receive documents with outline formating, and I want to make labels out
>of
> the headings.  How can I do that with a mail merge?
hueffmea - 30 Apr 2008 13:37 GMT
First off, thank you for taking the time to reply. Secondly, those are
excellent questions.

The very first heading is the name of the project. That must show up at the
top of each label.

The labels are no more than 5 headings deep.

The headings follow this format
(Heading 1) Name of Project
(Heading 2) 1 GENERAL CORRESPONDENCE
(Heading 3) 1.1 Correspondence from 2008
(Heading 4) 1.1.1 Correspondence from January 2008
(Heading 2) 2 PRE-COMMITMENT PROJECT REVIEW
(Heading 3) 2.1 Project Application
(Heading 4) 2.1.1 Formal Application

One of the issues I’m having is that we don’t know how many of each heading
there will be. For example.
(Heading 2) 1 GENERAL CORRESPONDENCE
(Heading 3) 1.1 Correspondence from 2006
(Heading 3) 1.2 Correspondence from 2007
(Heading 3) 1.3 Correspondence from 2008
(Heading 2) 2 PRE-COMMITMENT PROJECT REVIEW

In this case there is no Heading 4 and there are multiple Headings 3’s.

The labels must have this format:
Label #1
Name of Project
1 GENERAL CORRESPONDENCE (in bold)

Label #2
Name of Project
1 GENERAL CORRESPONDENCE (no longer in bold)
1.1 Correspondence from 2008 (in bold)

Label #3
Name of Project
1 GENERAL CORRESPONDENCE
1.2 Correspondence from 2007 (in bold)

Label #4
Name of Project
1.2 Correspondence from 2007 (no longer in bold)
1.2.1 Correspondence from Jackie (in bold)

Label #5
Name of Project
1 GENERAL CORRESPONDENCE
1.3 Correspondence from 2008 (in bold)

As you can see in all instances the bottom is bold.
Each label has the lowest level of Heading shown below its parent level.
I’ve been trying to figure this one out for two weeks.
Any continuing help would be greatly appreciated.

> That cannot be done directly with mail merge.  It should however be possible
> to use a macro to either create a data source that contained the headings or
[quoted text clipped - 10 lines]
> >of
> > the headings.  How can I do that with a mail merge?
Doug Robbins - Word MVP - 01 May 2008 00:30 GMT
Is there any other text in the documents that you receive?  Or, do they
contain just the Headings?

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> First off, thank you for taking the time to reply. Secondly, those are
> excellent questions.
[quoted text clipped - 74 lines]
>> >of
>> > the headings.  How can I do that with a mail merge?
Doug Robbins - Word MVP - 01 May 2008 09:31 GMT
It might be best if you can send me a document that is typical of the
"outline formatted" documents that you receive.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Is there any other text in the documents that you receive?  Or, do they
> contain just the Headings?
[quoted text clipped - 78 lines]
>>> >of
>>> > the headings.  How can I do that with a mail merge?
hueffmea - 02 May 2008 13:13 GMT
I sent you an email with two documents attached. The first was the list as I
get it, and the second was the labels I am creating. Please let me know if
you had any difficulty in receiving them.

> It might be best if you can send me a document that is typical of the
> "outline formatted" documents that you receive.
[quoted text clipped - 81 lines]
> >>> >of
> >>> > the headings.  How can I do that with a mail merge?
Doug Robbins - Word MVP - 02 May 2008 13:51 GMT
A macro containing the following code will create a document that you sent
to me that contains a table that could be used as a data source for a label
type mail merge.  With a bit more work, it could be made to create the
labels directly.  Note that there was one irregularity with the numbering in
the file that you sent me that I had to manually adjust to get this to work
correctly - that irregularity was a period after the first level 4

Dim Source As Document, Target As Document
Dim STable As Table, TTable As Table
Dim srange As Range, num As Range
Dim Project As String
Dim Line2 As String, Line3 As String, Line4 As String, Line5 As String,
Line6 As String
Dim Flag As String
Dim i As Long, j As Long, k As Long
Dim TRow As Row

Set Source = ActiveDocument
Selection.Find.Replacement.ClearFormatting
With Selection.Find
   .Text = "^13{1,}"
   .Replacement.Text = "^p"
   .Forward = True
   .Wrap = wdFindContinue
   .MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Set srange = Source.Range
srange.start = srange.start + InStr(srange, "Project")
Set srange = srange.Paragraphs(1).Range
srange.start = srange.start + InStr(srange, "[")
srange.End = srange.End - 2
Project = srange.Text
Source.ConvertNumbersToText
Set srange = Source.Range
srange.start = srange.Paragraphs(2).Range.start
srange.start = srange.start + InStr(srange, "MASTER FILE LIST")
srange.start = srange.Paragraphs(2).Range.start
For i = 1 To srange.Paragraphs.Count
   Set num = srange.Paragraphs(i).Range
   num.End = num.start + InStr(num, vbTab)
   j = 0
   For k = 1 To Len(num)
       If num.Characters(k) = "." Then
           j = j + 1
       End If
   Next k
   For k = 1 To j
       num.InsertBefore vbTab
   Next k
Next i
srange.ConvertToTable vbTab
Set STable = srange.Tables(1)
Set Target = Documents.Add
Set TTable = Target.Tables.Add(Target.Range, 1, 1)
With STable
   For i = 1 To .Rows.Count
       With .Rows(i)
           If Len(.Cells(1).Range) > 2 Then
               Set srange = .Cells(1).Range
               srange.End = srange.End - 1
               Line2 = srange.Text & vbTab
               Set srange = .Cells(2).Range
               srange.End = srange.End - 1
               Line2 = Line2 & srange.Text
               TTable.Cell(i, 1).Range.Text = Project & vbCr & Line2
               TTable.Rows.Add
           ElseIf Len(.Cells(2).Range) > 2 Then
               Set srange = .Cells(2).Range
               srange.End = srange.End - 1
               Line3 = srange.Text & vbTab
               Set srange = .Cells(3).Range
               srange.End = srange.End - 1
               Line3 = Line3 & srange.Text
               TTable.Cell(i, 1).Range.Text = Project & vbCr & Line2 & vbCr
& Line3
               TTable.Rows.Add
           ElseIf Len(.Cells(3).Range) > 2 Then
               Set srange = .Cells(3).Range
               srange.End = srange.End - 1
               Line4 = srange.Text & vbTab
               Set srange = .Cells(4).Range
               srange.End = srange.End - 1
               Line4 = Line4 & srange.Text
               TTable.Cell(i, 1).Range.Text = Project & vbCr & Line3 & vbCr
& Line4
               TTable.Rows.Add
           ElseIf Len(.Cells(4).Range) > 2 Then
               Set srange = .Cells(4).Range
               srange.End = srange.End - 1
               Line5 = srange.Text & vbTab
               Set srange = .Cells(5).Range
               srange.End = srange.End - 1
               Line5 = Line5 & srange.Text
               TTable.Cell(i, 1).Range.Text = Project & vbCr & Line4 & vbCr
& Line5
               TTable.Rows.Add
           ElseIf STable.Columns.Count > 5 Then
               Set srange = .Cells(5).Range
               srange.End = srange.End - 1
               Line6 = srange.Text & vbTab
               Set srange = .Cells(6).Range
               srange.End = srange.End - 1
               Line6 = Line6 & srange.Text
               TTable.Cell(i, 1).Range.Text = Project & vbCr & Line5 & vbCr
& Line6
               TTable.Rows.Add
           End If
       End With
   Next i
End With
With TTable
   For i = 1 To .Rows.Count
       With .Cell(i, 1).Range
           .Paragraphs(.Paragraphs.Count).Range.Font.Bold = True
       End With
   Next i
End With

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I sent you an email with two documents attached. The first was the list as
>I
[quoted text clipped - 92 lines]
>> >>> >of
>> >>> > the headings.  How can I do that with a mail merge?
hueffmea - 02 May 2008 15:42 GMT
I ran the script and I get an Invalid Outside Procedure error code at
Set Source = ActiveDocument

It looks to me as if it's not orphaned, but then to say I'm new to VB is an
understatement.

Can you tell me what I might be doing wrong?

> A macro containing the following code will create a document that you sent
> to me that contains a table that could be used as a data source for a label
[quoted text clipped - 211 lines]
> >> >>> >of
> >> >>> > the headings.  How can I do that with a mail merge?
hueffmea - 02 May 2008 18:20 GMT
Oddly enough, even though putting Sub at the top didn't seem to work, putting
public Sub did. I now have the list, and am working on figuring out how to
get it into the avery 5262 label template. Incidentally how much is your
consulting fee for something like this?

> I ran the script and I get an Invalid Outside Procedure error code at
> Set Source = ActiveDocument
[quoted text clipped - 219 lines]
> > >> >>> >of
> > >> >>> > the headings.  How can I do that with a mail merge?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.