none
Mailmerge - want to substring mergefield and then apply case function RRS feed

  • Question

  • Any version of Word. It is installed at the client's sites and i have no control over it. There is an existing  mailmerge process in place. I am hoping to make a small change to fix the client's issue. One of the datafields in street-address. The mailmerge performs a \*Lower and then a \*Case switch which results in Po (there are other issues like, Sw, Se, etc. But for now I am focusing just on PO). Without changing the data source, what are my options? I can write a VB function? If there anything in the pure Mailmerge that can do this?
    Friday, June 8, 2012 3:44 PM

Answers

  • If your mergefield is for a PO Box, has 'PO' as the first word and you're happy to have 'PO BOX', you could encode the mailmerge main document with:

    {IF{MERGEFIELD Addr1} = "PO *" {MERGEFIELD Addr1 \* Upper} {MERGEFIELD Addr1 \*Lower \*Caps}}
    where 'Addr1' is the data field's name.

    Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

    Doing anything to the mailmerge output document via vba or whatever after the merge can be problematic, as you need to be careful about identifying the ranges to process in each letter. Changing something as simply as 'Po Box' to 'PO Box' would be pretty easy, though, as you typically wouldn't need to be careful about specifying ranges for that and it could be done using an ordinary Find/Replace (which you could include in a macro).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Saturday, June 9, 2012 4:02 AM
  • Other than the field-based approach and postprocess options suggested by macropod (and personally I would try very hard to stick with those), your other options depend to an extent on what you mean by "Any version of Word"

    If "Any version of Word" is limited to Windows versions of Word, version 2002 (XP) and later, then you can also consider using Word's Mailmerge events in VBA to inspect the street address and insert a modified version as each source record is processed. (e.g. you can set the value of a Document Variable to the street address that you want, and insert it using a { DOCVARIABLE } field instead of a { MERGEFIELD } field.)

    Those events are not available in Word 2000 or earlier, or any version of Mac Word.

    People sometimes report reliability issues when using MailMerge events, but if you can't use the field-based approach, it may be a better way to go that postprocessing, and is arguably the only way if you are merging directly to printer or to email.

    [Just for completeness, one other thing you can consider with some types of data source on Windows versions of Word is to use Word VBA & OpenDataSource to issue SQL that modifies the street address for you. It is arguably "changing the data source" of course. The main problems with that are

     a. there's a limit on the length of the SQL query you can issue

     b. writing the code necessary to format the data correctly is likely to be harder than writing a piece of procedural code in a VBA Event handler, particularly within the length limit.

     c. it's likely to be difficult to get that to work correctly for all versions of Word. 

    You could attempt that with data sources that Word accesses via Jet SQL (Access, Excel via OLE DB and ODBC, in some cases you can use Jet SQL with plain text data sources, some xBase sources and so on), or using a "server" dialect of SQL, e.g. Transact-SQL if your data source is SQL Server.

    ]


    Peter Jamieson

    Saturday, June 9, 2012 10:46 AM

All replies

  • If your mergefield is for a PO Box, has 'PO' as the first word and you're happy to have 'PO BOX', you could encode the mailmerge main document with:

    {IF{MERGEFIELD Addr1} = "PO *" {MERGEFIELD Addr1 \* Upper} {MERGEFIELD Addr1 \*Lower \*Caps}}
    where 'Addr1' is the data field's name.

    Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message.

    Doing anything to the mailmerge output document via vba or whatever after the merge can be problematic, as you need to be careful about identifying the ranges to process in each letter. Changing something as simply as 'Po Box' to 'PO Box' would be pretty easy, though, as you typically wouldn't need to be careful about specifying ranges for that and it could be done using an ordinary Find/Replace (which you could include in a macro).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Saturday, June 9, 2012 4:02 AM
  • Other than the field-based approach and postprocess options suggested by macropod (and personally I would try very hard to stick with those), your other options depend to an extent on what you mean by "Any version of Word"

    If "Any version of Word" is limited to Windows versions of Word, version 2002 (XP) and later, then you can also consider using Word's Mailmerge events in VBA to inspect the street address and insert a modified version as each source record is processed. (e.g. you can set the value of a Document Variable to the street address that you want, and insert it using a { DOCVARIABLE } field instead of a { MERGEFIELD } field.)

    Those events are not available in Word 2000 or earlier, or any version of Mac Word.

    People sometimes report reliability issues when using MailMerge events, but if you can't use the field-based approach, it may be a better way to go that postprocessing, and is arguably the only way if you are merging directly to printer or to email.

    [Just for completeness, one other thing you can consider with some types of data source on Windows versions of Word is to use Word VBA & OpenDataSource to issue SQL that modifies the street address for you. It is arguably "changing the data source" of course. The main problems with that are

     a. there's a limit on the length of the SQL query you can issue

     b. writing the code necessary to format the data correctly is likely to be harder than writing a piece of procedural code in a VBA Event handler, particularly within the length limit.

     c. it's likely to be difficult to get that to work correctly for all versions of Word. 

    You could attempt that with data sources that Word accesses via Jet SQL (Access, Excel via OLE DB and ODBC, in some cases you can use Jet SQL with plain text data sources, some xBase sources and so on), or using a "server" dialect of SQL, e.g. Transact-SQL if your data source is SQL Server.

    ]


    Peter Jamieson

    Saturday, June 9, 2012 10:46 AM