Create Mailmerge Envelope with VBA

I am baffled as to how to create a envelope mailmerge document
automatically (without user interaction) using VBA. I have recorded how
word does it and the recorded macro apparently takes a "behind the
scenes" step in the creation of an autotext entry that it subsequently
uses when creating the mergedocument. Each time I record the process,
Word creates a new autotext entry and then uses that entry as the
parameter for the .Envelope.Insert AutoText:= command:

Activedocument.Envelope.Insert ExtractAddress:=False,
OmitReturnAddress:= _
False, PrintBarCode:=False, PrintFIMA:=False,
Height:=InchesToPoints(4.13 _
), Width:=InchesToPoints(9.5), Address:="", AutoText:=
"ToolsCreateEnvelope2"

In this example, an unrecorded command created the autotext entry
ToolsCreateEnvelope2. When I examine the autotext entry
ToolsCreateEnvelope2, I find that it contains the mergefields that I
added via the set up envelope command when I recorded the above command
in Word.

Because I am calling this VBA routine from Access and want the system
to automatically create the merge envelope document without user
intervention, I have to somehow figure out how to do this
automatically.

As I see it my options are:

- Somehow programmatically create an autotext entries containing my
mergefields.
or
- Somehow enter a string for the Autotext entry that includes the
mergefields I want to show in the address box
or
- Programmatically select the address text box and then insert
mergefield using the standard ActiveDocument.Mergefield.Add command.

I don't have any idea how to do any of these.

Any one have any experience with this problem?

Doug
Sat Oct 28 02:29:20 CDT 2006

The thing to do is to create a template in Word that is already populated
with the mergefields and then have you Access code create a new document
from that template.

--
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

"Keith" <PosSampah@gmail.com> wrote in message
news:1161979649.980406.259240@m73g2000cwd.googlegroups.com...
>I am baffled as to how to create a envelope mailmerge document
> automatically (without user interaction) using VBA. I have recorded how
> word does it and the recorded macro apparently takes a "behind the
> scenes" step in the creation of an autotext entry that it subsequently
> uses when creating the mergedocument. Each time I record the process,
> Word creates a new autotext entry and then uses that entry as the
> parameter for the .Envelope.Insert AutoText:= command:
>
> Activedocument.Envelope.Insert ExtractAddress:=False,
> OmitReturnAddress:= _
> False, PrintBarCode:=False, PrintFIMA:=False,
> Height:=InchesToPoints(4.13 _
> ), Width:=InchesToPoints(9.5), Address:="", AutoText:=
> "ToolsCreateEnvelope2"
>
> In this example, an unrecorded command created the autotext entry
> ToolsCreateEnvelope2. When I examine the autotext entry
> ToolsCreateEnvelope2, I find that it contains the mergefields that I
> added via the set up envelope command when I recorded the above command
> in Word.
>
> Because I am calling this VBA routine from Access and want the system
> to automatically create the merge envelope document without user
> intervention, I have to somehow figure out how to do this
> automatically.
>
> As I see it my options are:
>
> - Somehow programmatically create an autotext entries containing my
> mergefields.
> or
> - Somehow enter a string for the Autotext entry that includes the
> mergefields I want to show in the address box
> or
> - Programmatically select the address text box and then insert
> mergefield using the standard ActiveDocument.Mergefield.Add command.
>
> I don't have any idea how to do any of these.
>
> Any one have any experience with this problem?
>

Keith
Sat Oct 28 11:15:48 CDT 2006

Yes,

I suppose that is the only option. My goal however is to completely
generate everything from within VBA. It is possible to build a merge
document for letters, but apparently not possible for labels and
envelopes

Keith


