Last Call on Mail Merge...any takers!

  • Thread starter Thread starter mission2java_78
  • Start date Start date
M

mission2java_78

Guest
Ok last call here....

Does anyone have an example..of basically a template file in word..and creating a mail merge...meaning a small say customer database...and it sends its data to the word file. I cant find any good examples of mail merges..most of them make you go into word directly and create a data source. I need code to send data from the db query / table into the word document based on a template.

Anyone with examples please post!!!!!
=)
Thanks,.
 
Okay so maybe this is a daft idea. Its nearly home time and the brain is a bit dead.

Instead of using a mail merge in word

You have all the data elements you want to merge in the database I presume so the only thing you are lacking is the standard texty bits

So create a report in Access and output the whole lot from there onto headed paper if you want to or create your own headed paper in the report.

Best I could do

Len B
 
I cannot just output to a word document...
I need to output to specified fields...

so...

Almost there...I can open word..but how do I take fields from the query into say my field { MERGEFIELD CUSTOMER }.
In the word template it looks like <CUSTOMER>.

So heres what I have right now:

Code: 
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Query1")
rs.MoveFirst

  Dim docs As Word.Documents
   Dim strWordTemplate As String
   Dim strDocsPath As String
   Dim strTemplatePath As String
   Dim prps As Object
   Dim strShortDate As String
   Dim strLongDate As String
   Dim strTest As String
   Dim strAddress As String
   Dim strCountry As String
   Dim strSaveName As String
   Dim strTestFile As String
   Dim intSaveNameFail As Boolean
   Dim i As Integer
   Dim strSaveNamePath As String
   
   'Set global Word application variable; if Word is not running,
   'the error handler defaults to CreateObject
   Set pappWord = GetObject(, "Word.Application")
   
   strLongDate = Format(Date, "mmmm d, yyyy")
   strShortDate = Format(Date, "m-d-yyyy")
   strSaveName = "SampleFBDS403 " & rs("CustomerName").Value & " "
   strSaveName = strSaveName & " on " & strShortDate & ".doc"
   strDocsPath = "c:\temp\"
   strTemplatePath = "c:\temp" 'pappWord.Options.DefaultFilePath(wdUserTemplatesPath)
   strWordTemplate = strTemplatePath & "\" & "FBDS403Template.dot"
   
   'Check for existence of template in template folder,
   'and exit if not found
   strTestFile = Nz(Dir(strWordTemplate))
   Debug.Print "Test file: " & strTestFile
   If strTestFile = "" Then
      MsgBox strWordTemplate & " template not found; can't create letter"
      GoTo ErrorHandlerExit
   End If
   
   'Check for existence of previously saved letter in documents folder,
   'and append an incremented number to save name if found
   i = 2
   intSaveNameFail = True
   Do While intSaveNameFail
      strSaveNamePath = strDocsPath & strSaveName
      Debug.Print "Proposed save name and path: " _
         & vbCrLf & strSaveNamePath
      strTestFile = Nz(Dir(strSaveNamePath))
      Debug.Print "Test file: " & strTestFile
      If strTestFile = strSaveName Then
         Debug.Print "Save name already used: " & strSaveName
         
         'Create new save name with incremented number
         intSaveNameFail = True
         strSaveName = "Letter " & CStr(i) & " to " & _
            Me![FirstName] & " " & Me![LastName]
         strSaveName = strSaveName & " on " & strShortDate & ".doc"
         strSaveNamePath = strDocsPath & strSaveName
         Debug.Print "New save name and path: " _
            & vbCrLf & strSaveNamePath
         i = i + 1
      Else
         Debug.Print "Save name not used: " & strSaveName
         intSaveNameFail = False
      End If
   Loop
   
   Set docs = pappWord.Documents
   docs.Add strWordTemplate
   
   Set prps = pappWord.ActiveDocument.CustomDocumentProperties
   prps.Item("Customer").Value = rs("CustomerName").Value
   prps.Item("Site").Value = rs("Sites").Value
   prps.Item("Address").Value = rs("ShipToSiteAddress1").Value
   prps.Item("Commission").Value = rs("Commission").Value
   prps.Item("ShortDescription").Value = rs("Change").Value
   prps.Item("Reason").Value = rs("Reason").Value
   prps.Item("AdditionalInformation").Value = rs("AdditionalInformation").Value
   
   With pappWord
      .Visible = False
      .Selection.WholeStory
      .Selection.Fields.Update
      MsgBox "Going to save as " & strSaveNamePath
      .ActiveDocument.SaveAs strSaveNamePath
      '.Activate
      If (MsgBox("Open the word document file?", vbYesNo, "Open Document " & strSaveNamePath & "?") = vbYes) Then
        .Visible = True
        .Activate
      Else
        .Visible = False
      End If
      
      .Selection.EndKey Unit:=wdStory
   End With
   
   
