Mail Merge from Access Form To Word Doc (1 Viewer)

I need to find a way to create a one click button that will select the current record from an access 2007 form and mail merge it with a predefined word 2007 document.
From looking around online, I can tell you that I cannot use bookmarks or anything of that sort. It has to be mail merge-able due to the fact that that is how all of our word doc's are already set up. I can't copy it to a word table or excel sheet because we constantly have people in our DB adding to it.

Any help would be greatly appreciated!

Thank you in advance,
David92595

Here's a link that shows how to mail merge to word, and as a bonus it also shows how to include an excel chart in the word document.
http://www.rogersaccesslibrary.com/forum/topic396.html

There is a great little database by Albert Kallal that makes it easy to do word merge from access to word.
http://www.kallal.ca/msaccess/msaccess.html

scroll down to the word merge sample database

Thank you for your suggestions. I believe I have found a script writen by Helen Feddema on 4-22-98 and was last modified on 7-Aug-2007. It can be found at w3.helenfeddema.com/CodeSamples.htm

I have to admit that I don't know much VB, and I hate to ask this of you, but do you/anyone point out what parts of code I need to change. For instance our word documents are not in a Template folder under user\appdata. They are on our Network, so I could really use some help in highlighting where I need to make changes to be able to select a document on our network. For example, \\MAXSERVER\Default\ArizonaDB\file name.

While attempting to figure out this code I created a template in the filepath it is coded for, but everytime I tryed to change an item (for example) .Item("Address").Value = Nz(Me![txtAddress]) I would always get an error and it would highlight "Dim appWord As Word.Application" (near the top of the coding). Once again any help would be greatly appreciated.

Thank YOU!

The code is shown below:
Option Compare Database
Option Explicit
Private Sub cmdWordLetter_Click()
'Written by Helen Feddema 4-22-98
'Last modified 7-Aug-2007
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim docs As Word.Documents
Dim strLetter As String
Dim prps As Object
Dim strDate As String
Dim fso As New Scripting.FileSystemObject
Dim fil As Scripting.File
Dim strTemplate As String
Dim strTemplatePath As String
Dim strTemplateNameAndPath As String
Dim doc As Word.Document
Dim strTitle As String
Dim strPrompt As String

strDate = CStr(Date)
'Check whether template is found in the folder
'Get User Templates path from Word Options dialog
'(or replace with hard-coded path for your computer)
Set appWord = GetObject(, "Word.Application")
strTemplatePath = appWord.Options.DefaultFilePath(wdUserTemplatesPath)
Debug.Print "Template path: " & strTemplatePath
strTemplatePath = strTemplatePath & "\Personal Documents\"
strLetter = "DocProps.dot"
strTemplateNameAndPath = strTemplatePath & strLetter
Debug.Print "Template and path: " & strTemplateNameAndPath

On Error Resume Next
Set fil = fso.GetFile(strTemplateNameAndPath)
If fil Is Nothing Then
strPrompt = "Can't find " & strLetter & " in " _
& strTemplatePath & "; canceling"
MsgBox strPrompt, vbCritical + vbOKOnly
GoTo ErrorHandlerExit
End If

On Error GoTo ErrorHandler
Set docs = appWord.Documents
Set doc = docs.Add(strTemplateNameAndPath)

Set prps = doc.CustomDocumentProperties

With prps
.Item("TodayDate").Value = strDate
.Item("Name").Value = Nz(Me![txtFirstName] & " " & Me![txtLastName])
.Item("Address").Value = Nz(Me![txtAddress])
.Item("Salutation").Value = Nz(Me![txtSalutation])
.Item("CompanyName").Value = Nz(Me![txtCompanyName])
.Item("City").Value = Nz(Me![txtCity])
.Item("StateProv").Value = Nz(Me![txtStateOrProvince])
.Item("PostalCode").Value = Nz(Me![txtPostalCode])
.Item("JobTitle").Value = Nz(Me![txtTitle])
End With

With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown Unit:=wdLine, Count:=1
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub

Sorry, but I am do not use the type of code to automate word as shown in Helen Feddema's code you posted. It would be easy for me to help with code that I know quite well - Albert's code.
Please create a new question on the forum asking for help with code written by Helen Feddema to automate word.

I'm easy going...what ever gets the job done.
I really just need to find a way of integrating Albert’s code in with our current DB. More specifically how to change Albert’s code to specify a document on our network. For example, \\MAXSERVER\Default\ArizonaDB\file name, would be where our document would be.

I also need to know how to get rid of Forms:"what query", "GuiWordTemplate", "frmSearch", "frmMain" from his example without creating errors.
I'd like to also eliminate the form "GuiProgress" but that one is less important.

If it has not become apparent already I really only need a button to click that will Mail Merge a clients info into a predetermined document.

Thank you so much for your help so far on this. I'm new here and have to say I am a huge fan of this site if there are others out there that are like you in their patience and generosity.

Thank you for your time on this,

David92595

OK. Give me 2 days to do this and I will post the code for you to try.

Removed the attached database because it wouldn't open in A2007. Here is the database modified for use with a single folder and word merge template.


