Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1047
  • Last Modified:

Automate Mail Merge Within Access 2010

Been using Access for a very long time but this is the first time I want to do a mail merge.

I would like everything to work from within Access using VBA Code.

For testing purposes I create a Mail Merge document called 'MailMergeTest.docx' in directory 'C:\My Documents\Word Documents'.

It is linked to 'tblProperty' in my Access database.  I can run it manually from Word 2010 but want the output generated automatically from within Access, when the user requests.

How do I accomplish that?
0
mlcktmguy
Asked:
mlcktmguy
  • 3
  • 2
  • 2
1 Solution
 
bonjour-autCommented:
it takes some efforts;
lets assume you have prepared a word document with some bookmarks you want to replace by code.
you need to operate the complete word operations - 1. open the template.doc,2.  replace the bookmarks with variable data, 3. save the document with valid name

example code:

Private Sub cmdDoc_Click()
 
  Dim myTemplate, SName As String
  Dim wdAnw As Word.Application
  Dim wdDok As Word.Document
  Dim myBk As Word.Bookmark

 
  Set wdAnw = CreateObject("Word.Application")
  myTemplate = "someTemplate.dot" 'enter your template name
  Set wdDok = wdAnw.Documents.Add(Template:=myTemplate)
 
  If Nz(wdDok.Bookmarks.Count, 0) = 0 Then Exit Sub
 
  For Each myBk In wdDok.Bookmarks
    wdDok.Bookmarks(myBk).Range.Text = DLookup("myContent", "Dokument", "myMark='" & myBk.Name & "'")
  Next

  SName = "someFilepath&Name"
  pubDocFile = SName
  wdAnw.ActiveDocument.SaveAs (SName)
  wdAnw.Quit
  Set wdAnw = Nothing
  Application.FollowHyperlink (SName)
 
End Sub

!!! dont forget to add Ms-Word to the VBA references
0
 
mlcktmguyAuthor Commented:
Thank you, I got unexpectently called away and probably won't get to try this until at least next week.
0
 
Nick67Commented:
@bonjour-aut
Is that a mail merge?
My code for mail merge looks very different

       
'open the mergedoc
        Dim oApp As Object
        Dim MlMrge As Object
        Set oApp = CreateObject(Class:="Word.Application")
        oApp.Visible = True
        
        'this will open a new document based on the path as a template.  Excellent
        'but it doesn't open it as a mail merge
        oApp.Documents.Add "C:\My Documents\Word Documents\MailMergeTest.docx "
        ' so start a merge
        Set MlMrge = oApp.ActiveDocument.MailMerge
        With MlMrge
            'do the merge, get the datasource -- and be tricky if the Access app is opened exclusively!
            .OpenDataSource Name:="c:\temp\myapp.mdb", _
 LinkToSource:=True, AddtoRecentFiles:=False, _
 Connection:="TABLE [tblProperty]" ', _
 SQLStatement:="SELECT * FROM [tblProperty]"
             'show the field values
            .ViewMailMergeFieldCodes = 9999998 'wdToggle
            'merge to a new document
             'do you want merge to email instead
            .Destination = 0 'wdSendToNewDocument
            .SuppressBlankLines = True
            'blow through all the records
            With .DataSource
                .FirstRecord = 1 'wdDefaultFirstRecord
                .LastRecord = -16 'wdDefaultLastRecord
            End With
            .Execute (False) 'execute and don't stop for errors -- list them in a new Word document, if any.
        End With

Open in new window

This is merging one new document per record in the table -- so be prepared for a blizzard of Word documents!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bonjour-autCommented:
@Nick67

Your argument, that the example code is not a classical mail merge is correct. It just deals with the Q of variable data in word documents.
I felt, that to solve the task in clearly seperated steps will be beneficial for mlcktmguy.
For final solution of sending the mails, there are at least 3 different methods possible, depending on what mail-server infrastructure is desired/available:
1. You want to use MS-Outlook available on the same machine, as the application is running - You will have all Mails sent in the sent-folder. If you do not plan for a follow up, this may be disturbing.
2. You want to use the CDO - component, which directly works with the SMTP server (http://msdn.microsoft.com/en-us/library/ms526318%28v=exchg.10%29.aspx)
3. You want to use some web-service, either commercial or a own PHP script.

My clients love CDO version for informing their customers on new offerings.
0
 
mlcktmguyAuthor Commented:
Thanks for the responses, I should get to work on this again very soon.
0
 
mlcktmguyAuthor Commented:
Nick67:  I will be executing the mail merge from an .accdb that includes 'tblProperty' as a linked table.

What does the '.OpenDataSource'  look like in that scenario?
0
 
Nick67Commented:
I think I wrote it in correctly, but not as an accdb
.OpenDataSource Name:="c:\temp\myapp.mdb", _
 LinkToSource:=True, AddtoRecentFiles:=False, _
 Connection:="TABLE [tblProperty]" ', _
 SQLStatement:="SELECT * FROM [tblProperty]"

Alter as required in bold
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now