Access Mail Merge VBA Help

[shuttlelaunch]

Hello All,

I am trying to make a command button in Access that will fill a Word Mail Merge with information from that record set. The code I have so far is:

Set objWord = GetObject("C:\My documents\Mail Merge.docx", "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:="C:\my documents\Names Database.accdb", _
LinkToSource:=True, _
Connection:="Members"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintPreview

The code works so far, but imports all the entries in the database, not the specific recordset being viewed.

Thanks!

 

[dhookom]

Maybe change to a query rather than the table.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

The way I do it is use a query and spit it out to an XLS file using the transfer method.

Then use the XLS file as the data source to the mailmerge.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[shuttlelaunch]

How would you use a query? Would kind of code would I have to add to the button to run a query for the specific record being viewed?

 

[dhookom]

Where ever you had a link to your Access table, use a query. I would probably use a little code to change the SQL property. I don't do this so you would need to do some digging.

If you get an error message regarding pulling from the query, you might need the code to update the SQL property.

Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

If I am using a from the switchboard, how do you make a query for the single record being viewed? I am not using a link to an access table.

 

[dhookom]

Did you search the web for solutions? How about Use a table or query as a mail-merge data source.

Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

I have searched the web. There is nothing for how to create a mailmerge from the single record being viewed.

 

[dhookom]

What is the table name the merge is currently pulling records from?

Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

The merge pulls all the records from the table "TAD_Database

 

[dhookom]

Define "the specific recordset being viewed"

Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

When are viewing the database through a Form, the specific record you are looking at, not the whole table.

If I wanted to print a report with just that record, I use
the code:

DoCmd.OpenReport "LETTER_COMRATS", acViewPreview, , "ID=" & Me.ID"

Is there a mail merge equivalent for just the single record being viewed, not the whole table?

 

[dhookom]

I would use the fChangeSQL() found in the Query forum FAQs to change the SQL property of a saved query.

dim strOldSQL as String
strOldSQL = fChangeSQL("SELECT * FROM TAD_Database WHERE ID = " & Me.ID,"YourQueryName")

Then base you merge on YourQueryName.


Duane
Hook'D on Access
MS Access MVP

 

[shuttlelaunch]

Would all of that go into the Command Button On Click event?

 

[dhookom]

I expect it would go in the on click. Do you have existing code?

Duane
Hook'D on Access
MS Access MVP