ErrorHandlerExit:
   Set pappWord = Nothing
   Exit Sub

ErrorHandler:
   'Word is not running; open Word with CreateObject
   If Err.Number = 429 Then
      Set pappWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

It errors out on this line:
prps.Item("CUSTOMER").Value = rs("CustomerName").Value
Im guessing im setting prps to the wrong thing...basically I want to say take the customer name from the query and store it in the word document <<CUSTOMER>>
which is in my template as { MERGEFIELD Customer }.


Maybe this is wrong:
Set prps = pappWord.ActiveDocument.CustomDocumentProperties

????


Anyone ?

Thanks,
Jon
 
Last edited:
I'm not at all convinced that you can write into the Word field like that. The mailmerge in Word works by reading the data and creating a copy of your master document with the data in place. The method I posted creates a .txt file and Word reads the data from that to carry out the merge.
 

Attachments

neileg said:
I'm not at all convinced that you can write into the Word field like that. The mailmerge in Word works by reading the data and creating a copy of your master document with the data in place. The method I posted creates a .txt file and Word reads the data from that to carry out the merge.
Click to expand...

Look closely...that data goes into a new .doc.

Jon
 
I got it...i had to create my user defined fields in word.

Code: 
Private Sub Command0_Click()
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Query1")
rs.MoveFirst

  Dim docs As Word.Documents
   Dim strWordTemplate As String
   Dim strDocsPath As String
   Dim strTemplatePath As String
   Dim prps As Object
   Dim strShortDate As String
   Dim strLongDate As String
   Dim strTest As String
   Dim strAddress As String
   Dim strCountry As String
   Dim strSaveName As String
   Dim strTestFile As String
   Dim intSaveNameFail As Boolean
   Dim i As Integer
   Dim strSaveNamePath As String
   
   'Set global Word application variable; if Word is not running,
   'the error handler defaults to CreateObject
   Set pappWord = GetObject(, "Word.Application")
   
   strLongDate = Format(Date, "mmmm d, yyyy")
   strShortDate = Format(Date, "m-d-yyyy")
   strSaveName = "SampleFBDS403 " & rs("CustomerName").Value & " "
   strSaveName = strSaveName & " on " & strShortDate & ".doc"
   strDocsPath = "c:\temp\"
   strTemplatePath = "c:\temp" 'pappWord.Options.DefaultFilePath(wdUserTemplatesPath)
   strWordTemplate = strTemplatePath & "\" & "FBDS403Template.dot"
   
   'Check for existence of template in template folder,
   'and exit if not found
   strTestFile = Nz(Dir(strWordTemplate))
   Debug.Print "Test file: " & strTestFile
   If strTestFile = "" Then
      MsgBox strWordTemplate & " template not found; can't create letter"
      GoTo ErrorHandlerExit
   End If
   
   'Check for existence of previously saved letter in documents folder,
   'and append an incremented number to save name if found
   i = 2
   intSaveNameFail = True
   Do While intSaveNameFail
      strSaveNamePath = strDocsPath & strSaveName
      Debug.Print "Proposed save name and path: " _
         & vbCrLf & strSaveNamePath
      strTestFile = Nz(Dir(strSaveNamePath))
      Debug.Print "Test file: " & strTestFile
      If strTestFile = strSaveName Then
         Debug.Print "Save name already used: " & strSaveName
         
         'Create new save name with incremented number
         intSaveNameFail = True
         strSaveName = "Letter " & CStr(i) & " to " & _
            rs("CustomerName").Value
         strSaveName = strSaveName & " on " & strShortDate & ".doc"
         strSaveNamePath = strDocsPath & strSaveName
         Debug.Print "New save name and path: " _
            & vbCrLf & strSaveNamePath
         i = i + 1
      Else
         Debug.Print "Save name not used: " & strSaveName
         intSaveNameFail = False
      End If
   Loop
   
   Set docs = pappWord.Documents
   docs.Add strWordTemplate
   
   Set prps = pappWord.ActiveDocument.CustomDocumentProperties
   prps.Item("Customer").Value = Nz(rs("CustomerName").Value)
   prps.Item("Site").Value = Nz(rs("Sites").Value)
   prps.Item("Address").Value = Nz(rs("ShipToSiteAddress1").Value)
   prps.Item("Commission").Value = Nz(rs("Commission").Value)
   prps.Item("ShortDescription").Value = Nz(rs("Change").Value)
   prps.Item("Reason").Value = Nz(rs("Reason").Value)
   prps.Item("AdditionalInformation").Value = Nz(rs("AdditionalInformation").Value)
   
   With pappWord
      .Visible = False
      .Selection.WholeStory
      .Selection.Fields.Update
      MsgBox "Going to save as " & strSaveNamePath
      .ActiveDocument.SaveAs strSaveNamePath
      '.Activate
      If (MsgBox("Open the word document file?", vbYesNo, "Open Document " & strSaveNamePath & "?") = vbYes) Then
        .Visible = True
        .Activate
      Else
        .Visible = False
      End If
      
      .Selection.EndKey Unit:=wdStory
   End With
   
   
