VBA and Mail Merge

[GreenEnvy]

Hi,
I have a problem I am looking to solve, I'll explain what we're doing first.

Currently we use Word and Access to do mail merges to send people receipts for donations they have made. We currenly have a Word document that is basically a whole bunch of Nested IF statements that determine which letter will be printed out.
For example, if they donated to code "123" they get letter "a", if they donated to code "234" they get letter "b".
This is done using the inserttext word field, linking to the word doc to insert. The point of this is so we don't have to rerun the merge several times, one for each code.
This works perfectly fine right now, however we are almost at the 20 nested IF statement limit (word 2003).

I am wondering if I can do this IF logic in vba instead. So I have two questions.

1)Can I access the merge fields in a document from vba? something like: If (Mergefield.AppealCode = "12345") then .....

2)Can I insert a document using vba when the document merges? This would need to process for each merge record because the code can change from one record to another.

The Word statement currently looks like this:
{IF "AppealCode" = 12345 {INSERTTEXT="c:\letter1.doc"} {INSERTTEXT="Letter2.doc"}}

[TonyJollans]

Yes, you can do it in VBA, but do you need to?

You don't show how you are nesting but you could just have a lot of IFs one after the other:

{IF "AppealCode" = 12345 {INSERTTEXT="c:\letter1.doc"} }
{IF "AppealCode" = 67890 {INSERTTEXT="c:\letter2.doc"} }
{IF "AppealCode" = 97531 {INSERTTEXT="c:\letter3.doc"} }

[GreenEnvy]

Yes, you can do it in VBA, but do you need to?

You don't show how you are nesting but you could just have a lot of IFs one after the other:

{IF "AppealCode" = 12345 {INSERTTEXT="c:\letter1.doc"} }
{IF "AppealCode" = 67890 {INSERTTEXT="c:\letter2.doc"} }
{IF "AppealCode" = 97531 {INSERTTEXT="c:\letter3.doc"} }

Thanks for the quick reply,

The problem with having several standalone IF statements is that there are several dozen of these codes. Currently only about 17 of them require a special letter, the rest use a default letter. So if we did standalone IF's, I'd need to write an IF for each possible code, which would make the file HUGE (includetext seems to embed the letter, not just link to it, we're already over a 5meg file now).

Using nested IF's (basically else if's), I can have a catch all at the end, so any other code use the default letter.

Right now it's basicly like this for nesting:

{IF "AppealCode" = 12345 {INSERTTEXT="c:\letter1.doc"} {IF "AppealCode" = 23456 {INSERTTEXT="c:\letter1.doc"} {INSERTTEXT="c:\default.doc"}}}
So if this, do this, else if this, do this, else this.

If you can think of a way to still have that catch all without using nesting, I'm happy to use it :)

What are the arguments or variables for the mail merge fields when using vba?

[TonyJollans]

A couple of quick points.

You can run VBA code at appropriate points in a merge if you have a recent version of Word (2002 or later, I think) but it's not necessarily the best solution.

If your code is in a Mail Merge skeleton record, it shouldn't cause all possible letters to be embedded up front - but I will check it out and post back later.

[TonyJollans]

This is going to be a bit of a learning experience for me :)

It does look as though VBA - using the MailMergeBeforeRecordMerge Events is the way to do this. I would welcome confirmation - or otherwise - from others here but in the absence of that I'll try and go ahead.

First: what is your VBA experience? Can you set up Application Events?

[GreenEnvy]

I haven't done a ton in VBA, I learned in VB6 back in college, as well as c++, but I haven't used those skills in several years so I'm pretty rusty.

[GreenEnvy]

OK, I've got the code working so it can read in the merge fields and make a decision, now I just need to figure out how to insert the corresponding document.

[GreenEnvy]

OK What I am currently doing is this,
VBA is using the mailmerge events and is getting the value I want using the MailMergeBeforeRecordMerge event.
It successfully gets the code, and I then store that value in a word document variable.
Back in the word document, there are my IF statements, they check the value of that variable and display the letter accordingly.

