Mail Merge from Access with SubReport

Re: my previous question on this subject; I have a neat solution to post. Can be used for large Word templates that are too complex to design as a report.

Function OpenTemplate(TemplatePath As String, TargetFile As String) As Object

' Used as many template variations and docx; actually called from a table lookup where paths and files are held.

On Error GoTo Err_Template

Dim objWord As Word.Document
Set objWord = GetObject(TemplatePath & TargetFile)
objWord.Application.Visible = True

Set OpenTemplate = objWord

Exit_Template:
Set objWord = Nothing
Exit Function

Err_Template:

If Err.Number = 432 Then
'MsgBox "Cannot proceed with this Merge" & vbLf & vbLf & "Template and /or File are not present!", vbCritical, "Contact Programme Administrator"
'Not needed as error message in Calling Function
Resume Exit_Template
Else
MsgBox Err.Number & vbLf & Err.Description, vbCritical, "OpenTemplate error"
Set objWord = Nothing
End If

End Function

Function myMMwithCode()

' Reference Microsoft Word Object Library

On Error GoTo Err_MM

Dim myDoc As Word.Document

Set myDoc = OpenTemplate("c:temp", "MM Order Header.docx") ' see note

Dim rs As Object
Set rs = Application.CurrentDb.OpenRecordset("SELECT * FROM [Q-Order Header Only] WHERE [OrderID] = " & 16)

With rs
'Note z~prefix; so that fields appear at the bottom of Word's Document Properties list]

myDoc.CustomDocumentProperties("zFirstName") = !FirstName
myDoc.CustomDocumentProperties("zLastName") = !LastName
myDoc.CustomDocumentProperties("zOrderID") = !OrderID
myDoc.CustomDocumentProperties("zOrderDate") = CStr(Format(!OrderDate, "Medium Date"))
myDoc.CustomDocumentProperties("zTotal") = "£ " & CStr(!Total)
End With

Dim rsTable As Object
Set rsTable = Application.CurrentDb.OpenRecordset("SELECT * FROM [Order Details] WHERE [OrderID] = " & 16)

Dim iCount As Integer
Dim strQuantity, strUnitPrice, strLineTotal As String
Dim sTotal As Currency

If DCount("[OrderID]", "Order Details", "[OrderID] = " & 16) > 0 Then

With rsTable
.MoveFirst

Do Until .EOF
strQuantity = strQuantity & CStr(!Quantity) & vbCrLf
strUnitPrice = strUnitPrice & Format(!UnitPrice, "Currency") & vbCrLf
strLineTotal = strLineTotal & Format(!UnitPrice * !Quantity, "Currency") & vbCrLf

sTotal = sTotal + (!Quantity * !UnitPrice)

.MoveNext
Loop

End With

myDoc.CustomDocumentProperties("zQuantity") = strQuantity
myDoc.CustomDocumentProperties("zUnitPrice") = strUnitPrice
myDoc.CustomDocumentProperties("zLineTotal") = strLineTotal
myDoc.CustomDocumentProperties("zTotal") = "£ " & CStr(sTotal)

Else ' No Lines
myDoc.CustomDocumentProperties("zQuantity") = ""
myDoc.CustomDocumentProperties("zUnitPrice") = ""
myDoc.CustomDocumentProperties("zLineTotal") = ""
myDoc.CustomDocumentProperties("zTotal") = ""

End If

myDoc.Fields.Update

Exit_MM:
Set myDoc = Nothing
Set rs = Nothing
Set rsTable = Nothing
Exit Function


Err_MM:

If Err.Number = 91 Then
MsgBox "Cannot proceed with this Merge" & vbLf & vbLf & "Template and /or File are not present!", vbCritical, "Contact Programme Administrator"
Resume Exit_MM
Else
MsgBox Err.Number & vbLf & Err.Description, vbCritical, "Merge error"
Set myDoc = Nothing
Set rs = Nothing
Set rsTable = Nothing
End If

End Function

__________________________________________________ ______________

Technical Notes - Word 2007

First, create the Custom Document Properties in each Template word file

File/Prepare/Properties/Document Properties/Advanced Properties/Custom/ [prefix each fieldname with z so that they all appear at the bottom of the list]

