Using Excel VBA to open a Word document and enable Word macros

[Strider71]

I have an Excel 2007 macro that opens up a Word document and runs a Word macro that does a mail merge. It works great in Excel 2007, but I just switched to Excel 2010 (and Word 2010 of course). The Excel code still opens the Word file, but the macros are disabled when it opens Word so it doesn't run the mail merge macro.
Even if I enable Word macros in the Trust Center Settings (I know that's not recommended) it still doesn't allow me to run the Word macro.
If I open the Word file on my own and enable the macro, it works just fine.

The Excel code to Open Word and run the macro is:

Sub Run_Mail_Merge()
Dim wordApp As Object
    Set wordApp = CreateObject("Word.Application")
    wordApp.Documents.Open Filename:="D:\Form Letter.docm"
    wordApp.Visible = True
    wordApp.Run "!newmacros.MailMergeMacro"
End Sub

My Word code (which won't run if the Excel macro opens the file), is:

Sub MailMergeMacro()
If ActiveDocument.MailMerge.State = wdMainAndDataSource Then
    ActiveDocument.MailMerge.Execute
End If
ActiveDocument.SaveAs FileName:="Completed Letter.doc", 
   FileFormat:=wdFormatDocument, LockComments:=False, Password:="",AddToRecentFiles:= True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
End Sub

Any insight would be greatly appreciated!
Thanks.
-Strider71

 

[Domenic]

My code should open the merge mail document, and then ask whether you want to open the source data. If you answer Yes, it will load the source data, and you'll be able to preview the results. If you answer No, the source data isn't loaded. Isn't this what happens when you run my code?

 

[tomcatonnet99]

No sorry, it does exactly what my code does, no preview
No doubt both codes pick up the record/s for mail merge (display the correct count with prev/next record buttons) but wont allow preview! Only possible after .destination event Only!

 

[Domenic]

Which Excel version are you using?

 

[tomcatonnet99]

Tested it in Excel 2003 and at work on 2013

 

[tomcatonnet99]

It appears when mergefile is executed via VBA, there is no preview as it expects a fully automated merge through to print or new document...
Unless ofcourse you are able to use prev/next record in word mergemail file on opening via vba excel??

 

[Macropod]

tomcatonnet99: When you execute a mailmerge via VBA, you don't get any of the normal UI access/prompts or preview. If you need that functionality, you need to roll your own. Providing your own SQL-type prompt is quite easy; previews are something else. One way of generating previews (of sorts) would be to send the merge for each record to a separate document (not difficult) and let the user see those before deciding what to do with them (e.g. save/print, add to an aggregation document, discard). Previewing all records, though, would be a PITA in some cases, in which case, I'd be questioning the reason for wanting previews.

 

[Domenic]

I've tested my macro in Excel 2010 and 2013. In each version, it behaves as expected. It opens the merge document, and asks whether to open the data source. If Yes, it opens the data source and the preview results are available. You must have changed my code somehow. After taking another look at your recent code, it looks like you may have replaced...

SQLStatement:="SELECT * FROM `Sheet1$`"

with

SQLStatement:="SELECT * FROM `Sheet1$` WHERE `Printed`='N'"

 

[tomcatonnet99]

Got the previous/next preview working

ActiveDocument.ActiveWindow.View.ShowFieldCodes = False

Set myMerge = ActiveDocument.MailMerge

If myMerge.State = wdMainAndSourceAndHeader Or _

myMerge.State = wdMainAndDataSource Then

myMerge.ViewMailMergeFieldCodes = False

End If

 

[tomcatonnet99]

Now with one mergefield (alphanumeric) I need to insert double space, any idea why it results in error bookmark not found! On mail merge
If I insert double spaces it wont update at times

 

[Macropod]

What is the code you're using for that?