Results 1 to 3 of 3
  1. #1
    sjhime is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    9

    Access VBA Mailmerge Word Document

    Ok. Not sure what's going on here. I'm trying to initiate a mailmerge from Access VBA and when the Word mailmerge document is opened, the user get's asked to choose the query for the mailmerge, even though the connection is set in VBA and is attached as a source in the word document. Here's the code:



    Code:
    Response = MsgBox("Would you like to email the coordinator with the final thank you?", vbYesNo, "Final Survey Thank You?")
        'need to add something if No selected saying tbl3Notifications.ClassFormReceived will not be updated
        If Response = vbYes Then
    
    
            DoCmd.OpenQuery "qry_FinalSurveyTY", , acEdit
           
        
                    'Code to open a word document.
                       Dim LWordDoc As String
                       Dim oApp As Object
                       Dim Email As String
                       Dim MyRs As Recordset
                       Dim EmailSubject As String
                       Dim ConsentPth As String
                       Dim myItem As Outlook.Attachments
            
                        
                       Set MyRs = db.OpenRecordset("qry_FinalSurveyTY")
                       
                       If (MyRs.RecordCount = 0) Then
                            MsgBox "No records in Query", vbOKOnly, "No Records"
                            Exit Sub
                        Else
                               MyRs.MoveFirst
                               'Path to the word document
                               Set recb = db.OpenRecordset("SELECT * FROM tbl_Classrooms WHERE SchoolID = " & Company_ID)
    
    
                               If recb("Survey_Type") = 2 Then
                                   LWordDoc = "O:\PNA\2018 PNA\Correspondence\Surveys Returned Thank You - Online.docx"
                               ElseIf recb("Survey_Type") = 1 Then
                                   LWordDoc = "O:\PNA\2018 PNA\Correspondence\Surveys Returned Thank You - Paper.docx"
                               End If
                            
                               If Dir(LWordDoc) = "" Then
                                  MsgBox "Document not found."
                            
                                   Else
                                      'Create an instance of MS Word
                                    On Error Resume Next
                                          Set oApp = GetObject(LWordDoc, "Word.Application")
                                            If Err.Number <> 0 Then
                                                Set oApp = CreateObject(Class:="Word.Application")
                                                'Open the Document
                                                oApp.Documents.Open FileName:=LWordDoc
                                            End If
                                    On Error GoTo 0
                                        With oApp
                                          .Visible = True
                                
                                      'Open the Document
                                      'oApp.Documents.Open FileName:=LWordDoc
                                      With oApp.ActiveDocument.MailMerge
    '                                    .MainDocumentType = wdLetter
                                        .OpenDataSource Name:="O:\PNA\Database\Recruitment Database.accdb", ConfirmConversions:=False, _
                                            ReadOnly:=False, LinkToSource:=True, Connection:="QUERY qry_FinalSurveyTY"
                                        .Destination = wdSendToNewDocument
                                        .Execute
                                        '.Close wdDoNotSaveChanges
                                        '.Quit SaveChanges:=False
                                      End With
                                      'oApp.ActiveDocument.Close wdDoNotSaveChanges
                                    End With
                                      
                                      Set objOutlook = CreateObject("Outlook.Application")
                                      Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
                                      Set myAttachments = objOutlookMsg.Attachments
            
                                      Email = MyRs![EmailAddress]
                                      EmailSubject = MyRs![Email_Subject]
                                      CC = MyRs![ccEmail]
                                      'ConsentPth = MyRs![ConsentPth]
                                      
                                      With objOutlookMsg
                                        .Display
                                        .To = Email
                                        .CC = CC
                                        .Subject = EmailSubject
                                        .Body = oApp.ActiveDocument.Content
                                        '.Attachments.Add "Add any attachment paths here"
                                        '.Send
                                       End With
                                    
                                    Set objOutlookMsg = Nothing
                                    Set objOutlook = Nothing
                
                               End If
                            End If
                        
                rec.Edit
                rec("FinalThankYou") = True
                rec.Update
                
                DoCmd.Close acQuery, "qry_FinalSurveyTY"
                With oApp
                   .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
                End With
                Set rec = Nothing
                Set recb = Nothing
                Set db = Nothing
                Set oApp = Nothing
        End If

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    are you sure its supposed to be: "QUERY qry_FinalSurveyTY"
    rather than:
    "qry_FinalSurveyTY"


  3. #3
    sjhime is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    9
    Actually, I've tried both, with the same result.


    Quote Originally Posted by ranman256 View Post
    are you sure its supposed to be: "QUERY qry_FinalSurveyTY"
    rather than:
    "qry_FinalSurveyTY"


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

Similar Threads

  1. Automatically running Word Mailmerge from Access VBA
    By pledbetter in forum Programming
    Replies: 13
    Last Post: 04-14-2018, 05:06 PM
  2. MS Access and MS Word MailMerge
    By tgall in forum Access
    Replies: 1
    Last Post: 11-14-2015, 11:02 PM
  3. Access and Word MailMerge
    By saharmon in forum Access
    Replies: 0
    Last Post: 10-28-2014, 02:37 PM
  4. Access and mailmerge in Word
    By Popnorth in forum Access
    Replies: 2
    Last Post: 01-21-2013, 08:25 AM
  5. Mailmerge: Making a list - Access to word
    By Ribido in forum Access
    Replies: 3
    Last Post: 01-07-2012, 12:40 PM

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