Now, INSERT these new Document Properties in the Word Template

Insert/Quick Parts/Filed/DocProperty/


Post your answer or comment

comments powered by Disqus
Hi,

I have created a mail merge from a query in Access and it works fine if you run it from word but I would like to know how to automate the mail merge from Access using the Command button. Please can someone advise.

Thanks

Alex

Group,
I am somewhat familiar with ACCESS programming, definitely not an expert, but I can find my way around and maintain other's code, just haven't created from ground up. I'm also familiar with VBA coding, I do quite a bit in MS WORD, but now I'm stumbling around in Access 2010 and trying to accomplish a simple task, creating envelopes from data in a table. If I "export" to mail merge, all works, but I want to take the steps:export word merge etc...etc.....and put this in code. My thought is vba code behind the "print envelope" button from my Access form, I'm assuming the on-click event. Am I correct that the only way to get my envelopes is a mail merge, and if so, I'm looking for anything to get me started with "VBA mail merge" in Access. I've been trying to test this with a macro function, but not getting anywhere with this.
Any getting started hints would be appreciated.
Thanks,
Diane1100

Hi all

I've scoured the various threads on the subject of mail merges from Access and it seems I am not alone in getting frustrated!

I have a mail merge document which extracts data directly from an Access 2007 query. This works fine from the Word menus, however I want to run the merge from a command button within my Access application, which will select a subset of this query data based on the record ID of the form I am in.

When I run the code, Word opens up but I get an error from Word saying the database has been placed in a state by user Admin that prevents it from being opened or locked. Access VBA gives me a run-time error 5922.

Settings on Access are:
Default open mode : shared
Default record locking : no locks
Open databases by using record-level locking : no

Here's an extract of the code I have written.

Set objWord = GetObject(, "Word.Application")
If Err Then
Set objWord = New Word.Application
WordWasNotRunning = True
End If

On Error GoTo err_handler

'Word Object is created - now let's fill it with data.
With objWord
.Visible = True
objWord.Activate


Set myDoc = objWord.Documents.Add(strDocPath)
' allow time to open
DoEvents

With myDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="W:4. Databases and Documentsmydb.accdb", _
Connection:="QUERY myQuery", OpenExclusive:=False, _
LinkToSource:=False, ReadOnly:=True, _
AddToRecentFiles:=False, _
SQLStatement:="SELECT * FROM myQuery where WFID = " & CStr(WorkflowID)
.Destination = wdSendToNewDocument
.Execute
End With

' do other stuff

End With


Does anyone out there have any ideas?

Thanks, JD

I created a database in Access 2000 and set up user-level security using the wizard. All users have at least read access to all objects. I also have an MS Word mail merge set up linking to a query in the database. The code in Access opens the main merge document, and a code module in the document performs the merge and displays the print preview. The merge document opens a second instance of the database in order to link to the query.

Here’s what happens:

I log on to the database as any user, (doesn’t make a difference which one) and select the report to run the merge. Word opens and displays an error that I do not have appropriate permissions. I click “OK” and it goes ahead and performs the merge.

Next, I log on to the database as an Admin user and view the user and group permissions. For each group, I remove and immediately re-apply the read access.

Now, I can close the database, log in as any user and run the merge as before with no error -- until I reboot the PC; then the error comes back as before.

Anyone have any ideas on how I can keep the error message away?

Hi,

I have an access database and was hoping someone can advise me on how to create a mail merge from the access database, so that I can send emails that are personalised.

Thanks

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

Hi,

I've been helped before by this forum so am asking for some more. I have a stores order form in word which I use as the template for a mail merge (due to the complexities of the form I haven't been able to convert it to an access report).

When I close the form in access from which the data is obtained it asks if I want to link to an existing document which I do. I click on OK and am presented with the document options and I select the correct one. I then do the merge and save the document with the meged data. All is as I want to happen so far. I then close down the word forms which returns me to the mail merge wizard which tells me that the source is different to when I started the wizard and do I want to change it. Irrespective of whether I say yes or no it tells me the remote server machine does not exist or is unavailable. This happens every time.

Why and how can I stop it from doing so?

Hi

