Dynamically save automagically generated word document!
May 15, 2007 6:20 AM 
Subscribe
VBA assistance needed. I've got the automerge and print down, now to save it as a unique file...
I've got a database used to track donors. Upon entry of the appropriate data, I press a "Print Thank-You" button, and it auto merges the fields from THAT form into a form letter and automatically prints it. What I'd like to do is have it automatically SAVE the letter too, but with a new filename so that it doesn't overwrite my form letter.
Example:
Donor name is John Smith, date is 5/15/07. I'd like to save the file name as Smith 5-07 or something similar.
Here's the code I have right now:
######start code######
Option Compare Database
Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("C:\Users\ReStore\Documents\thankyouletter.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!MainData!DonorLastName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!MainData!DonorAddress))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!MainData!DonorCity))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Forms!MainData!DonorState))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Forms!MainData!DonorZip))
.ActiveDocument.Bookmarks("First2").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!MainData!Date))
.ActiveDocument.Bookmarks("items").Select
.Selection.Text = (CStr(Forms!MainData!ItemtoDonate))
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=True
'Close the document without saving changes.
'objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
'objWord.Quit
'Set objWord = Nothing
Exit Sub
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
'If Err.Number = 94 Then
' objWord.Selection.Text = ""
' Resume Next
'If the Photo field is empty.
'ElseIf Err.Number = 2046 Then
' MsgBox "Please add a photo to this record and try again."
'Else
' MsgBox Err.Number & vbCr & Err.Description
'End If
Exit Sub
End Sub
#####end code#####
Suggestions? Thanks!
posted by TomMelee to computers & internet (11 comments total)
1 user marked this as a favorite
objWord.ActiveDocument.SaveAs FileName:="whatever.doc"before you close the document.
If you want to generate a new, unique filename for the generated form letter, you can pull data out of the form to generate a discriptive name like this:
Dim FName As String
FName = "Form letter " & Forms!MainData!DonorFirstName & " " & Forms!MainData!DonorLastName & ".doc"
objWord.ActiveDocument.SaveAs FileName:=FName
If you absolutely need to make sure the filename doesn't clash with an already-generated form letter, you'll have to read the files in the directory and perhaps append an incrementing number to the end of the filename in the case of duplicates. (Word's object model doesn't support generating a unique filename, at least as far as I know.)
posted by zixyer at 7:25 AM on May 15, 2007