7 replies
by bschaettle
It will be easier to do it the other way around: use the Excel spreadsheet as a data source for a Word Mail Merge to generate the letters. You'll need to add another column to your spreadsheet called "Letter Sent" that will have values of Yes or No. You can set up a data validation rule to restrict the input on these cells to just these two values.
With Word, set up a letter template to mail merge only those records that have the "X" marked, and the Letter Sent column NOT equal to "Yes". After the letters are printed, re-open the spreadsheet and mark off the records that letters for which letters have been sent. I know this sounds like a pain, but it's about the simplest solution.
We've sent out thousands of letters to clients using a system like this. The spreadsheet is also a good tracking tool to see who letters have been sent to.
by 4denhay
Poster rated this answer.
We only send 1 letter periodically while following the progress of a job. Still wonder if there is a method to populate a cell and have a letter printed automatically.
by rkuhn040172
Do it all in MS Access.
1) Design a form for data input
2) Have a report for the mail merge
3) Run a query to mark those sent
All automated, except the data entry. For that matter, using an ODBC connection you might be able to connect to your other data sources and avoid data entry too.
by 4denhay
Poster rated this answer.
Thanks for the input.
by bschaettle
Oh, very well, we'll do it your way. Open your spreadsheet, open the VBA Editor, and "View Code" for the Worksheet object. Now paste in the code shown below. This will print a Word document whenever an "x" is typed into Column A.
---------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wdApp As Object
If Target.Cells(1, 1).Column = 1 Then ' Column "A"
If UCase(Target.Cells(1, 1).Value) = "X" Then
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
wdApp.Documents.Open "C:\folder\test Document.doc"
wdApp.ActiveDocument.PrintOut
DoEvents
' wait 5 seconds for the letter to print
Application.Wait (Now + TimeValue("0:00:5"))
wdApp.ActiveDocument.Close
wdApp.Quit
Set wdApp = Nothing
End If
End If
End Sub
by 4denhay
Poster rated this answer.
Thanks for the help.
by 4denhay
This discussion is closed to new replies.
Trending Software posts
Sort by Trending