The problem is, that it seems I am always 1 page behind, meaning If the current record has code #2 on it, then I won't see that code#2 until the record after it.

Anyone know why there is this 1 record delay? I thought using MailMergeBeforeRecordMerge would allow me to change that data before the record is merged, but it seems it's happening after instead.

PS - The reason I'm doing it this way is I'll be able to use un-nested IF's now, since I can do all the complex IF work in vba and just return a single digit code to the word document to tell it which letter to use.

[TonyJollans]

Sounds like you're making good progress. I'm not very familiar with using these events and don't have time right now to go into it. I will look tomorrow and come back to you.

[GreenEnvy]

Thanks Tony.
I think the issue is that by the time the beforemergerecord fires, Word has already copied the text from the letter, so the change I make to it doesn't apply until the next record. I'll be looking today to see if I can force a refresh of the data. If that isn't possible, is there any way to look forward to the next record in the data source, so I can apply that change earlier?

[TonyJollans]

Sorry, but I'm still struggling for time for this.

Can you expand a little on what you're doing in BeforeRecordMerge after you have determined the letter you want to use (and any relevant parts of the merge skeleton). Are you editing the document or the DataSource - I don't know whether either of these is possible or useful - but assume that they should be both.

Regardless of the delay, are you getting the content in the right place eventually? If so, perhaps updating Fields in the AfterRecordMerge might be a solution - but if your content is actually in the wrong letter, I don't know at the moment.

[TonyJollans]

I've just had a little play with this and think I understand how to do it now - and I suspect all I'm doing is catching up with you :)

I put a field in the skeleton document where I wanted the included text, something like this (without the red bits):

{ INCLUDETEXT "[C:\\Users\\Tony\\Desktop\\Dummy.Doc]" }

And then added a bookmark within the field code - as shown by [ & ] above.

And then in the BeforeRecordMerge event, I coded this:

Const myBM As String = "myBM"
Dim myRange As Word.Range
Dim FileName As String

' Code here to determine the FileName
' And don't forget to put double backslashes instead of single ones


Set myRange = Doc.Bookmarks(myBM).Range
myRange.Text = FileName
Doc.Bookmarks.Add myBM, myRange
myRange.Fields.Update


What this does is find the bookmark round the file name inside the field, puts the new filename in - which causes the bokmark to be deleted - and then reinstates the bookmark and then - critically as far as you are concerned, I suspect - updates the field.

[GreenEnvy]

I've just had a little play with this and think I understand how to do it now - and I suspect all I'm doing is catching up with you :)

I put a field in the skeleton document where I wanted the included text, something like this (without the red bits):

{ INCLUDETEXT "[C:\\Users\\Tony\\Desktop\\Dummy.Doc]" }

And then added a bookmark within the field code - as shown by [ & ] above.

And then in the BeforeRecordMerge event, I coded this:

Const myBM As String = "myBM"
Dim myRange As Word.Range
Dim FileName As String

' Code here to determine the FileName
' And don't forget to put double backslashes instead of single ones


Set myRange = Doc.Bookmarks(myBM).Range
myRange.Text = FileName
Doc.Bookmarks.Add myBM, myRange
myRange.Fields.Update


What this does is find the bookmark round the file name inside the field, puts the new filename in - which causes the bokmark to be deleted - and then reinstates the bookmark and then - critically as far as you are concerned, I suspect - updates the field.

Thanks! I'll give that a try on the weekend.

FYI, I did accomplish this task another way, I did it without the merge events. How I did it, is when the document is first opened, it reads the database and gets all the codes for this receipt of batches. It then matches them with the letters, and sticks the inserttext commands into the document. This is all done before the user even presses Merge.

I then took it a step further, and now have it so the user enters in all the codes and their matching letters in an excel file, and the word document looks in that excel file for matching them up. This vastly simplifies the user task of updating the document with new codes or letters.

I'll give your bookmark method a try, as that would clean up my code a bunch :)