Results 1 to 3 of 3
  1. #1
    huongdl1987 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    26

    VBA mailmerge from access 2010 with different template.

    Hi,

    I'm trying to do mail merge from Access. My code works fine. The only problem is I have 3 different templates to use (introductory letters, thank you letters). I created a combo box, choose a template. What i want is after clicking on the command button, ACCESS automatically mail merge with the chosen the template. How do I modify my code so it would work?

    Thank you,
    Meo


    Code:
    Option Compare Database
    Public Function CreateWordLetter(strDocPath As String)
        'if no path is passed to function, exit
        
        If IsNull(strDocPath) Or strDocPath = "" Then
            Exit Function
        End If
        
        Dim dbs As Database
        Dim objWord As Object
        Dim PrintResponse
        
        Set dbs = CurrentDb
        
        'create reference to Word Object
        
        Set objWord = CreateObject("Word.Application")
        
        'Word Object is created - now let's fill it with data
        
        With objWord
            .Visible = True
            .Documents.Open (strDocPath)
            
            'move to each bookmark, and insert correct text.
            .ActiveDocument.Bookmarks("firstname").Select
            .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomFirstName))
                    
            .ActiveDocument.Bookmarks("address").Select
            .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomAddress))
            
            .ActiveDocument.Bookmarks("city").Select
            .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomCity))
            
            .ActiveDocument.Bookmarks("state").Select
            .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomState))
            
            .ActiveDocument.Bookmarks("zip").Select
            .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomZip))
            
            .ActiveDocument.Bookmarks("firstname2").Select
            .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomFirstName))
        End With
        
        'find out if the user would like to print the document at this time.
        
        PrintResponse = MsgBox("Print this document?", vbYesNo)
        If PrintResponse = vbYes Then
            objWord.ActiveDocument.PrintOut Background:=False
        End If
        
        'release all objects
        
        Set objWord = Nothing
        Set dbs = Nothing
        
    End Function
        
               
    Private Sub cmdMailMerge_Click()
        CreateWordLetter "Z:\Studies\Cleft Utah New CDC Study\Utah mother file\IntroCase1.docx"
        
    End Sub


  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Perhaps you need a case select statement to let Word know which letter to select.

    http://word.tips.net/T000130_Underst...Structure.html

    This might help

  3. #3
    huongdl1987 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Posts
    26
    I didn't use the select case statement. Instead, I formed a table with a Value field and make the buttonclick choose the path based on the Value. However, for some reasons, the bookmarks wouldn't work. For example, I have 7 bookmarks for all 5 templates, but each template don't use all of them. When I ran mail merged program, ACCESS kept replacing the last bookmark used in the template with the last bookmark stated in the code, even if the bookmark is not used in this template. Any idea on how to fix this?

    Here is my code
    Code:
    Option Compare Database
    Public Function CreateWordLetter(strDocPath As String)
        'if no path is passed to function, exit
        
        If IsNull(strDocPath) Or strDocPath = "" Then
            Exit Function
        End If
        
        Dim dbs As Database
        Dim objWord As Object
        Dim PrintResponse
        
        Set dbs = CurrentDb
        
        'create reference to Word Object
        
        Set objWord = CreateObject("Word.Application")
        
        'Word Object is created - now let's fill it with data
        
        With objWord
            .Visible = True
            .Documents.Open (strDocPath)
            
            'move to each bookmark, and insert correct text.
            .ActiveDocument.Bookmarks("firstname").Select
            .Selection.Text = (CStr(Forms!MailMerge!strMomFirstName))
            On Error Resume Next
                            
            .ActiveDocument.Bookmarks("address").Select
            .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomAddress))
            On Error Resume Next
            
            .ActiveDocument.Bookmarks("city").Select
            .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomCity))
            On Error Resume Next
            
            .ActiveDocument.Bookmarks("state").Select
            .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomState))
            On Error Resume Next
            
            .ActiveDocument.Bookmarks("zip").Select
            .Selection.Text = (CStr(Forms!MailMerge!UpdatedMomZip))
            On Error Resume Next
            
            .ActiveDocument.Bookmarks("firstname2").Select
            .Selection.Text = (CStr(Forms!MailMerge!strMomFirstName))
            On Error Resume Next
            
            .ActiveDocument.Bookmarks("lastname").Select
            .Selection.Text = (CStr(Forms!MailMerge!strMomLastName))
            On Error Resume Next
            
        End With
        
        'find out if the user would like to print the document at this time.
        
        PrintResponse = MsgBox("Print this document?", vbYesNo)
        If PrintResponse = vbYes Then
            objWord.ActiveDocument.PrintOut Background:=False
        End If
        
        'release all objects
        
        Set objWord = Nothing
        Set dbs = Nothing
        
    End Function
              
    Private Sub cbxTemplate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    End Sub
    Private Sub cmdMailMerge_Click()
        If Not Len(cbxTemplate.Value) = 0 Then
            CreateWordLetter (cbxTemplate.Value)
        Else
            MsgBox "Select Mail Merge process"
        End If
               
        If cbxTemplate.Value = "Z:\Studies\Cleft Utah New CDC Study\Utah mother file\ThankYou.docx" Then
                DateofThankYouLetterSent = Date
                TrackingStatus = 8
            Else
                DateofLetterSent = Date
                TrackingStatus = 2
        End If
        
    End Sub
    
    Private Sub Combo121_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MailMerge Problem from Access Query
    By txacoli in forum Access
    Replies: 12
    Last Post: 04-12-2012, 06:08 AM
  2. Mailmerge: Making a list - Access to word
    By Ribido in forum Access
    Replies: 3
    Last Post: 01-07-2012, 12:40 PM
  3. vba code to populat word template in Access 2010
    By graffjohn in forum Programming
    Replies: 2
    Last Post: 06-11-2011, 09:36 PM
  4. Replies: 0
    Last Post: 01-20-2011, 01:24 PM
  5. mailmerge through Access 2007
    By malcolm.wilcock@tesco.net in forum Access
    Replies: 4
    Last Post: 03-03-2010, 08:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums