Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.
Mailmerge Winword from MS Access

Mailmerge Winword from MS Access

Before doing that you must tell MS Access how to talk to Winword. You could create a simple "Object", but it's more flexible to have an Object of the type "Word.Document". So Access knows about all allowed methods of Winword.

Tell MS Access about Winword

Open any code windows in Access (Alt-F11) and choose Extras - "Verweise" (this is the german definition, how is it called in English?)


Activate "Microsoft Word 10.0 Object Library".


Create Mailmerge with Access Visual Basic

Create a button and look at this code:

Private Sub btn_create_mailmerge_Click()
    ' Create a winword document with datasource
    ' of tpye query
    ' The winword document must not be opened
    On Error GoTo Err_btn_create_mailmerge_Click

    Dim db As Database
    Dim qdef  As QueryDef
    Dim sql As String
    Dim query_name As String
      
    Set db = CurrentDb

    ' What is the name of the query which is the datasource
    ' for the winword-document?
    query_name = "money_for_the_club"
    
    ' Create a new query which contains as data only one 
    ' record. The club which gets money from us
    sql = _
        "SELECT money_for_clubs.*, clubs.*" _
        + " FROM clubs RIGHT JOIN money_for_clubs" _
        + " ON clubs.club_id = money_for_clubs.club_id" _
        + " WHERE money_for_clubs.club_id=" + Str(Me.club_id) _
        + " AND money_for_clubs.zuschuss_jahr=""" + Me.money_for_year + """"
    ' Delete old query
    db.QueryDefs.Delete query_name
    ' and re-create it immediately
    Set qdef = db.CreateQueryDef(query_name, sql)
    qdef.Close
    
    ' What's the name of the word-document?
    ' It's in the same directory as the MS Access database itself
    ' The name of the file is a record in a table "ini"
    Dim ww_mailmerge_fqn As String
    ww_mailmerge_fqn = database_path() + "\" _
        + DLookup("[value]", "ini", "[variable] = ""ww_mailmerge_fn""")
    
    ' Open Word document with Winword
    Dim oWord As Word.Document
    Set oWord = GetObject(ww_mailmerge_fqn, "Word.Document")
    
    
    oWord.Application.Visible = True
    ' Open my own MS Access database as datas-source
    ' Not exclusive, because this database is just opened by me
    '
    ' Parameter SubType: with that currency-fields get converted right
    ' see -> http://support.microsoft.com/kb/324378/en-us
    ' SubType:=wdMergeSubTypeWord2000,
    oWord.MailMerge.OpenDataSource _
            Name:=CurrentDb.Name, _
            LinkToSource:=1, _
            Connection:="QUERY datasource_for_moneyletter", _
            openExclusive:=0, _
            SQLStatement:="SELECT * FROM [datasource_for_moneyletter]"
    oWord.MailMerge.Execute

    Set oWord = Nothing
    
Exit_btn_create_mailmerge_Click:
    Exit Sub
    
Err_btn_create_mailmerge_Click:
    MsgBox Err.Description
    Resume Exit_btn_create_mailmerge_Click
    
End Sub


Public Function database_path()

Dim Merk As String
Dim Nam As String
Dim Pfa As String
Dim T As Integer
Dim Lang As Integer
Dim Wo As Integer

    Merk = [CurrentDb].[Name]
    Lang = Len(Merk)
    For T = 0 To Lang - 1
        Merk = Mid([CurrentDb].[Name], Lang - T, 1)
        If Merk = "\" Then
            Wo = Lang - T
            Exit For
        End If
    Next T
    database_path = Left([CurrentDb].[Name], Wo - 1)

End Function

Impressum