On your computer, put both the database and the word folder with its 2 files on your local drive or desktop and try it out just as it is, before changing over to your company template folder and merge template. You will need to change the path to the template folder as explained on the Contacts form when the db opens.

There are instructions on how to set up the template folder and the merge template. You will need the folder called Word and the merge template and merge.888 file in it.

First and foremost, thank you for all your hard work on this project. My boss really likes the perspective of what this can do for us

Sadly, I keep getting an error message when trying to open the access file: "unrecognizable database format". I double checked to make sure we are both using the .accdb format, and we are. The first question that comes to mind is are you running access 2010? We have 2007 in the office. If this is the case to do know of any reliable patches between '07 and '10?

One other thing I noticed is that your word doc is searching for an SQL DB source. We are currently in the process of getting ours up and running, but for now we are stuck in an access DB only.

That's pretty much the only question I feel knowledgeable to ask. If you can think of the actual reason why, more power to you.

Once again, Thanks does not begin to describe the help you are giving me.
...but I'll try...Thank you

Here is an A2007 copy of the simplified word merge database based on Albert Kallal's WordMerge22.

Sorry its taken me so long to respond. Thank you so much, it's working GREAT!

I only have one small problem left that my boss wants me to fix. Every time we mail merge a document we get a pop-up stating: "Opening this document will run the following SQL command:SELECT * FROM C:\Users\David\local\Temp\merge.888
Data from your database will be placed in the document. Do you want to continue?"

The answer is always going to be yes, is there anyway of not getting this pop-up?

Thanks again, your the best!!

David92595

Jeanette,

I have one more favor to ask of you concerning Alberts brilliant mail merge. My boss's boss (owner) loved it so much he wants to expand the mail merge to the extent that we now need to create folders within the "Word Template" Form.

I've looked at Albert's modules, and really I don't even know where to begin with something like this.

Your assistance so far has possibly saved me from the chopping block, so any further assistance you can give me would as always be greatly appreciated.

Hope all is well,

David92595

Hi David, I'm glad to hear that your boss loved the mail merge.
Sorry to hear that you don't even know how to begin creating new folders for the Word Templates. Sorry that I am very busy on a new project and I'm not able to just write the code for you as I did for your previous post.

Would you be willing to have a go at doing the code yourself and post back as you run into problems? Be sure to post the code that you have tried and show where the error is when you ask for help.

I defiantly will do.

I walked into the office today and it turns out that this project is on the back burner until another one gets finished. So I may not respond for a day or two.

Thanks again,

David92595

Hello Jeanette,

I hope this message see you well.
I was looking at my old conversations regarding Albert’s mail merge. I have been tasked with finding a way to select multiple documents from Albert’s mail merge menu to merge at once.

Do you know how this can be accomplished? Or a good forum that explains how this can be done?

I still cannot thank you enough for helping me a year a half ago. It's still a gold star on my record in the company’s eyes ,

David92595

Jennette thank you SO much for posting this database example!!! I have been struggling with exporting records to an RFT format but during the conversion some of the formatting wasn't what I wanted. With this mailmerge script I think I can modify it to suite my needs. Regardless, thank you so much for posting this information!!

Just a quick follow-up. There is a more current version of Albert Kallal's Access to Word script than provided above via the .zip file and it has numerous updates. The updated version works with the newer .docx extensions and it has a feature to create unlimited number of Word Templates. So, you can output your data to dozens of different templates, create new templates, modify existing, etc. all within Access. Really nice.

You can find it on his website page at:

w3.kallal.ca/msaccess/msaccess.html

It is listed under "Super Easy Word Merge".

For those smarter than me using VBA, the script pulls the data from every Text Box on the active form which works great except, a few of mine are Combo Boxes. It still recognizes the Combo Box and allows me to add it as a merge field to Word, but instead of what is displayed on the form, it displays the ID number. So, instead of saying "Mr. John Doe" it merges the number "3".

I believe the problem is the Control Source for the Combo Box uses:

SELECT [Contacts Extended].ID, [Contacts Extended].[Contact Name2]
FROM [Contacts Extended]
WHERE ((([Contacts Extended].Active)=Yes))
ORDER BY [Contacts Extended].[Last Name];

Any idea on how to easily display the Name instead of the ID that links it???

Jeanette Cunningham said:

Here is an A2007 copy of the simplified word merge database based on Albert Kallal's WordMerge22.

Click to expand...

Thks for your sharing this code!.

I applied this code for my mailmerge doc, but I had a problem with "merge.888" Vietnamese font issue as follows:
""CH","BCAT","TDBL","Chi nhánh C?ng Hòa","CHI NHÁNH C?NG HÒA"," - Bàu Cát"," - BÀU CÁT","Bàu Cát","020000021","Phòng Ðang Ký Kinh Doanh - S? K? Ho?ch Và Ð?u Tu Thành Ph? H? Chí Minh"[/I][/I][/I]

Please help me to correct!

Thank you very much!

tienphan, please be aware that you are re-opening a 5-year-old thread.

It would also be useful to note that since you are using multi-national character sets, quoting can become an issue.

This is not my primary specialty, but one thing anyone of us would need to know is the exact symptom you are seeing. Saying "I had a problem" doesn't really tell us much. Can you be more specific?