ErrorHandlerExit:
   Set pappWord = Nothing
   Exit Sub

ErrorHandler:
   'Word is not running; open Word with CreateObject
   If Err.Number = 429 Then
      Set pappWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub
 
just ran into an issue...
I had to add these fields from the word template
At the top I went to File->Properties and I tabbed over to:

"Custom"

I then added the fields I wanted and labeled them as text. Now I pushed my data from access to this word document but I noticed most of hte memo fields were getting cut off short. Then I counted the characters in the word template for each of these fields and noticed they only allowed for 127 characters. So I guess it only allows for 1 byte of data (128-1 bits in size). Is there any way to change this? I need to allow memo fields to be entered...

I have attached the sample database that works..make sure you store the template file in C:\Temp. Anyone is free to use the code.

P.S The fields that dont fit are the additional information and revision fields....


Thanks,
Jon
 

Attachments

This is what I did and it work great:

behind a command button -

I run a query that makes a table with the data I want to use in my merge document - then I hyperlink to the Word document:
me.cmdGo.HyperlinkAddress = "location of document"

This opens the Word document.

In Word, link your document to the Access DB/table. In 97 I did an ODBC connect - in 2000 I used Open Date Source just follow the promts.

Works really slick.

Good Luck

:)
 
You must log in or register to reply here.

Similar threads

Dick7Access
Font Size
Replies
5
Views
464
Dick7Access
Dick7Access
R
Using Outlook, Word and Access to send bulk emails.
2
Replies
22
Views
1,235
Silversee
S
A
Problem with Word mail merge from query
Replies
8
Views
352
Al Kramer
A
Juice
Printing html file to a specific printer
Replies
4
Views
380
arnelgp
arnelgp
A
Mail merge query with multiple records
Replies
10
Views
733
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom