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

 

[tomcatonnet99]

What is the code you're using for that?

No code for it Domenic, just manually inserted fields whilst setting up the document for mail merge, more importantly all the other fields work just fine although I manually double spaced each letter in all fields to fit boxes in a form (stationery)

The only code running off the mergemail document itself (yes vba word) is the opendatasource..... As I earlier had problems with data source being volatile...when people at work opened file wiily bill without anything to merge, that code followed by the one above to preview as also your user option msg for .sentoprinter & .execute pause:=false OR manual merge&print

At present followhyperlink is opening word doc from excel data source file, from thereon word VBA takes over to complete merge
Your earlier code runs perfectly well too if preview code is added to it
Just that there were too many diff docs to repeat that code for (was a bit time consuming for me to make changes there)

 

[Macropod]

No code for it Domenic, just manually inserted fields whilst setting up the document for mail merge

I'm not Domenic!

The 'error bookmark not found!' message typically means the field code is broken and, since you say you input it manually, that seems likely to be the case here. Try adding it via the 'Insert Merge Field' dropdown.

 

[tomcatonnet99]

I'm not Domenic!

The 'error bookmark not found!' message typically means the field code is broken and, since you say you input it manually, that seems likely to be the case here. Try adding it via the 'Insert Merge Field' dropdown.

Apologies for getting your name wrong..

To clarify, fields have been inserted using insert field, by manually I meant not programmatically.

Just wondering if initially setting up the entire mail merge non programmatically & then using VBA, would create a conflict??
Also if so, how would one insert fields without setting the data source for merge... So I take it, it isn't incorrect to do so??

Regardless the issue of double space in one field, not allowing for it to update is baffling! Any way to achieve that??

 

[Macropod]

Inserting mergefields via the 'Insert Merge Field' dropdown rather than programmatically wouldn't change the outcome. Given that's what you seem to be doing, the problem may be elsewhere in whatever other field coding you're using. Without seeing the field coding you're using, I can't really say anything definitive about either that or the double-space problem you're having.

And, yes, it's quite easy to create mergefields in a document that isn't connected to a data source. For example, in any Word document:
• press Ctrl-F9 to create an empty pair of field braces (i.e. { }).
• between the field braces, insert 'MERGEFIELD Value', so you end up with {MERGEFIELD Value}
• select the field and press F9
Done. You should now see a mergefield that looks like «Value».
Programmatically, it's as easy as:

With ActiveDocument
  .Fields.Add .Characters.Last, wdFieldEmpty, "MERGEFIELD Value", False
End With

For more details, see my Mailmerge Tips and Tricks threads at:
http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
or:
http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

 

[tomcatonnet99]

Inserting mergefields via the 'Insert Merge Field' dropdown rather than programmatically wouldn't change the outcome. Given that's what you seem to be doing, the problem may be elsewhere in whatever other field coding you're using. Without seeing the field coding you're using, I can't really say anything definitive about either that or the double-space problem you're having.

And, yes, it's quite easy to create mergefields in a document that isn't connected to a data source. For example, in any Word document:
• press Ctrl-F9 to create an empty pair of field braces (i.e. { }).
• between the field braces, insert 'MERGEFIELD Value', so you end up with {MERGEFIELD Value}
• select the field and press F9
Done. You should now see a mergefield that looks like «Value».
Programmatically, it's as easy as:

With ActiveDocument
  .Fields.Add .Characters.Last, wdFieldEmpty, "MERGEFIELD Value", False
End With

For more details, see my Mailmerge Tips and Tricks threads at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge

As stated earlier, no vba code has been used for field codes, besides all codes update just fine, but one which is alphanumeric, will re insert this code again at work tomorrow and come back with results.. I guess using the kern button in font dialog might do it.
Also is it ok to use VBA code on a pre setup (mail merge data source I.e) document corrupt it in any way? Or will VBA just overwrite data source..???
Many thanks
Regds
Tom

 

[Macropod]

Also is it ok to use VBA code on a pre setup (mail merge data source I.e) document corrupt it in any way? Or will VBA just overwrite data source..???

