Mailmerge from a button in excel

[stevew]

I am trying to create a mailmerge in excel with the click of a button. When
the button is clicked it opens word sucessfully.

Sub OpenWord()
Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.application")
Set wdDoc = wdApp.Documents.Open _
(Filename:="C:\Documents and Settings\steve\My
Documents\MyTestDoc.doc")
wdDoc.Close savechanges:=False
Set wdDoc = Nothing
wdApp.Quit
Set wdApp = Nothing
End Sub

The code I have placed in Word gives me an error (Ambiguous selection) at
the second line .Destination=wdsendToPrinter.

Private Sub Document_Open()

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

End Sub

I'm making the assumption (new to VBA in excel) that the document is not
active but the spreadsheet is and therefore will not print. Not sure how to
solve this.

If anyone can give me any assistance it would be very much appreciated.

Steve Walker

 

[Guest]

Shouldn't you do this from Word?

 

[stevew]

Trying not to. I want to mail letters for the data in excel but use excel as
the main application.

Steve

 

[Dave Peterson]

It looks like you're not using a reference (tools|references) to your version of
MSWord. This is called late binding. (When you use the reference, it's called
early binding.)

If you don't have a reference to MSWord, then excel doesn't have any idea what
those MSWord VBA constants are.

You could try using the numeric value instead:

?wdSendToPrinter
1

?wdDefaultFirstRecord
1

?wdDefaultLastRecord
-16

So this can become:

Private Sub Document_Open()

With ActiveDocument.mailmerge
.Destination = 1 'wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1 'wdDefaultFirstRecord
.LastRecord = -16 'wdDefaultLastRecord
End With
.Execute Pause:=False
End With

End Sub

I opened MSWord. I opened its VBE. I hit ctrl-g to see its immediate window
and just typed:
?wdSendToPrinter

to see the value.

One nice thing about using early binding is that if you declare all your
variables nicely, you'll get VBA's intellisense that helps you complete the
property or method or constant.

 

[stevew]

Thanks Dave

Used the Intermediate Window and got a 1 as the printer. I've also put
references in for both Excel and Word but unfortunately this doesn't work,
still get the same error. Excel will open the document but for some reason
it won't print. Will try and see if it's the source.

Appreciate your response to my last message.

Steve W.

 

[Dave Peterson]

You may want to post your code.

In fact, this is one of those few posts where I'd suggest you cross
post--include both this newsgroup and a word newsgroup. (Not two messages--just
two groups in one message.)

Maybe one of the Word users will see the problem right away.

 

[stevew]

Cheers Dave thanks for your assistance

Steve Walker

 

[stevew]

Dave Peterson

I cross posted as you suggested and Doug Robbins replied. Similar code but
found out that there is a security issue in word that stops SQL statements
http://support.microsoft.com/?kbid=825765 (from Graham Mayor) from being
carried out. Once done works like a dream, bit dodgie though in terms of
security.

Thanks for your help

Steve Walker

 

[Dave Peterson]

Glad you have a solution--no matter how dodgie.

Dave Peterson

I cross posted as you suggested and Doug Robbins replied. Similar code but
found out that there is a security issue in word that stops SQL statements
http://support.microsoft.com/?kbid=825765 (from Graham Mayor) from being
carried out. Once done works like a dream, bit dodgie though in terms of
security.

Thanks for your help

Steve Walker