Doug Robbins - Word MVP wrote:
> The thing to do is to create a template in Word that is already populated
> with the mergefields and then have you Access code create a new document
> from that template.
>
> --
> 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
>
> "Keith" <PosSampah@gmail.com> wrote in message
> news:1161979649.980406.259240@m73g2000cwd.googlegroups.com...
> >I am baffled as to how to create a envelope mailmerge document
> > automatically (without user interaction) using VBA. I have recorded how
> > word does it and the recorded macro apparently takes a "behind the
> > scenes" step in the creation of an autotext entry that it subsequently
> > uses when creating the mergedocument. Each time I record the process,
> > Word creates a new autotext entry and then uses that entry as the
> > parameter for the .Envelope.Insert AutoText:= command:
> >
> > Activedocument.Envelope.Insert ExtractAddress:=False,
> > OmitReturnAddress:= _
> > False, PrintBarCode:=False, PrintFIMA:=False,
> > Height:=InchesToPoints(4.13 _
> > ), Width:=InchesToPoints(9.5), Address:="", AutoText:=
> > "ToolsCreateEnvelope2"
> >
> > In this example, an unrecorded command created the autotext entry
> > ToolsCreateEnvelope2. When I examine the autotext entry
> > ToolsCreateEnvelope2, I find that it contains the mergefields that I
> > added via the set up envelope command when I recorded the above command
> > in Word.
> >
> > Because I am calling this VBA routine from Access and want the system
> > to automatically create the merge envelope document without user
> > intervention, I have to somehow figure out how to do this
> > automatically.
> >
> > As I see it my options are:
> >
> > - Somehow programmatically create an autotext entries containing my
> > mergefields.
> > or
> > - Somehow enter a string for the Autotext entry that includes the
> > mergefields I want to show in the address box
> > or
> > - Programmatically select the address text box and then insert
> > mergefield using the standard ActiveDocument.Mergefield.Add command.
> >
> > I don't have any idea how to do any of these.
> >
> > Any one have any experience with this problem?
> >

Doug
Sat Oct 28 13:44:18 CDT 2006

It is also possible to create a merge document that can be used to print
envelopes using VBA. There are lots of other things that it is possible to
do with VBA, but using VBA does not always make the most sense. Likewise,
using the so-called macro recorder to create code does not always give you
the best result or even a useable result.

It can however be done in the following way:

Dim mmdoc As Document
Dim myrange As Range
Set mmdoc = Documents.Add
With mmdoc.PageSetup
.PaperSize = wdPaperEnvelope10
.Orientation = wdOrientLandscape
.LeftMargin = InchesToPoints(4)
.TopMargin = InchesToPoints(2)
.BottomMargin = InchesToPoints(0.5)
End With
Set myrange = mmdoc.Range
myrange.Paragraphs(1).SpaceAfter = 0
With mmdoc.MailMerge
.MainDocumentType = wdEnvelopes
.OpenDataSource "C:\documents and settings\Doug Robbins\My Documents\My
Data Sources\EVMS Labels Page 1.doc"
.Fields.Add myrange, "First"
myrange.End = mmdoc.Range.End
myrange.InsertAfter " "
myrange.End = mmdoc.Range.End
myrange.Collapse wdCollapseEnd
.Fields.Add myrange, "Middle"
myrange.End = mmdoc.Range.End
myrange.InsertAfter " "
myrange.End = mmdoc.Range.End
myrange.Collapse wdCollapseEnd
.Fields.Add myrange, "Last"
myrange.End = mmdoc.Range.End
myrange.InsertAfter vbCr
myrange.End = mmdoc.Range.End
myrange.Collapse wdCollapseEnd
.Fields.Add myrange, "Street"
'etc
End With


--
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

"Keith" <PosSampah@gmail.com> wrote in message
news:1162052148.843195.58240@m7g2000cwm.googlegroups.com...
> Yes,
>
> I suppose that is the only option. My goal however is to completely
> generate everything from within VBA. It is possible to build a merge
> document for letters, but apparently not possible for labels and
> envelopes
>
> Keith
>
>
> Doug Robbins - Word MVP wrote:
>> The thing to do is to create a template in Word that is already populated
>> with the mergefields and then have you Access code create a new document
>> from that template.
>>
>> --
>> 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
>>
>> "Keith" <PosSampah@gmail.com> wrote in message
>> news:1161979649.980406.259240@m73g2000cwd.googlegroups.com...
>> >I am baffled as to how to create a envelope mailmerge document
>> > automatically (without user interaction) using VBA. I have recorded how
>> > word does it and the recorded macro apparently takes a "behind the
>> > scenes" step in the creation of an autotext entry that it subsequently
>> > uses when creating the mergedocument. Each time I record the process,
>> > Word creates a new autotext entry and then uses that entry as the
>> > parameter for the .Envelope.Insert AutoText:= command:
>> >
>> > Activedocument.Envelope.Insert ExtractAddress:=False,
>> > OmitReturnAddress:= _
>> > False, PrintBarCode:=False, PrintFIMA:=False,
>> > Height:=InchesToPoints(4.13 _
>> > ), Width:=InchesToPoints(9.5), Address:="", AutoText:=
>> > "ToolsCreateEnvelope2"
>> >
>> > In this example, an unrecorded command created the autotext entry
>> > ToolsCreateEnvelope2. When I examine the autotext entry
>> > ToolsCreateEnvelope2, I find that it contains the mergefields that I
>> > added via the set up envelope command when I recorded the above command
>> > in Word.
>> >
>> > Because I am calling this VBA routine from Access and want the system
>> > to automatically create the merge envelope document without user
>> > intervention, I have to somehow figure out how to do this
>> > automatically.
>> >
>> > As I see it my options are:
>> >
>> > - Somehow programmatically create an autotext entries containing my
>> > mergefields.
>> > or
>> > - Somehow enter a string for the Autotext entry that includes the
>> > mergefields I want to show in the address box
>> > or
>> > - Programmatically select the address text box and then insert
>> > mergefield using the standard ActiveDocument.Mergefield.Add command.
>> >
>> > I don't have any idea how to do any of these.
>> >
>> > Any one have any experience with this problem?
>> >
>

Willms
Sun Oct 29 01:30:47 CDT 2006

Am Sat, 28 Oct 2006 18:44:18 UTC, schrieb "Doug Robbins - Word MVP"
<dkr@REMOVECAPSmvps.org> auf microsoft.public.word.vba.general :

> .Fields.Add myrange, "First"
> myrange.End = mmdoc.Range.End
> myrange.InsertAfter " "
> myrange.End = mmdoc.Range.End
> myrange.Collapse wdCollapseEnd
> .Fields.Add myrange, "Middle"
> myrange.End = mmdoc.Range.End
> myrange.InsertAfter " "

While fields can easily added this way, how about nested fields? I
would want to add a space after a first name only when there is a
first name, same for "middle", like this:

{IF {MERGEFIELD Middle} <> "" "{MERGEFIELD Middle} " ""}

How could I use .Fields.Add to create such nested fields? The above
one is still quite simple, the nesting could go deeper.


Yours,
L.W.

Doug
Sun Oct 29 04:31:59 CST 2006

I would go back to my original suggestion and create a template or a
document that is already set up with a link to the data source and the
mergefields in the configuration that you require them and then simply use

FileNew "Templatepathname"

or

FileOpen "Filepathname"

IMHO, using vba to try and create mailmerge main documents is like bashing
your head against the wall.

--
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

"Lüko Willms" <l.willms@domain.invalid> wrote in message
news:czd2LKcn8EGd-pn2-CKmkslzkhp8N@lueko.willms.dialin.t-online.de...
> Am Sat, 28 Oct 2006 18:44:18 UTC, schrieb "Doug Robbins - Word MVP"
> <dkr@REMOVECAPSmvps.org> auf microsoft.public.word.vba.general :
>
>> .Fields.Add myrange, "First"
>> myrange.End = mmdoc.Range.End
>> myrange.InsertAfter " "
>> myrange.End = mmdoc.Range.End
>> myrange.Collapse wdCollapseEnd
>> .Fields.Add myrange, "Middle"
>> myrange.End = mmdoc.Range.End
>> myrange.InsertAfter " "
>
> While fields can easily added this way, how about nested fields? I
> would want to add a space after a first name only when there is a
> first name, same for "middle", like this:
>
> {IF {MERGEFIELD Middle} <> "" "{MERGEFIELD Middle} " ""}
>
> How could I use .Fields.Add to create such nested fields? The above
> one is still quite simple, the nesting could go deeper.
>
>
> Yours,
> L.W.
>
>
>

Peter
Sun Oct 29 13:23:54 CST 2006