I need some help urgently with a mail merge I created.

I have created a mail merge based on a query from my access database, it works fine when the database is not open but when I try to access it when my database is open - I get a Data Link Properties windows asking to specify a username/password. The username is already Admin and the password field is blank. I have tried all the options but it always fails and I end up with a static word document.

Ideally, I want a 'Letters Form' where users can click on a hyperlink or button which would then open Word and the merged document.

Can anyone help me with this please...it is very frustrating.

Many Thanks in Advance.

Hello all,

I've been looking into Mail Merges from Access through VBA and after much messing got a basic mail merge working, but I want to be able to do a bit more with it, the only problem is I'm not sure if a mail merge can do what I want or whether I should be looking in another direction. Anyway I'll describe the problem and if anyone can point me in the right direction, thanks.

The merge in question is a report of Product coverage in Magazines, an access database stores the magazine name, circulation and a UNC link + filename where jpg pictures can be found.

As each Magazine article can be x number of pages long and a UNC path and filename are stored in Access allowing jpeg files to be inserted into the mail merge for each page of an article, is there any way I can change the header for every page after the first one so that it displays "continued".

The template code in word is posted below.

{MERGEFIELD "tbl_Media_CompanyName"}
{MERGEFIELD tbl_Coverage_Cover_Date @"MMMM YYYY"}
Circulation - {MERGEFIELD Circulation}
{INCLUDEPICTURE "{MERGEFIELD "PictureLocation"}"}

Also is it possible to create a Table of Contents - I realise I could promote the {MERGEFIELD "tbl_Media_CompanyName"} as a Outline Header 1 in the template, but this will show several entries for each multi page article when all I want is the first page of an article to be referenced.

Thanks again for being bothered to read this far.

Jon

I am trying to automate mail merge from access to word and within Word use Acrobat PDFMaker Office COM addin to send emails with each record saved as a pdf and attached to an email. I am able to do it manually but I'd like to automate it,does this sound possible?

I tried to record a macro while doing it manually but that didn't come up with anything usable, which is why I wondered if I could actually control PDFMaker via vbscript.

Thanks to anyone that can give me some insight.

Hi,
I have an access database with hundreds of records, and would like to be able to have a table of letters linked to a word 2003 doc location. I want to be able to select one or many records on a form, then select the letter, and then a button to Mail Merge.

I have been trying to do this for ages now, and have never got the hang of it. Most replies I have had have been to download an example - It would be great if I could have instructions on this forum.

Thank you all in advance,
Dan James

Lads,

Here's a little problem that is driving me nuts. I know what is wrong and yet I can't fix it!

I have an Access D/B and a mail merge word doc which gets its data from a table in the database. Now the date is in the correct format in the table i.e. uk/european format. The 5 of january 2005 would be 05/01/2005.

However when this gets into the word doc it changes to USA format i.e. 01/05/2005.

Not very desireable! how do I fix it?

Thanks in advance,
Liam

Twilight Zone Phormat Problem

I have a mail merge from Access into Word that works great except for a baffling problem with phone number formatting. Instead of (987) 654-3210, on certain records the merge produces 9876543210. I cannot identify a pattern to explain it, but it is not random. Certain records consistently leave the format behind on the phone number, some on the fax number, and some on both. This problem is affecting about 1/3 of the contact records. I have the Input Mask on Phone and Fax in the table set to !(999") "000-0000;0;_. Is anyone familiar with this problem?

I am not sure where to begin to make this work and any help would be greatly appreciated.

I have a access table name Customers. The table has 4 fields customer number, name, address, and phone number.

I have a letter in microsoft word titled Welcome Letter that I would like to be filled in based on the current record/customer being viewed.

The access form contains a button that I would like the user to click to open the word doc and fill in the customer number, name, address, and phone number.

First off, I am not sure how to have the button accomplish this.

Secondly, In the word doc do I just set it up using the normal mail merge or is there something else that needs to be done?

I have used mail merge before but not in combination with access.

THIS CAN'T BE HARD!
But it is for me.

What I want. I have a mail merge doc that works perfectly. But the date of the letter after it has been saved changes when reopened.

An Access mdb has data for membership. when a merged letter is sent out, it is saved for later review.

I have tried both the word fields, "date" and "createdate"! Each has their own errors.

When I use the date field it always updates to todays date. So if the letter was originally sent yesterday when we reopen the document its now with todays date.

If I use createdate the date is always the date that the document was originally created. I even tried merging to a template .dot file hoping the create date would be updated thru the new document when you use wdsendtonewDocument destination.

Any hints help or direction would be greatly appreciated.
Google searches address everything but what I am looking for. :-)
Access 2000 and word xp are the programs I am using.

