Help with Mail Merge please ?

[bellevue]

I hope someone can help please.

I have a command button on my form and when you click it (on the record for, say, "Wally Chambers") it prints off a report with the required data from Wally Chambers' record. The code is:

Private Sub Command233_Click()
On Error GoTo Err_Command233_Click

Dim strDocName As String
Dim strFilter As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
strDocName = "Directions Letter WC"


strFilter = "[Telephone] = Forms!Reservations![Telephone]"
DoCmd.OpenReport "Directions Letter WC", acViewNormal, , strFilter

Exit_Command233_Click:
Exit Sub

Err_Command233_Click:
MsgBox Err.Description
Resume Exit_Command233_Click

End Sub

I'm fine with this and it does what I want.

However, the problem is that I created this code so long ago I can't remember why or how it works; I seem to remember that I created a link (or filter ? maybe) between the fields "Telephone" (this field is unique ro each record) so that the data used to populate the report is just from Wallys' record.

I've googled Mail Merge but this hasn't helped at all.

I have a new task and now need the button to open a word mail merge document (populated with Wallys' details) but I'm not sure what I need to do to change the code.

The path for the word document is "H:\CorrespondenceLetters\MailMerge\BrksDednsEm.do c"

I definately want to point to a mail merge word document and not a access report for this new task.

Can anyone help please ?

[June7]

Google: Access Word mail merge

Does this help
http://office.microsoft.com/en-us/ac...001233657.aspx

[bellevue]

I forgot to say that I've googled Mail Merge but this hasn't helped; thanks for the link but it's not relevant for my needs and it relates to Access 2007 (I'm using Access 2000).

[June7]

Here's one specific to Access 2000 http://support.microsoft.com/kb/209976

I have never done mail merge with Access and Word. Why do that instead of Access report?

The code you posted passes the [Telephone] filter criteria to the report by use of the WHERE CONDITION of DoCmd.OpenReport

[bellevue]

Thanks for that June7, I'll have a look at that link.

Thanks also for the explanation of the bit of code; I recall it was set up to go to a report but I'm wanting to edit it (if possible) to go to the word mail merge.

When you use a report the document that appears can only be emailed as an attachment and that's not what I want; by merging to word, I can hit the Mail Recipient button and send it more easily as an email - hope that explains why I don't want to use a report.

[June7]

Do you mean email by the SendObject method? How complex is the report output? There is alternative to the SendObject. Review:
http://www.dbforums.com/microsoft-ac...ccess-vba.html

And there are add-in utilities.
http://www.fmsinc.com/MicrosoftAccess/Email.asp?id=553&gclid=CKHwh9_ohbUCFUxxQgodWEAA3g

[bellevue]

Thanks June7

Unfortunately, no, I didn't mean that. I meant that, by creating the mail merge, I can hit the "mail recipient" button.

Best wishes

[June7]

Oh, you mean 'mail recipient' on Word.

That Access 2000 link seems rather explicit. I hope it helps. Good luck.

[bellevue]

Thanks June 7; unfortunately, the link doesn't help that much.

I've been doing some more research and have managed to come up with the following code to do what I want:

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("H:\enjoy120210\CorrespondenceLetters\Ma ilMerge\BrksDednsEM.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Reservations.
objWord.MailMerge.OpenDataSource _
Name:="H:\enjoy120210\Reservations.mdb", _
LinkToSource:=True, _
Connection:="QUERY MasterDataSource", _
SQLStatement:="SELECT * FROM [MasterDataSource]"
'Execute the mail merge.
objWord.MailMerge.Execute
' Close BrksDednsEM.doc
Documents("BrksDednsEM.doc").Close (Word.WdSaveOptions.wdDoNotSaveChanges)
' Close Reservations.mdb

End Function

...the data source for this mail merge is a query and I have inserted the criteria ([forms]![Reservations2]![Booking Reference]) into the query to force it to return only the data for the record currently open in the form.

And indeed, when I view the query in Datasheet View, it does indeed, return only the data from that current record on the form. Below is a screenprint of that:



However, when I click on the command button on my form, to perform the mail merge, it brings up a prompt box asking "Enter Parameter Value" for [forms]![Reservations2]![Booking Reference].

When I click OK, it says that it couldn't merge the main document with the data source as the fields were empty...even though I know the query (in Datasheet view) is showing the data I need to merge.

If I enter the booking reference and click OK it then prompts me for the booking reference again; once I enter that and click OK, it performs the mail merge and I get the mail merged document for that specific record...success, in part.

However, I really don't want to have to enter the reference once (or even twice) for this process to work. In addition, this "workaround" is flawed because as soon as I click the command button, the module window opens and you can't see the booking reference field on the form.

I hope you can help please ?

[June7]

I might be able to help if I could work with the files but I would be working in 2007/2010 versions.

What do you mean by 'the module window opens'? The VBA editor?

[bellevue]

Hello June7

Thanks for that; I'm not sure I understand how working with the files in 2007/10 would work. If you found a solution, how that would be translated to work with 2000. In addition, the database is quite large and contains confidential info so couldn't be uploaded.

When I refer to the module window opening; I've attached a screenprint of what comes up. Please be aware that my knowledge of the correct terminology may not be 100% accurate but I am sure you can afford me some understanding because of that.

Best wishes

[June7]

Yes, that is the VBA editor that opens.

I could test and debug in 2007/2010 and my findings might be compatible with 2000.

Could make a copy, eliminate the confidential info (phone numbers, SSNs, emails), reduce size by elimination of records, delete objects not relevant to issue, run Compact & Repair, zip. If still large can upload to fileshare site such as box.com and post link to file.

If you can't find a fix for the mail merge, might be necessary to eliminate the Word component.

[bellevue]

Thanks June7, at least I know now that that is the VBA Editor !

I've stripped the database down to the bare bones and have attached it for you; in taking out unnecessary tables, queries etc I hope I've left what's needed to run it !

Of course, there's no mail merge template included so the code may need changing.

Thanks in advance.

RESERVATIONS.zip

[June7]

Also need your Word document.

[bellevue]

Attached.

BrksDednsEM.doc