FWIW, i agree with Doug. Although it's possible to create nested fields
"on-the-fly", the starting conditions have to be exactly right (you have to
be in such-and-such a view in Word, you have to have field code display
switched on, hidden text displayed, and so on - off the top of my head I
can't tell you exactly what you need: all I know is that it's not
particularly easy to be sure that you get it right.

Further, because "field code brace characters" are special, you cannot just
specify a string of field code brace characters and ordinary text characters
in a VBA string variable and use a method like InsertAfter to insert a set
of nested fields. You can write a little interpreter to do it that makes it
a bit easier, but at that point you are already moving away from the idea
that you are "just" using VBA to create your Envelope: you're using VBA code
and a separate, non-VBA specification of the fields, text and layout you
want to create. For example, you can write VBA to take a string such as

{ IF ""{ MERGEFIELD abc }"" = """" ""A"" ""{ MERGEFIELD abc"" }

and inser tit as a set of nested fields. To generalise it, of course you
need to be able to distinguish between "field code braces" and ordinary "{"
and "}" characters in your representation of the fields you want to insert.
I have some code to do that stuff but I'd have to dig it out.

However, once you've started down that path, you could for example do the
following:
a. create the envelope layout you need
b. save it as RTF (or WordProcessingML format
c. encode the entire RTF or XML as string data within your VBA code
d. have your VBA
- read the strings
- write them out to a .rtf or .xml file
- open the resulting document
- create an envelope (or an AUTOTEXT, and from that, and envelope) from it

It all depends on why you want to stick to "VBA only". If it's because you
want to start without a separate file in a file system somewhere, the above
approach avoids that. If it's because you want the user to be able to
specify the envelope layout, and your code has therefore to construct a
layout "on-the-fly", that's different: it's probably quite hard to customize
rtf code or WordProcessingML code in that way.

Just my 2c-worth...

Peter Jamieson


"Lüko Willms" <l.willms@domain.invalid> wrote in message
news:czd2LKcn8EGd-pn2-CKmkslzkhp8N@lueko.willms.dialin.t-online.de...
> Am Sat, 28 Oct 2006 18:44:18 UTC, schrieb "Doug Robbins - Word MVP"
> <dkr@REMOVECAPSmvps.org> auf microsoft.public.word.vba.general :
>
>> .Fields.Add myrange, "First"
>> myrange.End = mmdoc.Range.End
>> myrange.InsertAfter " "
>> myrange.End = mmdoc.Range.End
>> myrange.Collapse wdCollapseEnd
>> .Fields.Add myrange, "Middle"
>> myrange.End = mmdoc.Range.End
>> myrange.InsertAfter " "
>
> While fields can easily added this way, how about nested fields? I
> would want to add a space after a first name only when there is a
> first name, same for "middle", like this:
>
> {IF {MERGEFIELD Middle} <> "" "{MERGEFIELD Middle} " ""}
>
> How could I use .Fields.Add to create such nested fields? The above
> one is still quite simple, the nesting could go deeper.
>
>
> Yours,
> L.W.
>
>
>

Peter
Mon Oct 30 05:48:21 CST 2006

> I have some code to do that stuff but I'd have to dig it out.

I don't have this to hand, but FWIW if you happen to be near a
German-language bookshop you can find this code on p. 331 of
Word-Programmierung: Das Handbuch - Entwicklung und Automatisierung mit VBA,
XML und VSTO (Meister, Gahler, Jamieson, Fressdorf), e.g. see

http://www.amazon.de/exec/obidos/ASIN/3860639897/qid=1147606171/sr=8-1/ref=sr_8_xs_ap_i1_xgl/028-1816925-4882945

Peter Jamieson


"Peter Jamieson" <pjj@KillmapSpjjnet.demon.co.uk> wrote in message
news:eLqkE$4%23GHA.3344@TK2MSFTNGP03.phx.gbl...
> FWIW, i agree with Doug. Although it's possible to create nested fields
> "on-the-fly", the starting conditions have to be exactly right (you have
> to be in such-and-such a view in Word, you have to have field code display
> switched on, hidden text displayed, and so on - off the top of my head I
> can't tell you exactly what you need: all I know is that it's not
> particularly easy to be sure that you get it right.
>
> Further, because "field code brace characters" are special, you cannot
> just specify a string of field code brace characters and ordinary text
> characters in a VBA string variable and use a method like InsertAfter to
> insert a set of nested fields. You can write a little interpreter to do it
> that makes it a bit easier, but at that point you are already moving away
> from the idea that you are "just" using VBA to create your Envelope:
> you're using VBA code and a separate, non-VBA specification of the fields,
> text and layout you want to create. For example, you can write VBA to take
> a string such as
>
> { IF ""{ MERGEFIELD abc }"" = """" ""A"" ""{ MERGEFIELD abc"" }
>
> and inser tit as a set of nested fields. To generalise it, of course you
> need to be able to distinguish between "field code braces" and ordinary
> "{" and "}" characters in your representation of the fields you want to
> insert. I have some code to do that stuff but I'd have to dig it out.
>
> However, once you've started down that path, you could for example do the
> following:
> a. create the envelope layout you need
> b. save it as RTF (or WordProcessingML format
> c. encode the entire RTF or XML as string data within your VBA code
> d. have your VBA
> - read the strings
> - write them out to a .rtf or .xml file
> - open the resulting document
> - create an envelope (or an AUTOTEXT, and from that, and envelope) from
> it
>
> It all depends on why you want to stick to "VBA only". If it's because you
> want to start without a separate file in a file system somewhere, the
> above approach avoids that. If it's because you want the user to be able
> to specify the envelope layout, and your code has therefore to construct a
> layout "on-the-fly", that's different: it's probably quite hard to
> customize rtf code or WordProcessingML code in that way.
>
> Just my 2c-worth...
>
> Peter Jamieson
>
>
> "Lüko Willms" <l.willms@domain.invalid> wrote in message
> news:czd2LKcn8EGd-pn2-CKmkslzkhp8N@lueko.willms.dialin.t-online.de...
>> Am Sat, 28 Oct 2006 18:44:18 UTC, schrieb "Doug Robbins - Word MVP"
>> <dkr@REMOVECAPSmvps.org> auf microsoft.public.word.vba.general :
>>
>>> .Fields.Add myrange, "First"
>>> myrange.End = mmdoc.Range.End
>>> myrange.InsertAfter " "
>>> myrange.End = mmdoc.Range.End
>>> myrange.Collapse wdCollapseEnd
>>> .Fields.Add myrange, "Middle"
>>> myrange.End = mmdoc.Range.End
>>> myrange.InsertAfter " "
>>
>> While fields can easily added this way, how about nested fields? I
>> would want to add a space after a first name only when there is a
>> first name, same for "middle", like this:
>>
>> {IF {MERGEFIELD Middle} <> "" "{MERGEFIELD Middle} " ""}
>>
>> How could I use .Fields.Add to create such nested fields? The above
>> one is still quite simple, the nesting could go deeper.
>>
>>
>> Yours,
>> L.W.
>>
>>
>>
>
>

Keith
Thu Nov 02 09:37:19 CST 2006

Doug,

You are no doubt correct about using vba to create mailmerge mail
documents.

The application I am developing is a specialized donor tracking program
that is used by 300-400 users of various levels of computer expertise.
Most of them find creating a mailmerge doc challenging.

So my goal is/was to allow them to select addresses within the access
program, hit a button and get letters each month. The VBA prog will
generate the skeleton of a mail merge letter. They edit the skeleton
doc in word and save it. Subsequently, the access program can
automatically generate merge letters for them each month with updated
data. I am able to do that with no problem. It is the process of
creating merge envelopes or labels that is very difficult.

I appreciate everyone's input. I will try it and see where it leads.

Keith

On Oct 29, 4:31 am, "Doug Robbins - Word MVP" <d...@REMOVECAPSmvps.org>
wrote:

>
> IMHO, using vba to try and create mailmerge main documents is like bashing
> your head against the wall.

Doug
Thu Nov 02 12:51:21 CST 2006

I know that I am repeating myself, but you should just set up a template
envelope that is linked to the datasource and already has the mergefields in
it.


--
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

"Keith" <PosSampah@gmail.com> wrote in message
news:1162481839.176894.298890@f16g2000cwb.googlegroups.com...
> Doug,
>
> You are no doubt correct about using vba to create mailmerge mail
> documents.
>
> The application I am developing is a specialized donor tracking program
> that is used by 300-400 users of various levels of computer expertise.
> Most of them find creating a mailmerge doc challenging.
>
> So my goal is/was to allow them to select addresses within the access
> program, hit a button and get letters each month. The VBA prog will
> generate the skeleton of a mail merge letter. They edit the skeleton
> doc in word and save it. Subsequently, the access program can
> automatically generate merge letters for them each month with updated
> data. I am able to do that with no problem. It is the process of
> creating merge envelopes or labels that is very difficult.
>
> I appreciate everyone's input. I will try it and see where it leads.
>
> Keith
>
> On Oct 29, 4:31 am, "Doug Robbins - Word MVP" <d...@REMOVECAPSmvps.org>
> wrote:
>
>>
>> IMHO, using vba to try and create mailmerge main documents is like
>> bashing
>> your head against the wall.
>