How to Open Word file (already having Mailmerged details) from excel

Macropod

Retired Moderator
Also, it is weird that the produced document is a long chain of forms that are all the results of my mail merged documents.....I thought it was supposed to be like a) open the already preset document; b) find where the Excel file destination is and establish connection; c) apply the SQL conditions; d) I can use the 'Mailings' ribbon to navigate the letters one by one (coz I need to email them out)
If by that you mean you need to create a separate file for each record, see: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
 

herman925

New Member
What was the exact QueryString returned by the macro?
Code:
?activedocument.MailMerge.DataSource.Name
I:\Shared drives\EdUHK SEC Department\SEC Departmental OSP.xlsm

?activedocument.MailMerge.DataSource.ConnectString
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=I:\Shared drives\EdUHK SEC Department\SEC Departmental OSP.xlsm;Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

?activedocument.MailMerge.DataSource.querystring
SELECT * FROM `Guest Speakers$`  WHERE `Status` = 'Approved'


BTW I got it. Somehow! Except one little hiccup......the code that works below now opens 2 documents - 1 that is called 'Form Letters 1' (all MM items in one document......not what I want) and another one a proper MM document (exactly what I want)

Code:
Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document, wsName As String
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName

wsName = ActiveWorkbook.Sheets("Guest Speakers").Name

With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\01 - Approved Guest Speaker Notification.docx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=False, _
        LinkToSource:=True, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM`" & wsName & "$`" & "WHERE `Status` = 'Approved'", _
        SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      .Execute
      'Disconnect from the data source
      '.MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    '.Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Display Word and the document
  .Visible = True
End With
End Sub
 

Macropod

Retired Moderator
It 'worked' because you changed the 'WHERE' part of the query string you used between posts 17, 19 and 22, from:
WHERE `Status` = `Approved`
and:
WHERE `Status` = Approved
to:
WHERE `Status` = 'Approved'
 

herman925

New Member
It 'worked' because you changed the 'WHERE' part of the query string you used between posts 17, 19 and 22, from:
WHERE `Status` = `Approved`
and:
WHERE `Status` = Approved
to:
WHERE `Status` = 'Approved'
Yes. I noticed that now.

Thanks Paul. But just that one little issue I had. I'm not executing my code and it always opens with 2 documents. The one that joins and the one that doesn't. Can I just have the one that doesn't join?
 

herman925

New Member
Yes. I noticed that now.

Thanks Paul. But just that one little issue I had. I'm not executing my code and it always opens with 2 documents. The one that joins and the one that doesn't. Can I just have the one that doesn't join?


I think I got it. It's the execute command that 'simulates' the activation of the mail merge (joining the files into a single letter). Removing it worked fine!

Here is the code for those who wanna use it
Code:
Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document, wsName As String
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName

wsName = ActiveWorkbook.Sheets("Guest Speakers").Name

With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\01 - Approved Guest Speaker Notification.docx", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .SuppressBlankLines = False
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=False, _
        LinkToSource:=True, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM`" & wsName & "$`" & "WHERE `Status` = 'Delivered'", _
        SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      'Excecute the merge
      '.Execute
      'Disconnect from the data source
      '.MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    '.Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Display Word and the document
  .Visible = True
End With
End Sub
Thank you Paul. This isn't the first time you've helped me out, actually. Thank you very much
 
Last edited:

Macropod

Retired Moderator
You end up with two open documents because you've commented-out the code that closes the mailmerge main document...
 
Last edited:

Macropod

Retired Moderator
I think I got it. It's the execute command that 'simulates' the activation of the mail merge (joining the files into a single letter). Removing it worked fine!
Do NOT do that!!! You'll inevitably lose the merged data if you do.
 

herman925

New Member
You end up with two open documents because you've commented-out the code that closes the mailmerge main document...
Yes because I figured that I should comment it out so that I won't close the mailmerge main document - which is the one I want only....
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top