• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

Trouble creating Word 2003 Macro for Mail Merge

I snagged the following macro from an Internet source and have it in my Word document for mail merge:

Option Explicit

Private Sub Document_Open()
    DoMailMerge
End Sub

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

Open in new window


I had this working not long ago, but putting it in a new merge doc generates the error "Requested object is not available" as shown in the attached image. Apparently, I just got lucky with how I inserted the macro before because I cannot duplicate the working state.

What am I doing wrong?

Macro Error
0
jmarkfoley
Asked:
jmarkfoley
  • 9
  • 8
1 Solution
 
GrahamSkanCommented:
Has the Main document lost its status as a mail merge document? Try setting it up again.
0
 
GrahamSkanCommented:
By 'setting up' I mean going through the steps of setting the merge document type and the datasource
0
 
jmarkfoleyAuthor Commented:
GrahamSkan: > Has the Main document lost its status as a mail merge document? Try setting it up again.

If you look at the most recent post I made to my other question: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_27840463.html, I believe I do finally have the merge doc set up correctly. I can now use the mail merge tool bar to manually merge the doc.

However, this macro still does not work and still gives me this "Requested object is not available" message. I've tried relocating the Public code to one of the Normal modules (since that was in the original instruction I found: http://www.vbaexpress.com/kb/getarticle.php?kb_id=122) but that didn't help.

What am I doing wrong?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
GrahamSkanCommented:
Just as a check, can you try this code, and see what gets printed in the immediate window

Sub MergeDocType()
    Dim ty As String
    Select Case ActiveDocument.MailMerge.MainDocumentType
        Case wdCatalog '3
            ty = "Catalog"
        Case wdDirectory '3
            ty = Directory
        Case wdEMail '4
            ty = "E-mail message"
        Case wdEnvelopes '2
            ty = "Envelope"
        Case wdFax '5
            ty = "Fax"
        Case wdFormLetters '0
            ty = "Form letter"
        Case wdMailingLabels '1
            ty = "Mailing label"
        Case wdNotAMergeDocument '-1
            ty = "Not a merge document"
    End Select
    Debug.Print ty
End Sub

Open in new window

0
 
jmarkfoleyAuthor Commented:
Results: nothing prints. I'm probably not doing something right. I tried just using your code, only (no Document_Open() sub), in project "Aetna Medicare"/This Document; and in Normal/This Document. Nothing, not even a message asking about enabling macros. I then added the Document_Open() sub:

Private Sub Document_Open()
'DoMailMerge
MergeDocType
End Sub

If I put this and your function together in either  "Aetna Medicare" /This Document, or the Normal/This Document, I get the 'enable macro' message, but nothing prints. If I put the Document_Open() sub in Normal/This Document and leave your function in "Aetna Medicare"/This Document I get the 'enable macro' message, and then a "Compile error: Sub or Function not defined" with the "MergeDocType" line in Document_Open() highlighted. This indicates to me that they have to be in the same module (why? I added "Public" to your function) and that when they are in the same module it does run, but nothing prints. Where is the output supposed to show up anyway?

btw I put quotes around your Directory string in: ty = Directory
0
 
GrahamSkanCommented:
Are you saying that nothing appears in the immediate window? Perhaps I should have specified a message box:
Sub MergeDocType()
    Dim ty As String
    Select Case ActiveDocument.MailMerge.MainDocumentType
        Case wdCatalog '3
            ty = "Catalog"
        Case wdDirectory '3
            ty = Directory
        Case wdEMail '4
            ty = "E-mail message"
        Case wdEnvelopes '2
            ty = "Envelope"
        Case wdFax '5
            ty = "Fax"
        Case wdFormLetters '0
            ty = "Form letter"
        Case wdMailingLabels '1
            ty = "Mailing label"
        Case wdNotAMergeDocument '-1
            ty = "Not a merge document"
    End Select
    MsgBox ty
End Sub

Open in new window

0
 
jmarkfoleyAuthor Commented:
OK, the msgBox did the trick. The output is "Form letter"
0
 
GrahamSkanCommented:
Macros that work together should be in the same Project. They need to be Public, but that is the default for items in a Module.

To be sure that the code finds the Main document, try this combined code:
Public Sub DoMailMerge()
    Dim docMain As Document
    Set docMain = ActiveDocument
     'Do mailmerge
    With docMain.MailMerge
        MsgBox "Merging document: " & docMain.Name & " which is type '" & MergeDocType(docMain) & "'"
        .Destination = wdSendToNewDocument
        .Execute
    End With
     'Close mailmerge document
    docMain.Close wdDoNotSaveChanges
End Sub

Function MergeDocType(doc As Document) As String
    Dim ty As String
    Select Case doc.MailMerge.MainDocumentType
        Case wdCatalog '3
            ty = "Catalog"
        Case wdDirectory '3
            ty = Directory
        Case wdEMail '4
            ty = "E-mail message"
        Case wdEnvelopes '2
            ty = "Envelope"
        Case wdFax '5
            ty = "Fax"
        Case wdFormLetters '0
            ty = "Form letter"
        Case wdMailingLabels '1
            ty = "Mailing label"
        Case wdNotAMergeDocument '-1
            ty = "Not a merge document"
    End Select
   MergeDocType = ty
End Function

Open in new window

