Create Excel log and then Mail Merge in Word

[thesnoopster2]

I have a project I am working on where I need to create a log to track production and I want to be able to create form letters based on the information on the log.

On the log, I have info such as Customer name, address, type of product, ect. I have about 10 different types of letters based on the customer info so I am am planning to have a drop down to select the right type of letter for each situation. Based on the entries in the log, I want to hit a command button to open up a Word form letter, and enter the info from Excel into the letter.

I'm assuming I have to write a macro and such into Excel, I'm just trying to figure out how to make Excel play nice with Word. I know I can do a mail merge from Word and to use Excel as the data, but with the different letter types, I don't think that would be feasible.

 

[Trevor G]

So you would look to use a filter in Excel and then open a specific Word Document based on that letter type?

In excel you would need to reference to Word so you can use Word commands easily in Excel.

So in the workbook start of by setting the reference to use Word. Use Alt + F11 then > Tools Menu > References > Microsoft Word XX .Object Library.

Next insert a module Insert Menu > Module

Add this code

Sub wrdA()
'You need to set the Reference to Word
'Tools Menu > References > Microsoft Word XX Object Library
Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")
'Here you would need to consider filtering the excel worksheet
'Then open the relevant document which uses the Excel Source as its Mail Merge
'You code apply IF statements or Case Statements
With wrdApp
.Visible = True
.Documents.Open ("Path to document\Document Name.doc")
'What does it need to do from here
End With
End Sub