Create Mailmerge Envelope with VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith - 27 Oct 2006 21:07 GMT

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?

Reply to this Message

Doug Robbins - Word MVP - 28 Oct 2006 08:29 GMT

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.

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 am baffled as to how to create a envelope mailmerge document
> automatically (without user interaction) using VBA. I have recorded how
[quoted text clipped - 36 lines]
>
> Any one have any experience with this problem?

Reply to this Message

Keith - 28 Oct 2006 17:15 GMT

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

> 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
[quoted text clipped - 48 lines]
> >
> > Any one have any experience with this problem?

Reply to this Message

Doug Robbins - Word MVP - 28 Oct 2006 19:44 GMT

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

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

> Yes,
>
[quoted text clipped - 57 lines]
>> >
>> > Any one have any experience with this problem?

Reply to this Message

Lüko Willms - 29 Oct 2006 07:30 GMT

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
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

Doug Robbins - Word MVP - 29 Oct 2006 11:31 GMT

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.

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

> Am Sat, 28 Oct 2006 18:44:18 UTC, schrieb "Doug Robbins - Word MVP"
> <dkr@REMOVECAPSmvps.org> auf microsoft.public.word.vba.general :
[quoted text clipped - 19 lines]
> Yours,
> L.W.

Reply to this Message

Keith - 02 Nov 2006 16:37 GMT

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.

Reply to this Message

Doug Robbins - Word MVP - 02 Nov 2006 19:51 GMT

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.

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

> Doug,
>
[quoted text clipped - 23 lines]
>> bashing
>> your head against the wall.

Reply to this Message

Peter Jamieson - 29 Oct 2006 20:23 GMT

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

> Am Sat, 28 Oct 2006 18:44:18 UTC, schrieb "Doug Robbins - Word MVP"
> <dkr@REMOVECAPSmvps.org> auf microsoft.public.word.vba.general :
[quoted text clipped - 19 lines]
> Yours,
> L.W.

Reply to this Message

Peter Jamieson - 30 Oct 2006 12:48 GMT

> 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

> 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
[quoted text clipped - 66 lines]
>> Yours,
>> L.W.

Reply to this Message