0
 
jmarkfoleyAuthor Commented:
So Far so good (you still need quotes around "Directory" in line 20)

Results of code in response ID 38355940
Encouraged by this success, I pasted my original macro back in (see original posting), in exactly the same module ... and it worked!!!! I have no clue why, or what's different, or if it will stay working.My only thought is that I didn't really have how to specify the source data file properly at first.

But I'm not quite out of the woods yet. When I open  this document from a browser on the same machine, I get: the error: "Runtime error '4605': This method or property is not available because this document is in another application."

Is this error within your realm of expertise or should I open another question?
0
 
GrahamSkanCommented:
I think that the original problem was with picking up the right document to use.

It is unusual to have code in the document itself. It normally resides in the template. However a mail merge document is used in a template-like way, so I can see the point.

If the code is in the main document itself, it would be better to use ThisDocument to identify it.
Public Sub DoMailMerge()
    Dim docMain As Document
    Set docMain = ThisDocument
     'Do mailmerge
   '...

Open in new window


I'm afraid that I have no experience with running from a browser. Because the problem is so different, I think that a new question is called for, anyway.
0
 
jmarkfoleyAuthor Commented:
OK, I've posted a new question for the web issue: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_27853018.html

one last thing before I close this question. You wrote:

> It is unusual to have code in the document itself. It normally resides in the template. However a mail merge document is used in a template-like way, so I can see the point.

I've not done ANY of these before, so I'm not sure where I would put the code otherwise. In the VB editor I have a ThisDocument under the NORMAL project, along with a couple of Modules and a NewMacro module. These all seem to be empty. I do recall when I put this code somewhere in NORMAL, that I got the "enable/disable macros" message everytime I opened any document. If I recall correctly, that is why I moved the code into the "document name" project. Advice?

I added your suggested code to the macro, but didn't notice anything different. Was I supposed to?
0
 
GrahamSkanCommented:
Whenever you open a document in Word, there will be a VBA project for it. There will also be one for its Template. Code can be in either, but the code in the template will be usable from the document as long as the template is still the Attached Template, and can still be found. The template code can also be edited while any of its dependent documents is open.

A new document made from a template by File/New will not itself automatically have a copy of the template code.

A Mail merge main document acts a bit like a template in that copies of itself are made by the merge process while the document itself is unchanged.

Code in the Normal template is available to every document, but as you have discovered, if you use the Document_Open event in that template, it will run whenever any document is opened. Therefore you did right by putting it in the main document.

Note that another, perhaps more manageable, approach would be to put a macro into the Normal template. The macro could open the main document and run the merge. However your requirement to run from a browser might make that suggestion irrelevant.
0
 
jmarkfoleyAuthor Commented:
Well, I thought I at least had this working from Word locally, but no, it's not. I'll have to say this is one of the most frustrating problems I've dealt with. I've got over 20 years of programming experience and probably 5+ in VBA in Access, but this one is driving me banana's!

I tried opening this document with Word 2010 (I was using word 2003 up to this point). The document opened. I did not get initially get the "enable macros" warning, but I did get the "Data from your database will be placed in your document"  message to which I clicked 'yes'.

I then got a banner on a yellow background asking me if I wanted to enable macros. I clicked 'enable', then got the "Data from your database ..." message again. I clicked 'yes' again and it does the merge, but gives me the runtime error 5941 as shown below. This has to do with closing and not saving the original document. It does the merge, but does not close the original document (as expected from the error).

Interestingly, on subsequent opens of the document, it does not as about enabling macros and goes ahead and runs the macro (after asking the "Data from your database ..." message, of course). Perhaps this is a new feature of Word 2010 that once you enable macros it doesn't keep asking?

word 2010 macro error
Any idea how to fix this? Meanwhile, I'll try playing around with a few things.
0
 
GrahamSkanCommented:
There might be some sort of record kept about which document's macros have already been given permission to run.

The opening security message can be removed:
http://support.microsoft.com/?kbid=825765.

I can't explain the error message, but try
docMain.Close wdDoNotSaveChanges
0
 
jmarkfoleyAuthor Commented:
your docMain.Close did the trick. Must be some kind of difference between Office 2003 and 2010.

I've figured out the macro security message thing. The first time you open a document with a macro you get that yellow "enable" banner. If you enable, it ask if you want to make this a "trusted document" upon exit. If you answer yes, you don't get the macro warning message any more. If you modify the macro, you get the message again. This is a GREAT improvement over the Word 2003 method of either having to lower overall Office security, or obtain a certificate.

I'm going to hold off implementing the solution suggested in your link to the "Opening this will run the following SQL command" problem. I have a few more kinks to work out first. For example, if you answer "no" to the SQL command question, it still tries to run the maco and blows up on the '.Destination = wdSendToNewDocument' line. That kind of makes sense so I need to figure out a way to NOT run the macro and NOT merge (so I can edit the underlying document). So far, holding down the shift key and double-clicking the document seems to hang the whole workstation.

BUT ... I think this question has been dealt with, so I will post other questions for these other issues. Thanks for all the help. I'll leave this open another day in case you have any parting thoughts/advice.
0
 
GrahamSkanCommented:
That's good news. Thanks for keeping us updated
0
 
jmarkfoleyAuthor Commented:
Thanks for hanging in there.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now