Dan

In doing a mail merge from access to word, all seems fine, except that the date format is incorrect. On the form in access it shows as dd/mm/yyyy correct for UK. However checking thro the mail merge doc it has swapped over to mm/dd/yyyy, american version. Can anyone help me with this??

I have set up a Word document which draws its information from an Access Query. The Query has a calculated field - Tax: CCur(Int((((Int(((100*[Total Paid]/(100-[Rate of Tax]))*100)+0.5)/100)+0.01))*[Rate of Tax])/100). The ouput of this query is currency and includes any final zeros (eg $120.50). But when I insert this field into the mail merge document it loses any final zeros. How can I hold the proper format in the mail merge document?

Hi,

I'm trying to do mail merge from Access. My code works fine. The only problem is I have 3 different templates to use (introductory letters, thank you letters). I created a combo box, choose a template. What i want is after clicking on the command button, ACCESS automatically mail merge with the chosen the template. How do I modify my code so it would work?

Thank you,
Meo


Code: Option Compare Database Public Function CreateWordLetter(strDocPath As String) 'if no path is passed to function, exit If IsNull(strDocPath) Or strDocPath = "" Then Exit Function End If Dim dbs As Database Dim objWord As Object Dim PrintResponse Set dbs = CurrentDb 'create reference to Word Object Set objWord = CreateObject("Word.Application") 'Word Object is created - now let's fill it with data With objWord .Visible = True .Documents.Open (strDocPath) 'move to each bookmark, and insert correct text. .ActiveDocument.Bookmarks("firstname").Select .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomFirstName)) .ActiveDocument.Bookmarks("address").Select .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomAddress)) .ActiveDocument.Bookmarks("city").Select .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomCity)) .ActiveDocument.Bookmarks("state").Select .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomState)) .ActiveDocument.Bookmarks("zip").Select .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomZip)) .ActiveDocument.Bookmarks("firstname2").Select .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomFirstName)) End With 'find out if the user would like to print the document at this time. PrintResponse = MsgBox("Print this document?", vbYesNo) If PrintResponse = vbYes Then objWord.ActiveDocument.PrintOut Background:=False End If 'release all objects Set objWord = Nothing Set dbs = Nothing End Function Private Sub cmdMailMerge_Click() CreateWordLetter "Z:StudiesCleft Utah New CDC StudyUtah mother fileIntroCase1.docx" End Sub

I have a mail merge function which is supposed to merge the records (multiple) from a query (in which the user selects different criteria) into a letter in Word.

The merge works - except that it only merges the first record. I want to be able to merge all the results of the query into the document, producing several personalized letters. The code is outlined below, if anyone can help me figure out what I'm doing wrong, it would be greatly appreciated.

Private Sub cmdPrintLetterReport_Click()
Dim Whereclause As String
If Not IsNull(Me.cboFilterLocalNumber) Then
Whereclause = Whereclause & "([Local#] = """ & Me.cboFilterLocalNumber & """) And "
End If
If Not IsNull(Me.cboFilterLocalType) Then
Whereclause = Whereclause & "([LocalType] = """ & Me.cboFilterLocalType & """) And "
End If
If Not IsNull(Me.cboFilterAgeTo) Then
Whereclause = Whereclause & "([Age] < " & (Me.cboFilterAgeTo + 1) & ") AND "
End If
If Not IsNull(Me.cboFilterPartyAffiliation) Then
Whereclause = Whereclause & "([PartyAffiliation] = """ & Me.cboFilterPartyAffiliation & """) And """
End If
lngLen = Len(Whereclause) - 5
If lngLen

My code is as follows:


