Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
Shouldn't you do this from Word?
"stevew" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
Trying not to. I want to mail letters for the data in excel but use excel as
the main application. Steve "Kassie" wrote in message ... Shouldn't you do this from Word? "stevew" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
Cheers Dave thanks for your assistance
Steve Walker "Dave Peterson" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mailmerge from a button in excel
Glad you have a solution--no matter how dodgie.
stevew wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I perform mailmerge in Excel | Excel Discussion (Misc queries) | |||
Excel Word - Mailmerge | Excel Discussion (Misc queries) | |||
can i use mailmerge to in excel like you would in word? | Excel Discussion (Misc queries) | |||
Mailmerge in Excel? | Excel Programming | |||
VBA mailmerge from Excel | Excel Programming |