pseale's Word Mail Merge in FAQ 181-5088 - a few questions 1

[marcus101]

Hello all:

I've been experimenting a bit with pseale's Mail Merge code (posted in FAQ #181-5088), and was wondering if:

a) if anyone has tried pseale's solution,
b) what references/libraries are needed to make it work, if necessary,
c) if the directory/path functions are absolutely necessary in order to run the ToCSV and MailMerge creation functions properly - I'm guessing probably not if they point directly to where they need to point using a specific drive letter and path reference.

Any info/thoughts on this would be most appreciated.

Thanks,


marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[Remou]

Hi
a. I have, in a small sort of way and much prefer it to linking Word to Access.
b. Just the Microsoft DAO x.x Oject Library, as far as I know.
c. I use a stripped-down version that seems to work well.

 

[marcus101]

Hi Remou, thanks for your suggestions.

I have now integrated pseale's MailMerge code a bit more fully into my work, and am now trying to solve a couple of minor issues:

1. I don't quite have an import/export specification...yet.

Not having one basically breaks things in the RunMailMerge function. I get an Error #3625, which I suppose is pretty common if you don't have that piece present.

I'm trying to work around this by running the Export Wizard on the data source I need to merge from (the entire table for now, thankfully, I only need one data table anyway) and to save this as a named specification into the database using the Advanced button.

This part seems pretty easy, But...

2. Where the heck do I use the specification name once it is built?

Is it just as simple as declaring it as a constant per pseale's code and then inserting the name of the spec verbatim into the RunMailMerge Function?

Looks that way, but at this point, I just want to be sure.

3. Are there other things I should look out for when I use this, or is this pretty much it?

It seems pretty straight forward, and I think I'm getting close here, but any other gotcha warnings would be helpful.

4. Is there a generic text or RTF spec out there one can use, or do you need to build a specific export spec using the wizard for every table that you might use when you call the RunMailMerge function?

For example, it might make more sense/efficiency for me to rebuild my current spec using a generic query that calls only the fields that I need to export, then just use the Export Data Wizard to build a spec I can use from that...?

Once again, thanks for your help guys, and I'm looking forward to playing with this in more depth...


marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[Remou]

You do not have to have a specification, unless you have some fancy stuff to export. I used this line:

[tt]DoCmd.TransferText acExportDelim, , strQdfName, FullPathAndFilename, True[/tt]

Which works fine for straight names and addesses. You can use a generic query, if you prefer. You just insert the specification name as you mentioned.

One of the really big advantages of pseale's idea is that you can allow quite amateur users to develop their own letters, certificates, etc with the data and to add these names to a table of documents. After the praise, the butchery : -) Here is my stripped down version, please note that it is still all pseale's code, just trimmed a little:

Public Sub RunMailmerge(SQL As String, MergeDocumentFilename As String)
On Error GoTo Sub_Error
    Dim objWordDoc As Object
    Dim strMailmergeDataFilename As String
    Dim strDir As String
    
    strDir = CurrentProject.Path & "\WordFiles\"
    strMailmergeDataFilename = strDir & Format(Now, "yymmdd_hhnnss") & ".txt"
    
    ExportSQLToCSV SQL, strMailmergeDataFilename
    
    Set objWordDoc = GetObject(strDir & MergeDocumentFilename, "Word.Document")
    
    objWordDoc.Application.Visible = True
    
    'Format:=0 '0 = wdOpenFormatAuto
    objWordDoc.MailMerge.OpenDataSource _
        Name:=strMailmergeDataFilename, ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=0, _
        Connection:="", SQLStatement:="", SQLStatement1:=""

    'objWordDoc.MailMerge.Destination = 0 '0 = wdSendToNewDocument
    
    'objWordDoc.MailMerge.Execute
    
Sub_Exit:
On Error Resume Next
    'Word pops up a messagebox to confirm saving the document,
    'even if specifically set it to "wdDoNotSaveChanges".
    'Therefore first save the document, then close it.
    'objWordDoc.Save
    'objWordDoc.Close SaveChanges:=-1 '-1 = wdSaveChanges
    
    Set objWordDoc = Nothing
    'attempt to delete file, silently fail on errors.
    'FileSystem.Kill strMailmergeDataFilename
    
    Exit Sub
    
Sub_Error:
    If Err.Number = 432 Then
        MsgBox "ERROR: Invalid filename provided: '" & strMailmergeDataFilename & "' or " & _
        "'" & strDir & MergeDocumentFilename & "'."
    Else
        MsgBox Err.Description
    End If
    Resume Sub_Exit
End Sub


Public Sub ExportSQLToCSV(SQL As String, FullPathAndFilename As String)
On Error GoTo Sub_Error
    'steps:
    '-create a new querydef with the SQL parameter as its SQL.
    'save & attach this querydef.
    'run the TransferText on this temp querydef,
    'delete the querydef
    
    Dim strQdfName As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    strQdfName = "~temp_mailmerge_" & Format(Now, "yymmdd_hhnnss")

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(strQdfName, SQL)
    Set qdf = Nothing
    Set db = Nothing
    
    If Nz(DCount("*", strQdfName), 0) <= 0 Then
        'in this case, if the query is empty/contains no entries, we
        'should pop up a custom error message.  Basically we shouldn't attempt to
        'run the mailmerge if there's no data anyway, right?
        'So we have to turn off the standard error handling, delete the query (otherwise
        'the query is not deleted), and raise the error.
        '
        'the calling function "RunMailmerge" will catch and handle the error, and then
        'gracefully exit.
        On Error GoTo 0
        CurrentDb.QueryDefs.Delete strQdfName
        Err.Raise vbObjectError + 1024 + 2, "Query Export to CSV", "The report has no data, and thus cannot properly merge."
    End If
    
    AttemptToDeleteFile FullPathAndFilename
    DoCmd.TransferText acExportDelim, , strQdfName, FullPathAndFilename, True
    

Sub_Exit:
On Error Resume Next
    CurrentDb.QueryDefs.Delete strQdfName
    
    Exit Sub
    
Sub_Error:
    MsgBox Err.Description
    Resume Sub_Exit
End Sub


'AttemptToDeleteFile -
'attempts to delete the mailmerge.txt file located in the database directory.
'Basically provides error-handling capabilities to the single "Kill" method call.

Private Sub AttemptToDeleteFile(strFilename As String)
On Error GoTo Sub_Error
    
    Kill strFilename
    
Sub_Exit:
    Exit Sub
    
Sub_Error:
    If Err.Number = 53 Then 'err 53 = file not found, that means the file is already deleted!
        'no error, continue
        Resume Sub_Exit
    ElseIf MsgBox("Cannot delete file.  Close all Word mailmerge documents and click Retry.", vbRetryCancel + vbExclamation, "File In Use") = vbRetry Then
        Resume
    Else
        On Error GoTo 0
        Err.Raise vbObjectError + 1024 + 1, "file in use", "File In Use@" & _
                    "Cannot complete the mailmerge process because the file '" & strFilename & "' " & _
                    "is in use.  Close all Word mailmerge documents and try again."
    End If
End Sub

 

[marcus101]

Hi Remou:

All very interesting stuff, will try out and let you know how things go. I will likely putter around with both using an export spec and your suggestions and will compare both to see how things go...

I'm a bit curious as to this bit of code that you posted (taken from the RunMailmerge function):

 'objWordDoc.MailMerge.Destination = 0 '0 = wdSendToNewDocument
    
    'objWordDoc.MailMerge.Execute
    
Sub_Exit:
On Error Resume Next
    'Word pops up a messagebox to confirm saving the document,
    'even if specifically set it to "wdDoNotSaveChanges".
    'Therefore first save the document, then close it.
    'objWordDoc.Save
    'objWordDoc.Close SaveChanges:=-1 '-1 = wdSaveChanges
    
    Set objWordDoc = Nothing
    'attempt to delete file, silently fail on errors.
    'FileSystem.Kill strMailmergeDataFilename
    
    Exit Sub

In your changes, I've noticed that:

a) objWordDoc. and objWordDoc.MailMerge commands are commented out.

Does this not run the Word Mail Merge component per se, or just do it differently due to the use of the TransferText command in the ExportToCSV function?

I raise this because I DO want to run the MS Word Mail Merge component as-is as much as possible, (and possibly even go straight to PrintPreview from that)

b) FileSystem.Kill is commented out, which leads me to believe that you want to hang on to the data text files once they are finished. Any particular reason for that, perhaps, as you say, to create a table of data files?

I like your changes to the format of the data files.

Will keep you posted.

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[Remou]

You are correct at a), I have stopped the merge component as it is for a blank letter, to be filled in by the user.

As for the kill, I have found that I always seem to deal with people who ask (often several years later) "Was such a letter sent to so-and-so? When? Can you give me a copy?" If you show these people a file on disk, rather than in a database, it keeps them happy.

 

[marcus101]

Hi Remou:

Success! Everything works great, pretty much exactly as advertised. I will try and see if I can get pseale a star for his work as well, this is wonderful stuff and works beautifully, and is quite fast.

I didn't have a chance to try it pseale's way (it might be faster) but I will post some more info and results on those experiments in a week or so, because I'm still curious as to how the export spec function works, and I like tinkering with these things.

However, I am still wondering about one piece of code in the RunMailmerge function...

...

 objWordDoc.mailmerge.OpenDataSource _
        Name:=strMailmergeDataFilename, ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=0, _
        Connection:="", SQLStatement:="", SQLStatement1:=""

    objWordDoc.mailmerge.Destination = 0 '0 = wdSendToNewDocument
    
    objWordDoc.mailmerge.Execute
    
    ' ADDED: Print Preview the Merged Document..no effect?

' ---> '  objWordDoc.PrintPreview

I'd still like to get Word to jump straight into Print Preview or possibly the Page Setup dialog box after the basic mail merge to new document process starts.

Not quite sure if this is even doable, have tried a few different ideas and variations, but no luck.

Would be a "nice to have" though.

I've also been trying to directly edit my templates using a separate form button that grabs a signature name from a text box and changes it via an existing Word template bookmark, but been having only some luck there, can only open the template for editing, not change the bookmark -

My VBA chokes on the wdGoToBookmark line and doesn't recognize it as a command/function for some reason...

I'll post this in more detail in a different message/forum, but here's what I have so far (other ideas commented out):

 ' NOTE: 'AgentSig' is my Word template's bookmark..

    Dim objWordDoc As Object
    Set objWordDoc = GetObject(tempDoc, "Word.Document")

    ' Tried initializing this way first, but no luck.        
    ' Maybe because of conflicting library references?
    
    'Dim WordApp As Word.Application
    'Dim WordDoc As Word.Document
    
    'Set WordApp = CreateObject("Word.Application")
    'Set WordDoc = WordApp.Documents.Open(tempDoc)

    ' Show Word document..works this far..
    objWordDoc.Application.Visible = True
 
    ' Jump to the Bookmark Selection and replace text?

    If Not IsNull(sigVal) Then
    
    objWordDoc.Selection.GoTo wdGoToBookmark, , , "AgentSig"
    ' objWordDoc.Selection.Find.ClearFormatting ' needed?

    ' TEST: Is this entire section needed?

    'With objWordDoc.Selection.Find
    '    .Text = ""
    '    .Replacement.Text = ""
    '    .Forward = True
    '    .Wrap = wdFindContinue
    '    .Format = False
    '    .MatchCase = False
    '    .MatchWholeWord = False
    '    .MatchWildcards = False
    '    .MatchSoundsLike = False
    '    .MatchAllWordForms = False
    'End With

    objWordDoc.Selection.TypeText Text:=sigVal
    objWordDoc.Save

    End If

    objWordDoc.Close SaveChanges:=-1 '-1 = wdSaveChanges
    
    Set objWordDoc = Nothing

Thanks again for your help, guys, and I will post again soon once I finish up this work and try out and implement the export spec. I will provide comparison results to see how this all goes!

Cheers,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[Remou]

I think that you can acknowledge pseale by rating the FAQ.

[tt]objWordDoc.Application.ActiveDocument.PrintPreview[/tt]
ObjWordDoc is the document, you need the application for a preview.

' Tried initializing this way first, but no luck.
' Maybe because of conflicting library references?

'Dim WordApp As Word.Application
'Dim WordDoc As Word.Document
You may not have a reference to the Word Object Library, pseale uses late binding.