Function RunWordMacro()
Dim WordApp As Object
Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("S:MGMTSRVINDIVIDUAL STAFF PROJECTSMSwagle1mailmerge.docm")
WordApp.Visible = True

With WordApp.ActiveWindow

.Selection.WholeStory
.Selection.TypeText Text:="Dear Dr. "
.ActveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""LAST_NAME"""

.Selection.TypeText Text:=","
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=vbTab
.Selection.TypeText Text:="Your first surgery is on "
.ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _
, Text:="""MinOfSCHED_CASE_START"""
.Selection.TypeText Text:="."
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:=Chr(11)
.Selection.TypeText Text:="Thank You."
With .ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
End With

End With
Set WordApp = Nothing
End Function
I'm trying to automate a Word Mail Merge through access. I keep getting the error 424 on the lines with wd in them. I've read in other threads that you go to Tools and References to fix this, but I can't click References. Please let me know.

Thanks!

Hi Guys,

I have this code block



	Code:
	Private Sub Command55_Click()
 
On Error GoTo Err_Command55_Click
 
   Dim LWordDoc As String
   Dim oApp As Object
 
   'Path to the word document
   LWordDoc = "D:Template Test.dot"
 
   If Dir(LWordDoc) = "" Then
       MsgBox "Document ain't there!"
       Exit Sub
   Else
           'Create an instance of MS Word
       Set oApp = CreateObject(Class:="Word.Application")
       oApp.Visible = True
 
 
       'Open the Document
       oApp.Documents.Add ("d:template test.dot")
   End If
 
Exit Sub
 
 
Err_Command55_Click:
   MsgBox Err.Description
   Exit Sub
 
 
End Sub

The template is set up as a mail merge from a query within this database.

The code works essentially, but when the template opens as a new document, there are two issues,

1) It does not merge the fields from the query that it should merge. It opens with the fields like . If i close the DB, it works with no problem.

2) It names the document Document1.doc, when ideally i would like to be able to pass the name of the document in the add string, ie. TestingTemplates.doc. I've googled about, but cant seem to see the option, so i assume i'm using the function in the wrong way.


I hope you guys can help me


VBA_Googler

Hi,

I currently have VB code that forces a mail merge from Access to Word. I want to modify the code so that a user can browse and select the path for the Access Database (source) and the Word Template. When I have tried to make the file path for the database a variable, it has produced a run-time error. Here is my code. The bold word is my variable path name, and the path name it is (I believe) set equal to is listed below.

Dim objWord As Word.Document
Set objWord = GetObject("C:WorkRBI NarrativesNarrative Merge Template.doc", “Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:="Dim objWord As Word.Document
Set objWord = GetObject("C:WorkRBI NarrativesNarrative Merge Template.doc", “Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:=DataBaseLocation, _
LinkToSource:=True, _
Connection:="TABLE [Narrative Data Source]", _
SQLStatement:="SELECT * FROM [Narrative Data Source]"
objWord.MailMerge.Execute

"C:Program FilesMicrosoft OfficeOffice11SamplesNarrative Creator.mdb"

Can anyone see a problem with my code or see any problems with what I want to do?

Thanks

Hi, I'm having trouble with a mail merge - I'm trying to get a set of labels printed from Word, which have been mail merged from Access. The labels are for letters going out to people who need to renew a subscription, so there's a field that can be changed in the design view of the table containing all the subscribers to contain only those who joined between 2 particular dates. The problem is that Word won't display more than 16 labels at a time, just enough to not quite fill a page. If I change the dates so that there are fewer than 16 subscribers shown, Word still creates 16 labels and leaves some blank.
I didn't set up the database, which has apparently worked for at least 2 years until somebody messed around with it, and I'm almost completely clueless with Access, so any help would be greatly appreciated.
Cheers

Hi

mail merging from access query to word (2003). Ordinary text in the word doc has underline (chosen through formatting text) and this appears on screen however printouts do not show underline.

Think it is to do with mail merge as on some computers the underline is on the printed merged document but not on the one showing field names.

Although did read somewhere else that the issue might be with graphics card!

This is happening in a classroom - but only on 5 out of the 20 computers.

Any thoughts gratefully received


Not finding an answer? Try a Google search.