Auto-filling a letter template in Word from fields in a database

[hedges98]

Hello

I have a letter template in Word that has some blanks. I want to populate these blank spaces with fields from the database. These blanks are:

client name
client address
appointment date
practitioner (sender of the letter)

Ideally, I'd like to add a button to a form and when a user clicks it, it will take the information from the fields in that specific record and fill in the blanks of the letter template.

Is this at all possible? I've been reading about Mail Merge but I'm not sure it will do what I need - it seems to be for more bulk mailing. Also, I've been looking at Word form fields which seems to be what I need but I don't know if my version of Office will work with it properly (well, the code I've tried doesn't seem to recognise 'Word.'). I'm using 2000.

Any help/insight appreciated!

[ChipR]

I can't tell how much effort you've put into this. If you are interested in programming with Word from Access, it will be easier if you have a reference set in Access to Microsoft Word Objects. Then check out Microsoft Word 2000 Reference and Microsoft Word Objects

[hedges98]

Well, being completely clueless on where to begin I've just been googling for help and reading stuff. I understand the basics of it, I think. I have set the reference to Word Objects (cheers, google!)

I have been playing around with using Word Forms and trying to get them to update via Access but the code I found (and tailored to my database) doesn't seem to do anything. I'm toying around with it this afternoon but will probably get stuck again.

Cheers for those links though, ChipR, I'll give them a look.

[MMcCarthy]

You can also check out our article in the Insights section on Application Automation. Although the examples used are for Excel and Outlook the basic principles stand.

Mary

[NeoPa]

I think this is all a lot more simple than anyone is giving it credit for.

Look into MailMerge. It is a facility within MS Word. No programming required.

[hedges98]

But isn't Mail Merge used to create bulk letters/messages/whatevers? I only want it to produce one letter when a button is pressed. Is Mail Merge capable of doing that?

From playing around with Mail Merge, I've managed to insert the fields into the template as required but I can't figure out how I'd ensure it only filled labels for the current record/form as opposed to every record stored in the database.

[NeoPa]

To be honest, it's not something I've used greatly myself, but I do recall the ability to populate only one record of data. Look in the MailMerge toolbar. If the worst comes to the worst, run a bunch and simply select only the one you're interested in. I'm pretty sure there is an option to specify which one you want though if you look.

[hedges98]

You can specify a range of records to merge but I need it to be set to do it manually as it'll be used by a few people who need it automated.

For the time being, I've created a letter template through a report in Access that does the job but ideally, I'd like it to be in Word because it's easier for a user to edit etc.

[NeoPa]

That makes some sense. Unfortunately, this seems like you need to control Word from within Access. This is possible with Application Automation, though it is far from straightforward.

One benefit with handling development of the Word code is that you can record macros in word which create some (admittedly pretty basic) VBA code for you.

Remember when automating a foreign application that the defaults are not set up in the same way as they would be when running natively.

[hedges98]

Hmm, I think this is way beyond what I'm capable of. I guess I'll just keep it as I have it.

One annoying thing, exporting the report to Word (which would be ideal!) won't work because it loses the formatting and the images on the report don't export with it.

[NeoPa]

Yes. It's not ideal. The format is actually RTF (Rich Text Format) rather than native Word format as such. Quite limiting really. There's also a Snapshot format which is generally quite unreliable & non-portable, or a PDF option with an Add-In or with later versions. Both of which formats are generally non-updatable anyway though.

Have you considered doing some code from the Word end? It may not be quite as swish as a single-button operation, but you may find it is some help to your users (assuming of course that you are comfortable enough with VBA in Word).

[hedges98]

Ah, rubbish! You'd think they'd export to .doc. Hmm.

I haven't considered coding it from Word because I have no experience in using VBA in Word. I'll show the users what I've done tomorrow as I think it achieves what they want it to do (save them the hassle of switching between Access and Word to enter the client's name and address on a letter template, basically)

[NeoPa]

A thought just occurred to me. Perhaps it can export using XML (I'm pretty sure Office 2007 uses this extensively). If so, reading that into Word may prove to support more than the basic RTF it uses by default. It's a long-shot, but possibly worth a try.

Good luck.

[hedges98]

I'm using Office 2000, sadly. Plus, I think that's just delving deeper into something I know nothing about! Thanks for the suggestion though