Bookmark Mailmerge Problem

[Brandy]

Hi, I'm Brandy and I am hoping someone here can help me. I'm asking only because I could not find the answer in ANY of the forums. OK, I have some code that pulls up word and makes a pretty little letter using bookmarks, BUT, can this approach be used to do a real mail merge with a database with lots of names ( some addresses are Conadian)?

I thank you in advance

Brandy

 

[JamesMcS]

I think you can just do a mail merge with an Access database anyway can't you? As for leaving the country out if it's local, you can tell Word to remove blank fields - but that does mean you'll have to either take the country out of each field where it's local, or have another field in the table called, say, "merge_country" or something.

 

[Brandy]

Thank You JamesMcS, That's Sort of What I Thought.. Hey, Do you ore anyone else know how to get rid of word 'After' It's done a Merge. I Have code that works Pretty Well. It Pulls up word - does it's mail-merge - then saves the new document to the name I want, BUT, now I'm in a quandary. I need to sort by Zip code, therefor I Need more than one document. I want to put word into a loop to do this, but It needs to Either kill It self off - or check if it's already running and go from there.. Any thoughts?

Brandy

 

[JamesMcS]

You can control Word from VBA no probs. As for sorting by zip code, I would imagine that sorting the table by zip does the trick there (correct me if I'm wrong )

It sounds like you've already got the code to open a session of Word, I think it's just a case of application.Quit to close it - but I think that will close without saving so make sure it's saved!

 

[Brandy]

Thanks, I'll try this. I Should have told you that I have multiple zip codes and each one needs it's own mailing, So I have to kill off word or get it to go another round

Brandy

 

[JamesMcS]

Ah I understand - I have a small amount of brain fail today, but my first thought would be this:

1. Create a query based on your merge table, selecting and grouping by zip code. Run it and copy the results.

2. Create a table with an autonumber fields, and a zip code field. Paste results from 1 in there. This should give you a table with a unique ID for each different zip code.

3. Create a field in your merge table called zip_number, or some such.

4. Create an update query to update your merge table with the zip code number from step 2.

5. Create a loop in VBA as follows:

Sub Merge_To_Word()

Dim n as integer
Dim Dbs as database
Dim Rst as DAO.Recordset

Set Dbs = CurrentDB

For n = 1 to dmax("[Zip_Number]",""Merge_Table")

Set Rst = dbs.openrecordset("SELECT * From Merge_Table WHERE Zip_Number = " & n & ";", dbopendynaset)

...[Insert merge code here, using rst as the merge data]

Next n

Set Dbs = nothing
Set Rst = nothing

End Sub

I'm sure there's a better way of doing it but that's my first thought!