Creating a letter from a worksheet

[Phonedude]

Here's what I am trying to accomplish. I have several workbooks which
keep track of customers for me. There is a master sheet with a list
of all the customers in the book and each row has a link to that
particular customer's own sheet. I would like to be able to go the
the customer sheet and click an icon and open a Word file, insert data
from particular cells and let me print the letter after reviewing it
in Word. Several different letters, each with their own icon, or
mayby one icon with a letter selection option. Not interested in
saving the letter, but I might add code to the module which notes, on
the sheet, that the letter was sent.

This is *not* a mail merge -- it's one sheet with one customer. The
name, address, and other pertinent information is all on the sheet in
individual cells, but it's not a database.

I've searched and searched and cannot find a clue -- I am not
incompetent in VBS, but I can't find a direction to go in. Can
someone give me a clue?

Open a Word doc, insert data from known cells in the active sheet in
predetermined locations in the doc . . .

Does this make sense and is there a reference I can go to?

Thanks,

Larry

 

[Steve Yandl]

Larry,

I think the easiest approach is to first create a Word template file
containing bookmarks where you want to insert values found in various
workbook cells. For the example below, I opened Word, typed some stuff and
inserted a bookmark named "CustomerSalutation". When it came time to save,
I changed the file type from Word document to Word Template (dot file),
named it myLtr.dot and saved it to "C:\Test". If you save it to the default
template location, you can avoid having to type in the full path in your
subroutine but that's your choice.

Note that if you're not the only one using the subroutine and there is a
chance that people will launch it when their might be other instances of
Word already running, there are more elaborate ways to start Word but I
wanted to keep it simple here. There are also ways to save, close, print
etc. but I just opted to open a new document based on the template myLtr.dot
and insert the value found in cell B3 immediately after the bookmark named
CustomerSalutation and then leave the Word document open and visible.

_________________________________________
Sub WriteLetter()
strTxtInsert = Cells(3, 2).Value
Set oApp = CreateObject("Word.Application")
oApp.Documents.Add Template:="C:\Test\myLtr.dot"
oApp.ActiveDocument.Bookmarks("CustomerSalutation").Range.InsertAfter
strTxtInsert
oApp.Visible = True
End Sub


_________________________________________

Steve

 

[Steve Yandl]

Note,

oApp.ActiveDocument.Bookmarks("CustomerSalutation").Range.InsertAfter
strTxtInsert

should be on a single line with a space between InsertAfter and strTxtInsert

Steve

 

[Phonedude]

Note,

oApp.ActiveDocument.Bookmarks("CustomerSalutation").Range.InsertAfter
strTxtInsert

should be on a single line with a space between InsertAfter and strTxtInsert

Steve

Thanks -- this is exactly what I needed to get going. I tried it and
it works perfectly.

Larry