Word Merge mail from Excel

[davefish]

I am trying to create a word document pulling data from Excel and I am having some difficulty. I have used the following script to launch Word OK, but cannot get to my template file.

Private Sub Create_Bom_Click()
Dim myappid, ReturnValue
myappid = Shell("C:\Program Files\Microsoft Office2K\Office\winword.exe", 1)

So I turned to:-

Dim wdApp As Word.ApplicationwdApp.Documents.Open FileName:="C:\Temp\MyDoc.doc"wdApp.Visible = TruewdApp.WindowState = wdWindowStateMaximize

This created an error message of "type not defined" and stopped the whole works!

Assuming this is me and it easy to fix, can anyone tell me if I can also control Word's merge mail from Excel, or do I have to carry this out in Word and pull the data?

Hope you can help a frustrated user

DaveFish

 

[Chattin]

Not your answer but hopefully of some use to you

http://j-walk.com/ss/excel/tips/tip92.htm

HTH

 

[davefish]

Thanks HTH, I'll try this approach.

DaveFish

 

[Chattin]

For info by HTH I meant Hope This Helps ;-)

 

[davefish]

Yup, the solution works, but didn't give me the flexibility of Word. I haven't discarded it yet, but thanks for the lead.

DaveFish

 

[TonyJollans]

Hi davefish,

To clear the "type not defined" message you must set a referenece to Word. Under Tools > References, scroll down and select Microsoft Word x.x Object Library (where x.x is 8.0 for Word 97, 9.0 for Word 2000, etc.).

You will also need to add a line to create an instance of Word:

Dim wdApp As Word.Application

Set wdApp = CreateObject("Word.Application")

wdApp.Documents.Open FileName:="C:\Temp\MyDoc.doc"
wdApp.Visible = True
wdApp.WindowState = wdWindowStateMaximize

I haven't done it with a Mail Merge, but anything you can do in Word you should be able to drive from Excel, so what you ask should be possible. I'm sure there are other people here with specific experience of this but if not, come back with any problems and I'll look into them.

Enjoy,
Tony

 

[davefish]

Hi Tony,

Thanks for the advice, it certainly worked. I thought about the mail merge, and as I want to have a definite Word template, will try and build the vba code into the .doc file. If this works then no problem, but if I have to control it from Excel I may come back to you.

All the best

Dave