Nothing looks right about your code. For the purpose that you are talking about, it would be better to insert { DOCVARIABLE fieldname } fields in the document where you want the information to appear and use code similar to:
Dim objDoc As Object
Dim objWord As Object
Dim bolOpenedWord As Boolean
Dim i As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("[name of Access table or query]")
If rs.RecordCount = 0 Then
Exit Sub
End If
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
bolOpenedWord = True
End If
On Error GoTo 0
rs.MoveFirst
Do Until rs.EOF
Set objDoc = objWord.Documents.Add("C:\Users\user\Desktop\Access2Word.dotx"), False)
With objDoc
For i = 0 To rs.Fields.Count - 1
If rs.Fields(i).Value <> "" Then
.variables(rs.Fields(i).Name).Value = rs.Fields(i).Value
Else
.variables(rs.Fields(i).Name).Value = " "
End If
Next i
.Range.Fields.Update
.SaveAs2
"C:\Users\user\Desktop\" & rs.Fields(0).Value & ".docx"
' Field number starting from 0 of the field that contains the data to be used as the filename
' I WOULD MOST STRONGLY SUGGEST THAT YOU DO NOT SAVE THE DOCUMENTS TO THE DESKTOP!
.Close
End With
rs.MoveNext
Loop
Set objDoc = Nothing
If bolOpenedWord = True Then
objWord.Quit
End If
Set objWord = Nothing
Set objDoc = Nothing
rs.Close
Set rs = Nothing
Different code will be required if you want to merge the data from multiple records into a table in the document
There is a utility for doing that (Many to One Merge) on the MergeTools – 20150309.dotm Add-in that you can download from the following page of my One
Drive:
http://bit.ly/1hduSCB
The MergeTools – 20150309.dotm file needs to be saved in the Word Startup folder.
In Windows Vista and Windows 7, 8 or 8.1, the default location for that folder is
C:\Users\[User Name]\AppData\Roaming\Microsoft\Word\STARTUP
If you do not see the AppData folder: -
In Windows 7, - In Windows Explorer, click on the Organize drop down and then on Folder and search options and in the Folder Options dialog, go to the View tab and select the item "Show hidden files, folders, and drives". While there, it is a good idea to
uncheck the box of "Hide extensions for known file types".
In Windows 8 and 8.1, in the File Explorer, click on Options on the View tab of the ribbon and then on the View tab in the dialog that appears and select the item "Show hidden files, folders, and drives". While there, it is a good idea to uncheck the box of
"Hide extensions for known file types".
When that has been done and Word is started\re-started, the tab shown below will be added to the Ribbon:
The requirements for using the system are:
-
The mail merge main document must be of the Letter type, though that does not mean that the output cannot be sent as an e-mail message.
-
For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet
in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left.
For the Chart Merge utility, download the Mail Merging with Charts document that is also on that page of my OneDrive for additional requirements of the data source for use with that utility
-
For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
-
For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9).
-
For a data source in the form of an Excel worksheet, the data must start in the second row of the worksheet and there should be no empty rows within the range of data that is to be processed.
You may also want to download:
-
the Merging with Attachments document that is also on that page which explains how the system is used.
It is not actually necessary to have separate attachments as the facility can be used to send just the documents created by the merge itself as attachments, either as the body of the message itself or in the form of Word files or .pdf files.
-
the Mail Merging with Charts document that is also on that page.
That document explains how you must set up the Excel Data Source and the Mail Merge Main document to be able to execute a merge with a Chart that is unique to each record in the data source.
-
the Using the Many to One Facility document that describes how to use that facility.