Whether you use the code on a document connected to a different data source, or non at all, won't corrupt the document. the most you might need to do is to re-connect to the data source. Why do you think the VBA might overwrite a data source? Does your code actually create the data source?

 

[tomcatonnet99]

Whether you use the code on a document connected to a different data source, or non at all, won't corrupt the document. the most you might need to do is to re-connect to the data source. Why do you think the VBA might overwrite a data source? Does your code actually create the data source?

I meant opendatasource.... (as in the link to it)

No my VBA in word does not create or write back to the opened data source.

Another good thing i found out was by having VBA in the mergemail doc open the data source and do the merge, was it seems to effectively set focus on ms word application and allowed for a flawless flow of sendkeys!!�� - as I'm printing on a3 sized pre printed stationery and printer at work is set to default to duplex print, VBA has no control over,... And printing 2 sheets of the same form by any other method just jumbled the output due to the way printer feeds A3 sheets.. (e.g printing even or odd pages both didn't work) . Albeit I've left an option for auto print as well as manual print settings..
(pls excuse any typo errors using my phone...)

 

[tomcatonnet99]

CASE SOLVED AND CLOSED : (using my own concept and design)

For anyone who could make use of this I've included the code with explanation:


the following opens a Word document (intended for mailmerge ) using VBA Excel and then the WORD document has its own VBA for mailmerge

Private Sub Document_Open()

ThisDocument.Activate

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters


ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\path\whateverLetters.xls", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\path\whateverLetters.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Loc" _
, SQLStatement:="SELECT * FROM `Letters`WHERE `OS`='NC' And `Printed`='N' And `Whateverelse`='whatever`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess





With ActiveDocument.MailMerge.DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With




ActiveDocument.ActiveWindow.View.ShowFieldCodes = False
Set myMerge = ActiveDocument.MailMerge
If myMerge.State = wdMainAndSourceAndHeader Or _
myMerge.State = wdMainAndDataSource Then
myMerge.ViewMailMergeFieldCodes = False
End If




'just so the cursor is well below all data... (incase of sendkeys errors making any changes to data ) although having the mailmerge code in here has well and truly set the focus to this application!
On Error Resume Next
Selection.EndKey Unit:=wdStory


msg1 = MsgBox("Do you wish for Mailmerged letter/s to be autoprinted? (click 'Yes')" & vbNewLine & "OR" & vbNewLine & "Do you wish to print by manually configured settings? (click 'No')", vbYesNo)

If msg1 = vbYes Then



' these are printer specific --------------------------------------------------------------------------------------------------- and have been used to prevent duplex printing!!!!!!!!!!!!!!!!!!!!!!!
DoEvents
WordBasic.SendKeys "%M%", True
WordBasic.SendKeys "F", True
WordBasic.SendKeys "P", True
WordBasic.SendKeys "{Enter}"
WordBasic.SendKeys "{TAB 9}"
WordBasic.SendKeys "{Enter}"
WordBasic.SendKeys "{TAB 13}"
WordBasic.SendKeys "{UP}"
WordBasic.SendKeys "{TAB 7}"
WordBasic.SendKeys "{Enter}"
WordBasic.SendKeys "{TAB 12}"
WordBasic.SendKeys "{Enter}"
DoEvents


MsgBox ("Merged Letter/s sent to printer")

'OR '--------------------------------------------------------------------------------------------------for straightforward printing..................
With Options
On Error Resume Next
.Application.ActivePrinter = "\\aprdprn02\FollowYou-AKL"
.PrintBackground = False
.PrintReverse = False
End With



With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

.Execute Pause:=False




Else

MsgBox ("Mergemail file is ready to be set up for manual printing")


End If


End Sub


--------------------------------------- &&&&&&&&&&&&&&&&& ------------------------------------------------------- in Excel (VBA)
Private Sub CommandButton21_Click()



formtoopen = "C:\path\whateverwordletter.docm"






Set wordapp = CreateObject("Word.Application")
wordapp.Visible = True
wordapp.Activate ' useful when using 'sendkeys'.....
wordapp.Documents.Open (formtoopen)



Set wordapp = Nothing




----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

may I take the opportunity to thank Domenic and Paul who helped me ....