View Single Post
Old 06-02-2016, 08:59 AM
StaplerGnome StaplerGnome is offline Windows 7 64bit Office 2010 64bit
Join Date: May 2016
Posts: 8
StaplerGnome is on a distinguished road

Sorry to remain a pain. Unfortunately it isn't working - let me explain a bit more.

The way the documents are actually created is not from the Word doc. There's an Access database containing records. We create documents by selecting one of various buttons to generate the desired mail merged letter for that specific record.

I assume this is preventing your solution from doing anything by bypassing the way it gets triggered.

I've also tried the Unlink line in the main VBA code that runs the merge, but no joy (code line emphasised below):

'this subroutine is what actually performs the mail merge
Sub RunMerge(docPath, docName)

'check whether the record is locked pending replacement examiners
If Me.checkReplacement = True Then
MsgBox ("Awaiting replacement examiners. Do not send thesis.")
Exit Sub

Set oApp = CreateObject("Word.Application")

'this bit declares some variables used later
Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String
Dim currentDbName As String
Dim selectSQL

Dim getDoc As String
getDoc = docPath & "\" & docName

' this specifies the location of the Word mail merge document we want to use
Set oMainDoc = oApp.Documents.Open(getDoc)
oApp.Visible = True

' this gets the file name and location of this Access database, used to provide data to the mail merge
' it's REALLY IMPORTANT TO NOTE that for inexplicable reasons, this only works with a .mdb file
' if you use an .accdb database, this stops working
' it will say it can't find the file FILEPATH.mdb, and strip out the filename and .accdb bit entirely
' I was unable to find ANY workarounds for this at all. Microsoft have just broken this functionality
' so use an .mdb
currentDbName = CurrentDb.Name

'this copies data from qryMain (where all our data is stored and combined) into the holding pen of tblMergeSource.
'Using a table as the mail merge data source is more stable than using a query
With DoCmd
.SetWarnings False
'clear the existing data
.RunSQL "DELETE FROM tblMergeSource"
'copy over the current record only - we only want to print letters for this student, not for everyone in the database!
'so copy records where the Reg No equals the Reg No currently displayed on the form you're looking at
.RunSQL "INSERT INTO tblMergeSource SELECT * FROM qryMain WHERE qryMain.[Registration No]='" & Me.[Registration No] & "'"
.SetWarnings True
End With

'this is the instruction we'll give to the mail merge in a minute, telling it to grab everything in tblMergeSource
selectSQL = "SELECT * FROM [tblMergeSource]"

'prepare a mail merge
With oMainDoc.mailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=currentDbName, _
End With

'actually do the mail merge
With oMainDoc
'.MailMerge.Destination = wdSendToNewDocument
.Fields.Unlink <<<<<<<<<<<<<<<<<<<it's here
End With

oMainDoc.Close (Word.WdSaveOptions.wdDoNotSaveChanges)
'move the focus to the Word document
oApp.Documents.Parent.Visible = True
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1

'RemovePicLinks (I also tested using a separate function, see below)

'clear various variables from memory to avoid strange crashing issues
Set oMainDoc = Nothing
Set oSel = Nothing

Set oApp = Nothing

End If

End Sub
In this case I call it twice and generate two documents with a single button.

I've tried a solution suggested elsewhere, of using a separate sub called by the main, but this only delinks the first document, presumably because of something about how it finds ActiveDocument.

Public Sub RemovePicLinks()

Dim f As Word.Field
Dim i As Long
Dim lngCount As Long
Dim strMessage As String

For i = ActiveDocument.Fields.Count To 1 Step -1
Set f = ActiveDocument.Fields(i)
If f.Type = wdFieldIncludePicture Then
lngCount = lngCount + 1
End If
Next i

If lngCount > 0 Then
strMessage = "Complete - " & CStr(lngCount) & " picture links were broken"
strMessage = "Complete - no picture links were broken"
End If

MsgBox strMessage

End Sub
I suppose individual buttons for each document might work, but I'd really rather avoid having two dozen extra buttons on an already crowded UI to allow for every possible combination of letters that need making - and of course it greatly increases the risk of user error/omission.
Reply With Quote