Access to Word Automation

COJMORE

New member
Local time
Today, 17:33
Joined
Jan 10, 2003
Messages
7
Access to Word Automation Help, PLEASE?

Hi,

I have been given this question and I really have no idea how to do it. i was wondering if somebody may be able to help me?

"You need to add another button to the form used for sending a letter to a specific customer, giving details of a car that meets their requirements. This should then generate an appropriately worded document, giving full details of the customer and the car on offer. The date of writing should also be clearly defined?"

I was wondering if somebody might be able to help me out with this problem I have?

Any help wouid be greatly appreciated,

Thanks,

COJMORE
 
Also...

Hi,

Here's a little background info so the question isn't quite so vague...

I have designed my database in MS Access and from within Access, I need to somehow, add a button that when clicked / pressed, will send a letter to a customer concerning there individual needs and requirements.

For example, John Smith, wants a new house in the price region of £250,000, so I need to be able to send him a letter with all details of the houses we have that fit his criteria, and also, the letter needs to show John Smiths full address and the date of the letter.

Thanks
 
Does the "assignment" require you to produce the letter in Word?

If not, I would strongly suggest that you accomplish the task using a Report based on the criteria you mentioned.

Good luck

Brad
 
Hi Brad,

The question comes under the following heading: "Automation" - Access to Word and then the question I stated in my first post.

The question has got me quite confused to say the least. It states that I need to add another button to the form used for sending a letter to a specific customer, giving details of a car that meets their requirement. This then should generate an appropriately worded document, giving the full details of the customer and the car on offer. The date of writing should also be clearly defined.

I presumed that the letter therefore would have to be done in Word, and I have the letter typed out as I want it, however, how I create the button to send the letter and if the letter has to be done in Word, how do I get the letter to load from Access AND show the customers name and car's for example that meet their requirements / criteria?

Again, I would appreciate anyone's input here, I'm really struggling,

Thanks again,

COJMORE
 
I suppose what I'm trying to do is create a mail merge with the data extracted from the exisiting database that has the information a potential customer might be interested in? I just don't know how to do that!
 
First, you have to remember that Access is not a word processor!
Second, make a report the way you want using lables to put all your wording, then for each of the information that you have in your table make a text box. Let me give you an example:

[Date]
[CustomerAddress]
[CustomerCity]& " "&[CustomerState]" "&[CustomerZip]

Dear [CustName],

Here is your information blah blah blah blah,

I hope that I am making sense! The parts that have the squer brackets are going to be your text boxes and the rest are going to be your lables. Good luck:)
 
Is it possible?

Is it possible that a button can be created in Access that can run a macro to automatically populate the blank fields in the MSWord letter or create one in Access?
 
I think I completly missed your question. The report that I was talking about should be made in Access not word. If the report is made in Access, them it can be imported to word.
 
The attached DB contains an example. It uses a letter template AccessToWord.dot for mailmerge.

The letter template is a Word document containing bookmarks such as Recipient, Addrees1, etc. and is saved as type Document Template with a .dot extension. When the button on the form is clicked, the bookmarks in the letter are replaced with the field contents of the current record. The code is in the On Click event of the button.

The code will look for the letter template in C:\MailMerge\. If you put the template in a folder other than C:\MailMerge, you have to amend its location in the code.

To write the code, a reference must be made to the Microsoft Word x Object Library (when the code window is open, choose menu Tools, References...)

The DB and the letter template are in Access 97 and Word 97 formats. They have been tested in Access 2000 and Word 2000. (If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.)


Hope it helps.
------------------------------------
Edited 17 Aug 2006: corrected a typo
 

Attachments

Last edited:
You need to use Word's help files and any manuals you have on the subject of MailMerge using an Access query. Then you need to put some VBA code behind the button they are talking about. The Button Wizard will create the code and there IS an option to open a word document with MailMerge option.

The way I would do this is: I would find a way to mark the record to be expanded into the letter. I would make the button clear all marks, perhaps by running a hidden query, then mark the record.

I would define a MailMerge document that is based on a query that finds all records with the aforementioned mark. Of which you should only have one in most cases (?) if I understood your question correctly and if your button would only mark one car record. Once I opened the MailMerge document, I would be able to save the resulting letter from Word.
 
access to Word

Jon K

Your example db has helped me overcome a problem that I was working on.
Is it possible to go one step further and have the letter printed without the need for further input from the user?

Regards
Keith
 
In the sample that was posted... Before you set WordApp = Nothing, try inserting

WordApp.PrintOut

Search Help Files for PrintOut Parameters
 
Keith,

Sambo's command should work.

The Macro Recorder in Word is another good source for finding commands in Word VBA.

Jon
 
Many thanks Jon K and Sambo. Every day is a school day.

Keith
 
Jon K

Your mailmerge database is excellent. Solved alot of problems I was having. Is there anyway you can ask it to select a template. So you could have a number of letters rather than internally change the location everytime.

Matt
 
Jon-
I downloaded your file, but when I click on the Word button on your form, Word opens but the template does not load. Am I doing something wrong. I am using Access 97. Thanks!
 
Belsha,

You need to specify the location of your file in the code. If you open the Contact form in design view and goto the on click event of the button that opens Word. Scroll down till you see the following code:

'Specify location of template
strTemplateLocation = "C:\MailMerge\AccessToWord.dot"

Enter the location of the template that was included in the zip file Jon gave you where it says "C:\MailMerge\AccessToWord.dot".

HTH Skip
 
lucky said:
Jon K

Your mailmerge database is excellent. Solved alot of problems I was having. Is there anyway you can ask it to select a template. So you could have a number of letters rather than internally change the location everytime.

Matt

Hey,

I would be interested in hearing about a solution to the above too :)
 
samuelbrr said:
Hey,

I would be interested in hearing about a solution to the above too :)


What I did was point to a template based on a combo box


' Specify location of template
strTemplateLocation = cboTEMPLATE.Column(1) & cboTEMPLATE.Column(2)

Where Column(1) was the template's path and Column(2) the template file's name. In this example I gave those columns a width of 0 (hiding them) so all the user sees is column containing the template description.

Hope this helps.



A question of my own however, does anyone have any idea about how I could create a function to do this that can be table driven (i.e. keep all of my bookmark to field mappings in one place).

I was thinking somthing like;

FunctionName("BookMarkName","MappedField","NullMappedField")

That could run through the process...

Currently I'm using 5 templates with close to 75 different mapped values and it is cumbersome to maintain.

Thanks

-Eric
 
Another quick question does anyone know the syntax for opening/closeing the header/footer (I have a couple of bookmarks there that need to be used)?

Thanks

Eric
 

Users who are viewing this thread

Back
Top Bottom