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

[gssachin]

hi,

I Created one master data in excel which I already link to word file through mailmerged. Now I want to Open that document through excel macro. I created following but it's not working as I required (it open only 1 merged record, I want all records should shown in file when I run macro to open that file) .

Sub marco1()
Dim app As Object
Set app = CreateObject("Word.Application")
app.Documents.Open ("\\Sachin\c\CERTIFICATE\Loan from Bank.doc")
app.Application.DisplayAlerts = None
Visible = True


End Sub

Thanks in advanced.

 

[Macropod]

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]

What was the exact QueryString returned by the macro?

?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)

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]

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]

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]

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

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

 

[Macropod]

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

 

[Macropod]

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]

Do NOT do that!!! You'll inevitably lose the merged data if you do.

Oh no. So what should I do? I'm really lost now

 

[herman925]

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....

 

[Macropod]

Oh no. So what should I do? I'm really lost now

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....

You really should have left the code alone... instead of commenting-out and deleting lines you don't understand.