R
ryguy7272
I found this (below) Access code online:
Option Compare Database
Public Sub SendLetters()
Dim wapp As Word.Application
Dim wdoc As Word.Document
Dim fld As Word.FormField
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim filenm As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_CustomerData")
Set rs2 = db.OpenRecordset("tbl_MailInformation")
Set wapp = New Word.Application
wapp.Visible = True
rs.MoveFirst
While Not rs.EOF
If rs.Fields("Days_Past_Due").Value <= 30 Then
filenm = "C:\test\Letter1.doc"
End If
If rs.Fields("Days_Past_Due").Value > 30 Then
filenm = "C:\test\Letter2.doc"
End If
Set wdoc = wapp.Documents.Open(filenm)
For Each fld In wdoc.FormFields
fld.Result = rs.Fields(fld.Name).Value
Next fld
wdoc.SaveAs "C:\test\" & _
rs.Fields("Name").Value & ".doc"
wdoc.Close
rs2.AddNew
rs2.Fields("CustomerDataID").Value = rs.Fields("CustomerDataID").Value
rs2.Fields("DateMailed").Value = Now()
rs2.Fields("LetterMailed").Value = filenm
rs2.Update
Set wdoc = Nothing
rs.MoveNext
Wend
Set fld = Nothing
wapp.Quit
Set wapp = Nothing
rs2.Close
rs.Close
Set rs2 = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
Works great! For me, the only problem is that the fields in the Word docs
are not named, so Access just pushes the data through. As far as I can tell,
the code doesn't really link certain fields from the Access table to certain
fields in the Word doc. I’ve done this before, pushing data from Excel to
Word, by using DocVariables in Word. There must be a similar way of doing
this in Access, by linking the fields in the Access table to the DocVariables
in the Word doc.
Below is a sample of the code that I use to control Word from Excel:
Sub PushToWord()
Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next
objWord.ActiveDocument.variables("BrokerFirstName").Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName").Value =
Range("BrokerLastName").Value
objWord.ActiveDocument.Fields.Update
'On Error Resume Next
objWord.Visible = True
End Sub
When I try to run this code in Access I get this message:
Compile Error: Method or data member not found.
This is highlighted blue:
..GetOpenFilename
The reference to Word is checked off. What am I doing wrong?
Thanks,
Ryan---
Option Compare Database
Public Sub SendLetters()
Dim wapp As Word.Application
Dim wdoc As Word.Document
Dim fld As Word.FormField
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim filenm As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_CustomerData")
Set rs2 = db.OpenRecordset("tbl_MailInformation")
Set wapp = New Word.Application
wapp.Visible = True
rs.MoveFirst
While Not rs.EOF
If rs.Fields("Days_Past_Due").Value <= 30 Then
filenm = "C:\test\Letter1.doc"
End If
If rs.Fields("Days_Past_Due").Value > 30 Then
filenm = "C:\test\Letter2.doc"
End If
Set wdoc = wapp.Documents.Open(filenm)
For Each fld In wdoc.FormFields
fld.Result = rs.Fields(fld.Name).Value
Next fld
wdoc.SaveAs "C:\test\" & _
rs.Fields("Name").Value & ".doc"
wdoc.Close
rs2.AddNew
rs2.Fields("CustomerDataID").Value = rs.Fields("CustomerDataID").Value
rs2.Fields("DateMailed").Value = Now()
rs2.Fields("LetterMailed").Value = filenm
rs2.Update
Set wdoc = Nothing
rs.MoveNext
Wend
Set fld = Nothing
wapp.Quit
Set wapp = Nothing
rs2.Close
rs.Close
Set rs2 = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
Works great! For me, the only problem is that the fields in the Word docs
are not named, so Access just pushes the data through. As far as I can tell,
the code doesn't really link certain fields from the Access table to certain
fields in the Word doc. I’ve done this before, pushing data from Excel to
Word, by using DocVariables in Word. There must be a similar way of doing
this in Access, by linking the fields in the Access table to the DocVariables
in the Word doc.
Below is a sample of the code that I use to control Word from Excel:
Sub PushToWord()
Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next
objWord.ActiveDocument.variables("BrokerFirstName").Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName").Value =
Range("BrokerLastName").Value
objWord.ActiveDocument.Fields.Update
'On Error Resume Next
objWord.Visible = True
End Sub
When I try to run this code in Access I get this message:
Compile Error: Method or data member not found.
This is highlighted blue:
..GetOpenFilename
The reference to Word is checked off. What am I doing wrong?
Thanks,
Ryan---