Generating a letter from MS Access (1 Viewer)

latino · Jun 20, 2006

I need to generate a pre-formatted letter from within MS Access, where the name, address and other information will be pulled from the current record on an Access Form.
Basically it is like a welcome letter I could send to any new client I enter into the data base. Since I am doing one letter at the time, mail merge in MS Word is not an option
What would be the easiest way to do this?
Thanks

workmad3 · Jun 20, 2006

Well, in my DB, I have stored the letters that need to be printed out as forms, and populate it with the required information during form loading.

ansentry · Jun 21, 2006

Since I am doing one letter at the time, mail merge in MS Word is not an option

Yes you could, from Code in Access you can merge 1 document to ? (I don't know the max number)

However, as you only want to send an addressed letter to a new client I would suggest you have a look at my attachment. You will see that it only uses 1 bookmark. If you have a look at the query you will see how I have done this.

Before you try the program, read the "readme" the template is also in the folder.

If you know this then ignore;
You can only use a bookmark once in a document, if you have a bookmark called bmkAmountDue then you can't use it 10 times throughout the document (without writing code in word to re-produce them). I just warn you about this to save heartache when you try and they don't work.

latino · Jun 21, 2006

Great Solution

Hi John, great and simple solution!
I like the drop down where you can select the customer. I just have a couple of questions:
Let's say that I have a group of letter or documents I want to send. (one would be a welcome letter, the other a copy of a contract, the other a reminder letter, etc) Could it be possible have a drop down to select the type of document I want to send then merge it with the client's address?
Also on the original document in MS Word you did a "form letter" and inserted the fields is that correct?
Finally, could you explain this CusDetails: [CustomerName] & Chr(13) & Chr(10) & [Street] & Chr(13) & Chr(10) & [City] & " " & [State] & " " & [PostCode]
(Specially the Chr(#) thing
Thanks

ansentry said:
Yes you could, from Code in Access you can merge 1 document to ? (I don't know the max number)

However, as you only want to send an addressed letter to a new client I would suggest you have a look at my attachment. You will see that it only uses 1 bookmark. If you have a look at the query you will see how I have done this.

Before you try the program, read the "readme" the template is also in the folder.

If you know this then ignore;
You can only use a bookmark once in a document, if you have a bookmark called bmkAmountDue then you can't use it 10 times throughout the document (without writing code in word to re-produce them). I just warn you about this to save heartache when you try and they don't work.

latino · Jun 21, 2006

Great Solution

Hi John, great and simple solution!
I like the drop down where you can select the customer. I just have a couple of questions:
Let's say that I have a group of letter or documents I want to send. (one would be a welcome letter, the other a copy of a contract, the other a reminder letter, etc) Could it be possible have a drop down to select the type of document I want to send then merge it with the client's address?
Also on the original document in MS Word you did a "form letter" and inserted the fields is that correct?
Finally, could you explain this CusDetails: [CustomerName] & Chr(13) & Chr(10) & [Street] & Chr(13) & Chr(10) & [City] & " " & [State] & " " & [PostCode]
(Specially the Chr(#) thing
Thanks

ansentry said:
Yes you could, from Code in Access you can merge 1 document to ? (I don't know the max number)

However, as you only want to send an addressed letter to a new client I would suggest you have a look at my attachment. You will see that it only uses 1 bookmark. If you have a look at the query you will see how I have done this.

Before you try the program, read the "readme" the template is also in the folder.

If you know this then ignore;
You can only use a bookmark once in a document, if you have a bookmark called bmkAmountDue then you can't use it 10 times throughout the document (without writing code in word to re-produce them). I just warn you about this to save heartache when you try and they don't work.

ansentry · Jun 22, 2006

latino,

Let's say that I have a group of letter or documents I want to send. (one would be a welcome letter, the other a copy of a contract, the other a reminder letter, etc) Could it be possible have a drop down to select the type of document I want to send then merge it with the client's address

The answer is yes. After the weekend I will send you a sample db with document choice in it.

Also on the original document in MS Word you did a "form letter" and inserted the fields is that correct?

In the form letter there is only 1 bookmark called bmCusDetails. Open the template and then go to tools / options then under View click bookmarks. The bookmark will then appear in the doucment. Back in the document , click Insert and then bookmarks and you will see that there is only 1 bookmark.

Finally, could you explain this CusDetails: [CustomerName] & Chr(13) & Chr(10) & [Street] & Chr(13) & Chr(10) & [City] & " " & [State] & " " & [PostCode]
(Specially the Chr(#) thing

Code:

Chr(10) returns a linefeed character 
Chr(13) returns a carriage return character 
The two-character string Chr(13) & Chr(10) returns a Windows newline

If you open the query qryCusGeneric and have a look at the second column (CusDetails) you will see how it is done.

Microsoft help under concatenation

You can join two strings of text together by using the & operator in an expression. This is called concatenation.

Post back if you have any further questions, I should post back the sample either Sunday Night or Monday morning (Aust EST)

latino · Jun 22, 2006

Great!

John, thanks again. Your sample will help me a lot.

ansentry said:
latino,

The answer is yes. After the weekend I will send you a sample db with document choice in it.

In the form letter there is only 1 bookmark called bmCusDetails. Open the template and then go to tools / options then under View click bookmarks. The bookmark will then appear in the doucment. Back in the document , click Insert and then bookmarks and you will see that there is only 1 bookmark.

Code:

Chr(10) returns a linefeed character Chr(13) returns a carriage return character The two-character string Chr(13) & Chr(10) returns a Windows newline

If you open the query qryCusGeneric and have a look at the second column (CusDetails) you will see how it is done.

Microsoft help under concatenation

Post back if you have any further questions, I should post back the sample either Sunday Night or Monday morning (Aust EST)

ansentry · Jun 23, 2006

Latino,

Here is the sample I promised you.

Again read the readme first and then all should be OK.

If you have any problems then post back or PM me.

latino · Jun 23, 2006

ansentry said:
Latino,

Here is the sample I promised you.

Again read the readme first and then all should be OK.

If you have any problems then post back or PM me.

Hey John
Thanks again.
I just have a quick question
On the previous one, you had this for the path where the document was
objWord.Documents.Add ("C:\Temp\WordBookMarks210606\AccessTest.dot")

But on this one, there is no path:
bjWord.Documents.Add strDocChoice

I beleive that the strDocChoice reffers to open the document I choose on the drop down, but where do I add the path where the documents are? After the strDocChoice, so it would look like this:
bjWord.Documents.Add strDocChoice ("C:\Temp\")

Jorge

ansentry · Jun 24, 2006

objWord.Documents.Add ("C:\Temp\WordBookMarks210606\AccessTest.dot")

In the first one was:

QUOTE]objWord.Documents.Add ("C:\Temp\AccessTest.dot")

[/QUOTE]
-------------------------------------------------------------

But on this one, there is no path:
bjWord.Documents.Add strDocChoice

In the code is this:

strDocChoice = Me.cboDocChoice.Column(2) ' strDocChoice hold the variable choice made by the user

The path is from the choice that you make in the combo box, note the column(2). Now open the form in design view click the documents combo and have a look at the data source you will see the query has three columns , the first one is (0) second is (1) and third is (2) that is how access see them in a combo. The user see column(1) - Name of the document and the code see what in column(2) "the path and doc name".

The query behind the combo box get its data from tblDocuments.

I should have noted in the readme that I had added a tblDocuments.

With the tblDocuments you can add as many docs and paths as you like and each on will appear in the combo list.

If you want to use different bookmarks then you would have to write code to suit, somthing like I have done for the "Reminder"

Sorry for not pointing out the tblDocuments, hope it all works well for you.

Generating a letter from MS Access (1 Viewer)

latino

Registered User.

workmad3

***** Slob

ansentry

Access amateur

Attachments

latino

Registered User.

latino

Registered User.

ansentry

Access amateur

latino

Registered User.

ansentry

Access amateur

Attachments

latino

Registered User.

ansentry

Access amateur

Similar threads

Users who are viewing this thread