Mailmerge to Word 2007 from Access

Roxar
Roxar used Ask the Experts™
on
Hi,

I'm currently working on a project where i need to automate opening of documents, and mailmerge from an Access database utilizing VBA. The opening of documents part is working as intended, but i'm getting stonewalled when it comes to automatic mailmerge.

I've tried diffrent solutions now, but nothing seems to work for me.

The last thing i tried was to add a module to NORMAL where the following code where placed:

Public Sub DoMailMerge()
    Dim DocName$
    DocName = ActiveDocument.Name
     'Do mailmerge
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
     'Close mailmerge document
    Windows(DocName).Close wdDoNotSaveChanges
End Sub

The above code seems to work with Word 2000, but not with 2007. I've even tried to add:

ActiveDocument.MailMerge.Destination = wdSendToNewDocument

Instead of:
.destination = wdSendToNewDocument


My experience with VBA is limited so please keep it simple.

Thank you in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GrahamSkanRetired
Top Expert 2012

Commented:
As far as I can see that code should work with 2007.

What happens?

Author

Commented:
I get:

"Run-time error '5852':
The object is not available"

And when running debug it marks out ActiveDocument.MailMerge.Destination = wdSendToNewDocument

This only happens when i open the document from Access, it poses no problem when it's opened manually
GrahamSkanRetired
Top Expert 2012

Commented:
Are you sure that the active document is a mailmerge document. Click on Start Mail Merge on the Mailings Tab to see which type of document it is.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
It's supposed to merge from a .mdb in the same folder, and it is set to do that, and it did before I added the module. If i choose to end the debug however, there's no mailmerge active in the document.
GrahamSkanRetired
Top Expert 2012

Commented:
It may be cause by a security patch. Try this registry edit from microsoft:

http://support.microsoft.com/?kbid=825765

Author

Commented:
That seems to take care of that problem, however a new one poped up.

It now gives me an error on another line with the message:

"Run-time error '5941':"

And it marks out the line: Windows(DocName).Close wdDoNotSaveChanges   in the debug
GrahamSkanRetired
Top Expert 2012

Commented:
What is the text of the error message, please?

Author

Commented:
The requested member of the collection does not exist.
GrahamSkanRetired
Top Expert 2012

Commented:
Try using document object rather than the name.


Public Sub DoMailMerge()
    Dim DocA As Document
    Set DocA = ActiveDocument
     'Do mailmerge
    With DocA .MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
     'Close mailmerge main document
    DocA.Close wdDoNotSaveChanges
End Sub

Open in new window

Author

Commented:
That did solve that problem.

Now my problem is that it opens a document wich all pages are shown.

The reason i need this is that i'm making a simple database contaning user information in the domain. This info is supposed to printed and sent to the individual user. If all the pages are shown, then there would be the problem of printing a spesific page (for example: The last row in the database, or the third (depends on the situation))

If the explanation is confusing or if you have question about something please do ask, i'm near desperate now.
GrahamSkanRetired
Top Expert 2012

Commented:
Yes, that is what is supposed to happen. It should produce a section of at least one page for each record in the recordset.

If you want only one record you would have to limit it in some way. You could add a WHERE close to the query.

With DocA.MailMerge.DataSource
    .QueryString = .QueryString & " WHERE `Name` = 'Jones'"
End With

Don't save the main document or you will accumulate WHERE clauses. Note that Word appears to need the "`" character as opposed to the straight apostrophe "'" in some places, for example around data field names.

Author

Commented:
As I said in my main post i'm inexperienced when it comes to VB and VBA, and I'm sorry for producing even more work.

I'll try to explain what i need as good as I can.

As said, i've created a .mdb containing user information such as username, different app usernames, email adress, department etc etc.

When we have created one or more users we print out the info through word and send the info to the head of the department. When using a normal mailmerge method you can only see one page per row, and you have to browse until you find the page you want to print out. I do not want to limit the number of rows that can be displayed, just the number of imported rows that are displayed at once.

I'm sorry for my messy explanation
GrahamSkanRetired
Top Expert 2012

Commented:
I'm not sure that I understand. Where do you view the rows?
GrahamSkanRetired
Top Expert 2012

Commented:
Somewhere and somehow there has to be a choice of which records are to be printed.

You can do that without code by using the 'Select recipients' button. Otherwise there has to be some criterion that can be tested programatically if you need it to be fully automated.

Author

Commented:
I'm sorry I haven't been able to reply in the weekend.

Ok, i'll try to clarify a bit

If you look at the first image you will se what i want (Had to remove most of the text due to very strict privacy policies (I'm working at a hospital)) and the second picture is what i get. The second image illustrate that every record in the database is shown at the same time, i only want to see one record at the time.

If you look at the first picture, in the top part, there is a "browsing" function wich you can use to go through the records.

I hope that makes everything a bit clearer.
Word-Example-1.JPG
Word-example-2.jpg
GrahamSkanRetired
Top Expert 2012

Commented:
The button "Rediger motterliske" seems to be the Icelandic version of "Edit Recipient List". If you have chosen a list in "Select Recipients" or "Velg mottakere", then you should be able to choose particular recipients there.
Commented:
Question PAQ'd, 250 points not refunded, and stored in the solution database.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial