Hi DBG!
The user will have to do a "Save As" in Word after they finish editing the document. The code below, 1) works like I want it to 2) but hasn't been cleaned up, (work in progress). It opens a template with the company's logo and other key information from the Access [Quote Form]. Remember, the code below is from the Command Button but will be cleaned up as I get a little further in the project.
Private Sub QuoteLetterX_Click()
Dim Response As VbMsgBoxResult
DoCmd.RunCommand acCmdSaveRecord
' MsgBox ("Checking for blank contact address info")
If (IsNull(Forms![Company Form]![Contact Form].Form![AddressX]) Or _
IsNull(Forms![Company Form]![Contact Form].Form![CityX]) Or _
IsNull(Forms![Company Form]![Contact Form].Form![StateX]) Or _
IsNull(Forms![Company Form]![Contact Form].Form![ZIPX])) Then
' MsgBox ("There are blanks")
Response = MsgBox("Some of your contacts address information is blank. In order to properly " _
& "format your quote letter, it is suggested you don't open your quote letter until " _
& "you add your contacts address information. A quick way to do that is to click on Yes in this message " _
& "then 'Copy Company Address' button on your Contact Form. Do you want to cancel opening " _
& "your Quote letter, (Yes), or Continue to open your Word Quote Letter? (No)", vbQuestion + vbYesNo)
Select Case Response
Case vbYes
DoCmd.Close acForm, "Quote Form"
Case vbNo
End Select
Else
' MsgBox ("All contact info supplied")
Dim WordDoc As Word.Document
Dim WordAppl As Word.Application
'Dim ClientFirst, ClientLast, Loc, Phone, Time As String 'the first four are Variants!
'Dim ClientFirst As String, ClientLast As String, Loc As String, strTime As String
'Dim MeetingDate As Date
Dim strTemplate As String
Dim CompanyName As String
Dim ClientFirst As String
Dim ClientLast As String
Dim ClientAddress As String
Dim ClientCity As String
Dim ClientState As String
Dim ClientZIP As String
Dim QuotePrice As Currency
Dim QuoteNbr As String
Dim KeyInfo2 As String
Dim SalesEmailXY As String
Dim SalesPersonXY As String
Dim SalesTitleXY As String
Dim SalesCellXY As String
Dim SalesStreetXY As String
Dim SalesStreet2XY As String
Dim SalesCityXY As String
Dim SalesStateXY As String
Dim SalesZIPXY As String
Dim SalesReturnAddrXY As String
Dim SalesStreetsXY As String
Set WordAppl = New Word.Application
WordAppl.Visible = True
strTemplate = "C:\Users\Chuck\Documents\Custom Office Templates\MiJack quote Template2.dotx"
Set WordDoc = WordAppl.Documents.Add(Template:=strTemplate)
CompanyName = Nz(Forms![Company Form]![Company])
ClientFirst = Nz(Forms![Company Form]![Contact Form].Form!CFName)
ClientLast = Nz(Forms![Company Form]![Contact Form].Form!CLName)
ClientAddress = Nz(Forms![Company Form]![Contact Form].Form!CAddress)
ClientCity = Nz(Forms![Company Form]![Contact Form].Form!CCity)
ClientState = Nz(Forms![Company Form]![Contact Form].Form!CState)
ClientZIP = Nz(Forms![Company Form]![Contact Form].Form!CZIP)
QuotePrice = Nz(Forms![Quote Form]![ProposedRevenueX])
QuoteNbr = Nz(Forms![Quote Form]![QuoteNumberX])
KeyInfo2 = CompanyName + vbCrLf + ClientFirst + " " + ClientLast + vbCrLf _
& ClientAddress + vbCrLf + ClientCity + ", " + ClientState + " " + ClientZIP _
& vbCrLf + vbCrLf + vbCrLf + "Dear " + ClientFirst + "," + vbCrLf + vbCrLf
SalesEmailXY = Nz(DLookup("EMail", "[Admin Table]", "LimitNbr=1"))
SalesPersonXY = Nz(DLookup("FName", "[Admin Table]", "LimitNbr=1")) & " " & Nz(DLookup("LName", "[Admin Table]", "LimitNbr=1"))
SalesTitleXY = Nz(DLookup("Title", "[Admin Table]", "LimitNbr=1"))
SalesCellXY = Nz(Format(DLookup("Cell", "[Admin Table]", "LimitNbr=1"), "(###) ###-####"))
SalesStreetXY = Nz(DLookup("Street", "[Admin Table]", "LimitNbr=1"))
' SalesStreet2XY = Nz(DLookup("Street2", "[Admin Table]", "LimitNbr=1"))
If IsNull(DLookup("Street2", "[Admin Table]", "LimitNbr=1")) Then
SalesStreet2XY = ""
Else
SalesStreet2XY = ", " & DLookup("Street2", "[Admin Table]", "LimitNbr=1")
End If
SalesStreetsXY = Nz(DLookup("Street", "[Admin Table]", "LimitNbr=1"))
SalesCityXY = Nz(DLookup("City", "[Admin Table]", "LimitNbr=1"))
SalesStateXY = Nz(DLookup("State", "[Admin Table]", "LimitNbr=1"))
SalesZIPXY = Nz(DLookup("ZIP", "[Admin Table]", "LimitNbr=1"))
SalesReturnAddrXY = SalesStreetXY & SalesStreet2XY & ", " & SalesCityXY & " " & SalesStateXY _
& " " & SalesZIPXY & " " & SalesCellXY
'Loc = Forms!MeetingForm!MeetingLoc
'strTime = Forms!MeetingForm!MeetingTime
DoCmd.Close acForm, "Quote Form"
Debug.Print WordDoc.Bookmarks.Count 'show test presence of bookmarks
With WordDoc.Bookmarks
.Item("KeyInfo").Range.Text = KeyInfo2
' .Item("QuoteRev").Range.Text = QuotePrice
.Item("QuoteItem").Range.Text = QuoteNbr
.Item("SalesEmail").Range.Text = SalesEmailXY
.Item("SalesPerson").Range.Text = SalesPersonXY
.Item("SalesTitle").Range.Text = SalesTitleXY
.Item("SalesCell").Range.Text = SalesCellXY
.Item("SalesReturn").Range.Text = SalesReturnAddrXY
'.Item("CFirst").Range.Text = ClientFirst
'.Item("CLast").Range.Text = ClientLast
'.Item("CoName").Range.Text = CompanyName
'.Item("CoAddress").Range.Text = ClientAddress
'.Item("CoCity").Range.Text = ClientCity
'.Item("CoState").Range.Text = ClientState
'.Item("CoZIP").Range.Text = ClientZIP
'.Item("Quote").Range.Text = QuotePrice
.Application.Activate
'.Item("Location").Range.Text = Loc
End With
MsgBox ("This is just before 'Set WordDoc = Nothing'.")
'WordDoc.Save 'Prompts user for directory, filename, etc.
'WordDoc.SaveAs "YourFileName.doc" ' Use this one if you know where you want to save it
'WordDoc.Close
Set WordDoc = Nothing ' release reference to document
' WordAppl.Quit
Set WordAppl = Nothing ' release reference to application
End If
End Sub