Solved

Automate Mail Merge Within Access 2010

Posted on 2014-11-14
7
680 Views
Last Modified: 2014-12-04
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
Comment
Question by:mlcktmguy
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40444211
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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40452350
Thank you, I got unexpectently called away and probably won't get to try this until at least next week.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40463610
@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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40463996
@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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40468356
Thanks for the responses, I should get to work on this again very soon.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 40473746
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40474171
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

19 Experts available now in Live!

Get 1:1 Help Now