Creating a letter in Word with records from Access

[pw4000]

Hi guys,
i'm not very expert with Access, I'm new with the program and I know only the basics, a few of SQL and a little more. So I need your help to solve a problem (a big problem) I have.

At the moment I have a database in which I save the name, the address and an ID of people I need to send letters to.

From this database I'd like to ask Access who I want to send a letter (and for doing that I created a query that asks me for the ID, very easy).
Once I have the record with the person, I want to send the data to MS Word which would have to places the name (with the right header, depending if I'm sending the letter to a man, a woman, a group of people or to heirs) the adress, the date (I want to select the date in some way, I don't want to obtain the current date) and the ID in the right places, so to generate a letter and save it on a NAS I have with a specific name (date and name of the addressee).

Is it possible to do that by giving to Access only the ID using the query (and then everything else is done automatically)?

Thanks for the help!

P.s.
sorry for my english...

 

[Dick7Access]

Hi PW,
Welcome to the forum, it is a great place to meet a great bunch of nice guys, and one old grouch.
I not sure I understand all of your question, but let me see if I can help with one aspect. I have been doing mail merges for over twenty years and I find it much easier two write my letter in MS Word then do a mail merge from Word.
Here are the steps.
This is with Word 2010 so another version might be a little different.

1. Start a letter and then click on “Mailings”, then on “Select Recipients”,

that will pull up a list of “Type New List”, “Use Existing List”, and “Select from Out Look Contacts”.

You want to choose Use “Existing List”. That will then open your Windows Explore Dialog Box. From there you will have to find where you have your database stored.

Click on that db and it will open a dialog box with all your queries. Scroll to the query that you have made for the records that you want to send letters to.

On your letter go to the spot where you want to put peoples name, titles, address, gender, etc. and click on “Insert merge field”.

That will bring a drop down list of all the fields in you query.

Others here may have a better way to do it but this is the way I like.

If you have any question on this first part do not be shy about asking. 99% of the people here are here to help. 100% started where you are.

 

[pw4000]

Thank you very much for your answer.
It's a good solution, I tried something similar by exporting the record selected by the query to word with the export functions in Access.

But I need a fast way to do that. I have to send a lot of letters so if every time I need to create one I also have to do all those things that you listed me, I would never end.


When I write a letter it has a standard text, but it changes in some parts (but luckily those parts are a few and always the same).
I was looking for a method that reads the fields in my query, understands who is the addressee (gender and so on), adapts automatically the letter by changing certain words if the letter heads to a man instead of a woman, puts the fields in the right places (for example the date in a certain area of the letter and the ID in another).

Everything with one or two operations made by me, no more, so that I can save time.

P.S.
I use Office 2010, so the instructions you gave me are perfect for my version!

 

[Dick7Access]

Thank you very much for your answer.
It's a good solution, I tried something similar by exporting the record selected by the query to word with the export functions in Access.

But I need a fast way to do that. I have to send a lot of letters so if every time I need to create one I also have to do all those things that you listed me, I would never end.


When I write a letter it has a standard text, but it changes in some parts (but luckily those parts are a few and always the same).
I was looking for a method that reads the fields in my query, understands who is the addressee (gender and so on), adapts automatically the letter by changing certain words if the letter heads to a man instead of a woman, puts the fields in the right places (for example the date in a certain area of the letter and the ID in another).

Everything with one or two operations made by me, no more, so that I can save time.

P.S.
I use Office 2010, so the instructions you gave me are perfect for my version!

Are you saying that you are sending the same letter to a lot of people
or
a lot of different letters to a lot of people.

 

[I Need Help]

Hi pw4000,
From what you're saying it sounds like you want to go to a particular record, click a button in your MS Access form and automatically have it open a MS Word file and fill it in for you, for the particular person you are viewing in access. If so, you are in luck! I had the same problem about a week ago and found some code that does just that. I'll upload what you need in a bit
Regards Sam

 

[I Need Help]

OK I found it.
The code in VBA to do this is called a MergeSingleWord (I think)
The module containing all the code to do this was written in 2001 by a guy called Albert Kallal, so all credit to him.

