Results 1 to 2 of 2

Thread: Call Word document (with mergefields ready) and MailMerge it from Excel (all hosted on Sharepoint)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Call Word document (with mergefields ready) and MailMerge it from Excel (all hosted on Sharepoint)

    I have to setup a series of mail merged forms for my school to invite guest speakers to our classrooms. For that, I have setup a table called Guest_Speaker_MM.
    I've already created all the merge fields and tested them using manual mail merge and they worked fine. However, when these files are shared via Sharepoint (One Drive), the mail merge always unlinks even when I made sure the filepaths are identical on all other computers.
    In frustration I looked for VBA to help me point to the proper path when I press a button on Excel.
    These are the things I have so far but I am unable to achieve anything since I'm just a beginner of VBA
    Code:
    Private Sub ReminderButton_Click()
    Dim WordObject As Object
    Dim DocSource As Object
    Dim RealWorkbookPath As String
    
    On Error Resume Next
    Set WordObject = GetObject(, "Word.Application")
    If wd Is Nothing Then
       Set WordObject = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    
    Set DocSource = WordObject.Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\05 - Reminder of Collection & Payment needed for Invited Guest Speaker.docx")
    RealWorkbookPath = ThisWorkbook.Path & "/" & ThisWorkbook.Name
    
    DocSource.MailMerge.MainDocumentType = wdFormLetters
    
    DocSource.MailMerge.OpenDataSource _
    Name:=RealWorkbookPath, _
    Connection:="Data Source=" & RealWorkbookPath & ";Mode=Read", _
    SQLStatement:="SELECT * FROM 'Guest Speakers$' where Status = Signed"
    
    Unload Me
    End Sub
    I need to achieve these functions:

    • When a button is pressed, automatically open the specified word document (it's in a sub-folder of the workbook's path)
    • Execute the MailMerge function, with the WorkSheet Name being Guest Speakers and the Table Name is Guest_Speaker_MM
    • Apply a filter, where Column Header Status = Reminder Needed (I have no idea how to execute SQL Commands here)




    Please help me

  2. #2
    New Member
    Join Date
    Apr 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Call Word document (with mergefields ready) and MailMerge it from Excel (all hosted on Sharepoint)

    Getting something like this now, which is in similar structure i suppose, but not working with error 91: "Object Variable or With block variable not set"

    Code:
    Private Sub CommandButton1_Click()
        Dim WordApp As Word.Application
        Dim OpenDocument As Word.Document
        Dim wordMailMerge As Word.Mailmerge
        Dim wordMergeFields As Word.MailMergeDataFields
        Dim wordPath As String
        Dim excelPath As String
        
        WordApp.DisplayAlerts = wdAlertsNone
        
        CurrentWorksheet = ActiveSheet.Name
        excelPath = ThisWorkbook.FullName
    
                    wordPath = ThisWorkbook.Path & "\1.2 - Guest Speaker\01 - Approved Guest Speaker Notification.docx"
                    Set WordApp = CreateObject("Word.Application")
                    Set OpenDocument = WordApp.Documents.Open(wordPath)
                    Set wordMailMerge = OpenDocument.Mailmerge
                    
                    wordMailMerge.OpenDataSource Name:=excelPath, SQLStatement:="SELECT * FROM `'Guest Speakers$'` where `Status`= 'Reminder Needed'"
                    wordMailMerge.Execute
                    OpenDocument.Close
                    WordApp.Visible = True
            
        Set wordMailMergeFields = Nothing
        Set wordMailMerge = Nothing
        Set OpenDocument = Nothing
        Set WordApp = Nothing
        WordApp.DisplayAlerts = wdAlertsAll
        
        Sheets(CurrentWorksheet).Select
    End Sub
    Last edited by herman925; Sep 17th, 2019 at 02:14 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •