Solved

Mail Merge SQL Table Data To MS Word

Posted on 2002-04-16
12
1,544 Views
Last Modified: 2010-05-18

Guys & Gals..

I need to very quickly knock together an app that will mail merge som data from an SQL table to a MS Word document. But I have no experience with word objects or for that matter using a Mail Merge through word...

The idea is very simple but I need help on getting started, do any of you have any source knocking around that I could look at???

Thanks Madmarlin

PS Helpfull source will lead to greater double points
0
Comment
Question by:Madmarlin
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6944749
0
 

Accepted Solution

by:
nickwoolley earned 100 total points
ID: 6945081
Here's two options I've used before.

1.Use Word's mail merge to merge data from a table via a DSN connection.

2.Create a csv file from a recordset then merge data from csv file using Word's mail merge.


1.Mail merge via ODBC DSN

Function MergeWordDoc(strTemplateName as string, Optional varTable As String) As Integer
   
    Dim i As Integer
    Dim nSec As Integer
   
    'Requires declarations and string values set for variables sDbPath & sDbName

    On Error GoTo ErrHandler
   
        With wdApp.ActiveDocument.MailMerge
            'Create Main Document from template
            .MainDocumentType = wdFormLetters

            'Get data source
            .OpenDataSource Name:= _
                sDbPath & sDbName, _
                ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
                PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
                WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
                Connection:="Table " & varTable, _
                SQLStatement:="SELECT * FROM " & varTable, SQLStatement1:=""
           
            .EditMainDocument
        End With

 '       'Hide Access window set property to false
 '       WindowVisible(FindWindowPartial("Microsoft Access")) = False

        'Merge Document
        With wdApp.ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .MailAsAttachment = False
            .MailAddressFieldName = ""
            .MailSubject = ""
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=True
        End With
   
    MergeWordDoc = 0

Exit Function

ErrHandler:
    MsgBox Err.Description

End Function
'-----------------------------------------------------------------------------------------------------------


2. Mail merge from rs via csv

Public Function MergeDoc(adoRS As adodb.Recordset, sTempCopy, varTable As String) As Boolean
   
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer
    Dim nSec As Integer
    Dim wdDoc As New Word.Document
    Dim nflds As Integer
    Dim wdGlobVar As New Word.Global
    Dim nDocs As Integer
    Dim sData As String
    Dim bMergeFieldsFound As Boolean
    Dim nRecs As Integer
    Dim sFieldList As String
   
   
    'Can't pass RS to Word's normal mailmerge without an ODBC DSN
    'This code creates a csv data source and uses Word MailMerge to merge records to one doc?
   
   
    MergeDoc = False
   
    bError = False
   
    On Error GoTo errHandler
   
   
    If Not bAppDocOpen Then OpenDoc (sTempCopy)
       
        'Create Data Source Table in CSV file
       
        sFieldList = ""
       
        sDataDoc = Replace(sTempCopy, ".tmp", "Data.csv")
        sDataDoc = GetFileNameFromPath(sDataDoc)
        sDataDoc = sTempPath & sDataDoc
       
        bError = False
       
        'Write Csv file
        If WriteCsvFile(adoRS, sDataDoc, True) Then
   
            'Merge csv data file with doc
            With wdApp
                With .ActiveDocument.MailMerge
                    .OpenDataSource Name:=sDataDoc, _
                            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
                            PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
                            WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
                            Connection:="Table " & varTable, _
                            SQLStatement:="", SQLStatement1:=""
                    .Destination = wdSendToNewDocument
                    .MailAsAttachment = False
                    .MailAddressFieldName = ""
                    .MailSubject = ""
                    .SuppressBlankLines = True
                    With .DataSource
                        .FirstRecord = wdDefaultFirstRecord
                        .LastRecord = wdDefaultLastRecord
                    End With
                    .Execute Pause:=True
                End With
            End With
           
            oPrintOptions.ProgressBar 1
        Else
            bError = True
        End If
               
   
    If Not bError = True Then MergeDoc = True


Exit Function

errHandler:
    MsgBox Err.Description

End Function
'-----------------------------------------------------------------------------------------------------------
Function GetFileNameFromPath(sPath As String) As String
  Dim sTemp As String
  Dim nPrev As Integer
  Dim nResult As Integer
  nPrev = 1
  If InStr(1, sPath, "\") = 0 Then
    GetFileNameFromPath = sPath
    Exit Function
  End If
  Do While True
    nResult = InStr(nPrev, sPath, "\")
    If nResult > 0 Then
      nPrev = nResult + 1
    Else
      If nPrev > 1 Then
        GetFileNameFromPath = Mid$(sPath, nPrev)
        Exit Do
      End If
    End If
  Loop
End Function
'-----------------------------------------------------------------------------------------------------------
Public Function WriteCsvFile(rs As adodb.Recordset, sFileNameWithPath As String, bShowProgress As Boolean) As Boolean
'Open named txt file in path or application path write field names and data records from passed recordset and close file
   
    Dim sFieldList As String
    Dim sDataRow As String
    Dim iFile As Integer
    Dim i As Integer
    Dim j As Integer
    Dim nRecs As Integer
    Dim nflds As Integer


    WriteCsvFile = False
   
    On Error GoTo errHandler
   
    rs.MoveFirst
   
    nRecs = rs.RecordCount
    nflds = rs.Fields.Count
   
                     
    iFile = FreeFile                        ' Get unused file number
   
    Open sFileNameWithPath For Output As #iFile
   
    'Write field list
    For i = 0 To nflds - 1
        If i = 0 Then
            sFieldList = Chr(34) & Trim(rs.Fields(i).Name) & Chr(34)
        Else
            sFieldList = sFieldList & ", " & Chr(34) & Trim(rs.Fields(i).Name) & Chr(34)
        End If
    Next i
   
    Print #iFile, sFieldList & vbCrLf
   
    'Write records
    For j = 0 To nRecs - 1
        For i = 0 To nflds - 1
            If i = 0 Then
                sDataRow = Chr(34) & Trim(rs.Fields(i).Value) & Chr(34)
            Else
                sDataRow = sDataRow & ", " & Chr(34) & Trim(rs.Fields(i).Value) & Chr(34)
            End If
        Next i
        Print #iFile, sDataRow & vbCrLf
        rs.MoveNext
        If bShowProgress Then oPrintOptions.ProgressBar 1
    Next j
       
    Close #iFile
   
    WriteCsvFile = True
   
   
    Exit Function
   
errHandler:
    MsgBox "Error writing local data file"
    On Error GoTo 0
   
End Function
'-----------------------------------------------------------------------------------------------------------



0
 
LVL 1

Author Comment

by:Madmarlin
ID: 6945682
Sorry just cant seem to follow some of this..

Would it be easier if I explain a little better what i have and what I want..

Basically I have returned an ADODB.Recordset with the values I wish to merge into a document. And thats about it.

I have a template of a document that I havent set up as a mail merge document as I have NO experience in this.

Can I some how create a function(will need help with) where I can pass in the docuemnt path (once I've set up as a mail merge doc) and the recordset of values, then create the merged doc on screen..

0
 
LVL 1

Author Comment

by:Madmarlin
ID: 6947112
What declarations do I have to make, thats my problem I no very little about the word object..
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6947152
Ok, since it is not possible to directly use a recordset as mailmerge datasource even not in XP, you can follow these steps

-first set up a template, sample was given in the first link posted and if you already got one skip this step
-then write the recordset to disk as csv this will be your datasource
-then use the procedure from nickwoolley to read in the csv datasource from disk

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6947161
there's only one way of using ODBC sources directly in Word and that's through MS Query, but you have to program against that to get it to do what you need

btw here's a small piece of code to write your recordset to disk for pickup in the next step, it needs SQL statement to fill the recordset, an active connection object and a filename of your datasource

Private Sub GetDataSource(sSQL As String, sConn As String, sFile As String)
Dim oRS As ADODB.Recordset
Dim oFSO As Scripting.FileSystemObject
Dim oTSoutput As Scripting.TextStream
Dim sTemp As String

    Set oRS = New ADODB.Recordset
    oRS.Open sSQL, sConn, adOpenForwardOnly, adLockReadOnly
    sTemp = oRS.GetString(adClipString, -1, ",", vbCrLf, "")
    oRS.Close
    Set oRS = Nothing

    Set oFSO = New Scripting.FileSystemObject
    Set oTSoutput = oFSO.OpenTextFile(sFile, ForWriting, True,
TristateFalse)
    oTSoutput.Write sTemp
    oTSoutput.Close
    Set oTSoutput = Nothing
    Set oFSO = Nothing
End Sub

HTH:O)Bruintje
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:sridhar_PJ
ID: 6947321
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6947688
Do you need to do this through VB or can you do it strictly through Word?

Your options would be to go into word and use the Mail Merge wizard to choose a data source for the mailmerge document, then you can insert the fields from your recordsource into the appripriate places in your document.  Then, whenever you select Mailmerge from the menu, it will go out, run the query or open the recordsource and produce copies of the document for each row returned.  All of this is done strictly inside of Word.

Or, you can simulate mail merge, by creating a word document, for example, that looks something like this:

Invoice Date: INVOICE_DATE

Dear CUSTOMER_NAME,

bla bla bla...

Then highlight the text for INVOICE_DATE and go to the menu to create a bookmark.  You can call it INVOICE_DATE if you want.  Do the same for CUSTOMER_NAME.

Then, in your VB Code, you can open the document and insert text from your recordset into the various bookmarks and then either print the page, or save it off as a separate document.

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdBookmark As Word.Bookmark

Private Sub Command1_Click()

    Set wrdApp = New Word.Application
    Set wrdDoc = wrdApp.Documents.Open("H:\Code\bookmark.doc")
    While Not RS.EOF
        Set wrdBookmark = wrdDoc.Bookmarks("INVOICE_DATE")
        wrdBookmark.Range.Text = RS("INVOICE_DATE").Value
        Set wrdBookmark = wrdDoc.Bookmarks("CUSTOMER_NAME")
        wrdBookmark.Range.Text = RS("CUSTOMER_NAME").Value
        wrdDoc.PrintOut
        RS.MoveNext
    Wend

wrdApp.DisplayAlerts = wdAlertsNone
wrdApp.Quit
Set wrdApp = Nothing
Set wrdDoc = Nothing
Set wrdBookmark = Nothing

End Sub

I'm sure that there are ways in invoke the Word mailmerge through VB too, but I just don't know how to do it...

0
 
LVL 18

Expert Comment

by:mdougan
ID: 6961261
Hello Madmarlin, I thought this was urgent?
0
 
LVL 1

Author Comment

by:Madmarlin
ID: 6965506

Madmarlin, is away for a while.
He asked my to post this message as a friend and to let you know he will be back to deal with this later...

Madmarlin sends his appologies..
0
 
LVL 1

Author Comment

by:Madmarlin
ID: 6996367

In the end I didnt need this answer as it was proved more productive to save the document to hard disk and a referenc e to their location in the DB.

But Thanks Anyway..

Madmarlin,
please forgive the delay but I've been away for a while..
0
 

Expert Comment

by:adamjsawyerj
ID: 9680235
i have a question relating to this issue
using a vba macro in word, and the datasource being an access query, how can i check if the query returned no results, before attempting the mailmerge and getting an error (yes i have error trapped, but would rather not run the mailmerge at all if i can detect it first)
sorry to barge in here but i've got no points left
:(
thanx
adam
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now