I've attached the DB containing everything you need. What you need to do to get this to work for you is:
1)unzip the archive attached and decide where you want to store your db (move the zip archive contents there)
2) open up the db and then open the modWordCode module for editing
3)the third line of code specifies the location of the letter file your db is going to open. The current arrangement is that the button is opening the letter.doc file in the word folder in the archive. So now specify the new path to that word folder, depending on where you stored it
3)After saving your changes you should find that the merge single word button in the form works.
4)now that you've got the button working you want to personalise it for you. So alter this guys table and form to suit you. Next do as Dick7Access said and create your word file and link it to the msaccess table and add all your fields etc. Save this word file as letter.doc (word 97-2003 document or the code doesn't work) and replace the letter file in the word folder with your new file.
Now when you press the button everything should work.

P.S. If anyone does manage to edit this code to open new msword file formats let me know please

 

[pw4000]

Are you saying that you are sending the same letter to a lot of people
or
a lot of different letters to a lot of people.

I have to send one letter (with a standard text) to a lot of people. I need to change only some parts in the letter, except from the header and the information about the adressee, I need also to make some corrections in the "grammar format" to adapt it at the language rules (I'm italian)

OK I found it.
The code in VBA to do this is called a MergeSingleWord (I think)
The module containing all the code to do this was written in 2001 by a guy called Albert Kallal, so all credit to him.

I've attached the DB containing everything you need. What you need to do to get this to work for you is:
1)unzip the archive attached and decide where you want to store your db (move the zip archive contents there)
2) open up the db and then open the modWordCode module for editing
3)the third line of code specifies the location of the letter file your db is going to open. The current arrangement is that the button is opening the letter.doc file in the word folder in the archive. So now specify the new path to that word folder, depending on where you stored it
3)After saving your changes you should find that the merge single word button in the form works.
4)now that you've got the button working you want to personalise it for you. So alter this guys table and form to suit you. Next do as Dick7Access said and create your word file and link it to the msaccess table and add all your fields etc. Save this word file as letter.doc (word 97-2003 document or the code doesn't work) and replace the letter file in the word folder with your new file.
Now when you press the button everything should work.

P.S. If anyone does manage to edit this code to open new msword file formats let me know please

Thank you so much! really!
I'll let you know what I'll manage to do!

 

[I Need Help]

you're welcome. good luck and let me know how you get on
Sam

 

[pw4000]

Ok, so. I changed some things in the file to fit my needs.
Now I have the form that shows me all the fields I want to place in the letter.

I have a couple of questions now:
1- I want to create a menu from which the user chooses the header of the letter. How can I create it?
2- In the letter I have the header and also other words that change depending on who is the addressee. How can I change those words automatically?

For example (a simplified letter, only to make you understand) could be:

Dear Sir
Name and Surname
I would like you Mr. Name and Surname to call me as soon as you can
Regards

I have to change every time the two parts in bold with their adequate correspondent, considering if it is a man, a woman, a group of people or the heirs of a person.

3- (i forgot this) I need now also to connect permanently the database to the letter. So how can I do that? When I tried to use the "print merge" (i hope this is the right name) I had every time to specify where to place the fields. I would like to do that once and then every time I open the database and add new records, Access or Word remember where to put those fields..

 

[I Need Help]

I'm not quite sure what you mean. Basically create fields in your table that cover all the info you need.

So for a letter a good starting place would be:

Title (this one you could create a lookup to give you a dropdown with options e.g. Mr, Mrs, Miss etc.)
Forename
Surname
Address 1
Address 2
Address 3
Address 4

If you edited the table that was already there, great. If not, go to the form, and change its data source to the table you are using.
Then close and save all that, before opening ms word.
Go to the mailings tab and start a mailmerge, linking it to the table you're interested in. Insert all the fields (may need to insert some of them multiple times e.g. the names which may come up in the letter more than once).

After saving this as letter.doc (Word 97-2003 document) replace the letter.doc in the Word\ folder so it now opens your letter.
then it should work.

As for the headers bit, I'm not 100% so I'm going to have to freestyle.
If what you want to put in the headers is like one of a few things then you can create a field for it with dropdowns and insert the field in the header itself whilst doing the mailmerge.

If you need the whole paragraph to change, whilst being specific to who you're talking to then I recommend doing an if function in word. This essentially says if field x = (this) then write (that). Bear in mind when you're telling it what to write, if you want it personalised to them, write it in word first with fields and everything, then cut and paste it into that if statement box.

As for what you said about having to do it every time, I need to read exactly what you wrote again, but...you shouldn't have to do any of that stuff. Set it up once and it should be good to go.
So if the template file is letters.doc it should open up your filled in version for a particular client as letters1.doc.

Hope this helps
Sam

 

[I Need Help]

I may be wrong, but I think where you're going wrong is that you finished the merge. You don't need to do that, thats what you do to create the actual letter from word, you're going to do that automatically form access.
So go to mailings tab --> start mail merge
Select recipient list
Write your letter and add your fields then save. Don't finish the merge.
Now what you have is essentially a ready to go template

 

[pw4000]

Ok, so here follow some questions:

1- the header has not a complex format. I need only (Mr. Mrs. Miss. etc) so I need a dropdown menu (maybe directly in a dedicated field in the table). But I don't know how to create it.
2- once I know who is the addressee I know also his gender, so I know what words and in which way these words have to be changed. I have to do some tests, but where can I set the "IF" function?
3- i want a particular name for the file I generate. I don't like "letter", I prefer a name made with this format: "Letter 04/10/2013 FirstName Surname", so a name that uses again the fields of my table ("date" and "FirstName Surname"). Is it possible?

 

[I Need Help]

Ok so for question 1
open your table in design view and then for the data type next to the title field select lookup wizard -->I will type in the values that I want
then enter all your titles e.g. mr, mrs, miss dr etc in one column and your dropdown is done

2- if you are going to be using words like he and she in the letter then create a gender field with lookup (same as above) and instead of male and female use he and she so the field is ready to go into word
the if functions etc are in the mailing tab when you click rules (im in word 2013 so im not sure if it'll be exactly the same for you)

3- automatically naming the file is probably doable, but to be honest I don't know how to do it. I just used that ready made code and made it work for me
however you may find it handy to create a field of data type: attachment so that you can easily store and manage any correspondence for that client

 

[pw4000]

1- Ok I did it, it works nicely in Access!
2- If I create a field with "he" or "she" then I don't think I need a "IF" function. I can use the same method used for the header or the name etc. But this makes me loosing time because there's one new field that has to be compiled. Clarified that the field "he"/"she" depends from the header I think that the ideal way to solve the problem is the "IF" function, so I can save time (that is the main problem I want to solve).
3- The problem is that I save the letters with a particular name because I have another program (Nuance Paperport) that stores files and needs a name which can be found easily. In Paperport I type in the name of the person and the program gives me back all the files named with that name. So I need to find out a solution also for this little problem.

 

[pw4000]

I think I'm almost done. I have a final little problem now:
I have to apply an IF function to a word.
But it is not simple.

I have to build a function that does:
IF <header> is "Sir" OR "Miss" then write "X", otherwise write "Y".
I can't find how to do that...

 

[I Need Help]

That is where you stop using the MS Word interface to create your rule and start typing/coding your stuff. The code is hidden text which will be between these marks -->{ } . To view/edit this hidden text you hit alt + F9.
Now you can do things like
if this is x AND that is y then do this.
Or what you're saying which would be an OR
So that is what you need to look into I think. but I don't know how to write that code unfortunately.

If you find out how to do it let me know please I've been meaning to start a thread asking about that.

 

[pw4000]

Yes, I have been looking around to find out how to set an IF function chained with another. There are some examples around the internet (also on the official Office support site) but I can't understand how to do what I need.
I'll post the link to a possible answer to my question, maybe you can understand how to build the command...

 

[pw4000]

here it is
http://office.microsoft.com/en-us/word-help/examples-of-if-fields-HP005187676.aspx
If you look down in the page there's a dropdown section "specify multiple conditions"...