Excel Automated Mailmerge with an Embedded OLE Word Object
 

Hello Chaps,

Am hoping that some of your genius might rub off on me, but Ive a
doozer here that am just bashing my head against the wall with.

Basically, am attempting to Automate a mailmerge within Excel, using
an embedded Word Document. The workbook just needs a sheet named
'Mailmerge' to use the below code.

All seemed to be working fine, upto the point where I want to trigger
some VB with the Private Sub Document_Open() event that sites within
the embedded word doc.

You would think I could automate the mailmerge by simply connecting to
the saved excel file (by default I save it to C:\) but... for some
reason the Private Sub Document_Open() is not being triggered. The
code within this as follows:

(btw: I can exicute each line in the immediate window and the merge
happens as desired!)

Private Sub Document_Open()

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.Destination = wdSendToNewDocument

ActiveDocument.MailMerge.OpenDataSource Name:="C:
\FeesDecMailfile.xls", _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Data Source=C:\FeesDecMailfile.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet
OLEDB:Engine Type=35;J" _
, SQLStatement:="SELECT * FROM `Mailmerge$`",
SQLStatement1:="", SubType _
:=wdMergeSubTypeAccess

With ActiveDocument.MailMerge
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With


A simple debug excercise of simply calling a msgbox on the first line
results in nothing being displayed!

All I can assume is that there are no events being triggered when an
OLE doc is opened!! but surely not?!? Ive scoured the net but to no
avail.

Any pointers? :\


Help me Obi Wan...

regards,

David