Automate mail merge into MS Word with a unique scenario 2

[asherid]

Hi haven't been able to find anything in the archives or FAQ on this.

I am relatively new to database design and am currently faced with a unique scenario (I think). I want to try and get some direction on the best way to approach this before I attempt to tackle this task.

I have a db that I have designed which we'll call a "Supporter Database". It keeps track of supporters, volunteers, donors, etc. as well as different campaigns and their level of involvement in the campaign. I need to merge fields from the results on a search form that I have created, into one of 3 different Word documents (letters) depending on what is in the fields. I know this is a little vague so I will try to give you an example. Please bear with me.

Let's say the campaign is a golf tournament. The level of participation in the golf tournament can consist of golfer, hole sponsor, or prize donor. I need to be able to do a mail merge that selects a different letter (Thank You letter) based on whether the person was a golfer, hole sponsor, or prize donor. The end user can use the created search form to generate a list (results are displayed on a subform) of the people by stating whether they want all the golfers, the hole sponsors, or the prize donors. The user then clicks on a button that will send the info to the appropriate Word document. I can go into more detail if necessary, but I just wanted to find out if anyone has a suggestion on the best way to approach this. If there is a better way to do this without having to use the search form I created then by all means let me know.

Also, the end users for this db have little or no knowledge of Access so the more automation the better (the simpler for them the better).

Clear as mud? Thanks.

Aidan

 

[grtammi]

Check out FAQ702-2379 - should get you started ...

If you don't understand, just nod and smile ...

 

[asherid]

Thanks for the quick response. That definitely gets me started, but how does one go about automating it to select different Word documents depending on the information being merged?

Basically, I want to have one button to click on that will call Thank_You1.doc or Thank_You2.doc or Thank_You3.doc depending on whether the recipient is a golfer, a hole sponsor, or a prize donor.

Thanks.

Aidan

 

[asherid]

Further to my last post, ideally I would like to have one command button that when clicked, would look in Table1 and mail merge all records where chkGolfer = True to DocumentA, all records where chkHoleSponsor = True to DocumentB, and all records where chkPrizeDonor = True to DocumentC. Once merged, the user should be able to look at all three merged documents and make any formatting changes they need to do (in Word) and then print. Is that possible?

Aidan

 

[grtammi]

Hmm ... this method may be a little bit sticky and slow, but it'll do what you want it to.

sub cmdDoMerge_Click()

dim dbs as database: set dbs=currentdb
dim rst as recordset
dim strSQL as string

strSQL = "SELECT * FROM Table1 WHERE chkGolfer = TRUE;"
set rst = dbs.openrecordset(strSQL, dbopendynaset)

with rst
if .recordcount = 0 then
exit sub
else
.movelast
.movefirst
while not .eof
CreateWordLetter("C:\Thank_You1.doc&quot
.movenext
wend
end if
end with

strSQL = ""
set rst = nothing
set dbs = nothing

end sub

As you can see, the above will only do the Thank_You1.doc merge for those where chkGolfer = true. Modify appropriately for the rest of what you need.

HTH

Greg

If you don't understand, just nod and smile ...

 

[asherid]

Thanks so much Greg. I have not been able to try this yet as I am in the middle of working out another section in my database. I will post when I get a chance to try this, hopefully in a day or two. Thanks again.

Aidan