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



## gssachin (Nov 6, 2016)

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 (Nov 6, 2016)

If the document you're opening is the mailmerge main document, rather than a file produced by a mailmerge, you don't even have one record - all you have is a mailmerge preview that has been disconnected from the data source. A mailmerge must be executed before a document with all records can come into existence. As it is, if your document is the mailmerge main document, you'd normally be getting an SQL prompt when you open it. Until that is answered, the merge can't execute. However, using Application.DisplayAlerts = False (not None) both prevents the SQL prompt and causes Word to disconnect it from the data source. Consequently, you need to both add the code to turn your document back into a mailmerge main document, supply the SQL string, then execute the merge. Try something along the lines of the following. As you'll see, it's a whole lot more involved than what you have.

```
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
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open("\\Sachin\c\CERTIFICATE\Loan from Bank.doc", _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, 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 `Sheet1$`", _
        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
```


----------



## gssachin (Nov 6, 2016)

Thanks for reply.

But I m getting following error 
Compile Error:
User-defined type not defined

For your Ref : I Have office 2007 on my PC, I m using Excel File 2007 & Output File Word 2003


----------



## Macropod (Nov 6, 2016)

Did you read the line above the one that gave the error? - the comment line that says:
'Note: A VBA Reference to the Word Object Model is required, via Tools|References


----------



## gssachin (Nov 6, 2016)

Dear Sir,

Sorry I read the line but not understand. Since I m new in macro I'm unaware of VBA Reference, 

I went to Tool > Reference but still not found. Is there any way to download it from internet ??? 

Please guide me


----------



## Macropod (Nov 6, 2016)

No, you cannot download it from the internet - it's part of Office, which you already have. Go to Tools|References, then scroll down till you find the entry for the Microsoft Word object library and click on the checkbox.

Note also that the code assumes your data are on a worksheet named Sheet1. If your worksheet has a different name change Sheet1 in the code to that name. You'll see it on the line:
SQLStatement:="SELECT * FROM `Sheet1$`", _


----------



## gssachin (Nov 7, 2016)

Sir I got it. Thanks a lot

One help After opening word document I want to run Macro1 (word macro). Can this process also included in above macro ???


----------



## Macropod (Nov 7, 2016)

It would probably be simpler to incorporate the code into the code I provided. That way, you can more easily control whether it runs before/after re-establishing the mailmerge connection or, perhaps, on the output document instead of the mailmerge main document.


----------



## gssachin (Nov 7, 2016)

I have following macro in word document. to change font of whole document also to I want date format 07th November 2016 (in that "th" is superscript) due to mailmerge I m not getting so done following trick to find & replace the word

How to add this in above macro ???

Sub FindAndReplacedate()


Application.EnableCancelKey = xlDisabled
     Selection.WholeStory
    Selection.Font.Name = "Times New Roman"


With Selection.Find
  .Text = "s--t"
  .Replacement.Text = "ST"
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchWildcards = False
  .MatchSoundsLike = False
  .MatchAllWordForms = False
  .Replacement.Font.Superscript = True
  .Execute Replace:=wdReplaceAll
  .MatchWildcards = True
  .Execute Replace:=wdReplaceAll


.Text = "t--h"
.Replacement.Text = "TH"
.Execute Replace:=wdReplaceAll


.Text = "r--d"
.Replacement.Text = "RD"
.Execute Replace:=wdReplaceAll


.Text = "n--d"
.Replacement.Text = "ND"
.Execute Replace:=wdReplaceAll




End With
End Sub


----------



## Macropod (Nov 7, 2016)

You should not be using a macro to change the font - you should be doing that _once_, in the mailmerge main document directly and preferably by editing the relevant Styles.

As for your Find/Replace code, none of that is needed if your mailmerge main document uses a suitably-coded field for the date display. To see how to do this and just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
Microsoft Word Date Calculation Tutorial | Windows Secrets Lounge
or:
Graham Mayor - Downloads
In particular, look at the item titled 'Insert A Date with Ordinal Numbering'. Do read the document's introductory material.


----------



## gssachin (Nov 6, 2016)

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.


----------



## herman925 (Sep 17, 2019)

Macropod said:


> If the document you're opening is the mailmerge main document, rather than a file produced by a mailmerge, you don't even have one record - all you have is a mailmerge preview that has been disconnected from the data source. A mailmerge must be executed before a document with all records can come into existence. As it is, if your document is the mailmerge main document, you'd normally be getting an SQL prompt when you open it. Until that is answered, the merge can't execute. However, using Application.DisplayAlerts = False (not None) both prevents the SQL prompt and causes Word to disconnect it from the data source. Consequently, you need to both add the code to turn your document back into a mailmerge main document, supply the SQL string, then execute the merge. Try something along the lines of the following. As you'll see, it's a whole lot more involved than what you have.
> 
> ```
> Sub DoMailMerge()
> ...



Dear sir I'm unable to get past the error 91 "*Object variable or With block variable not set"*. How do I circumvent that?


----------



## Macropod (Sep 17, 2019)

Fairly obviously, I would have thought, you need to change "\\Sachin\c\CERTIFICATE\Loan from Bank.doc" to match your own situation. You may also need to change the sheet name (i.e. 'Sheet1') in "SELECT * FROM `Sheet1$`".


----------



## herman925 (Sep 23, 2019)

Macropod said:


> Fairly obviously, I would have thought, you need to change "\\Sachin\c\CERTIFICATE\Loan from Bank.doc" to match your own situation. You may also need to change the sheet name (i.e. 'Sheet1') in "SELECT * FROM `Sheet1$`".



Dear Paul, yes indeed I did that.....even before I posted it. It just wouldn't work. It's very weird. This is what I got below, with the error code still persistent:

BTW for the context of which I need to achieve:
1) The Word file is contained in a sub-folder of where the Excel file's is, hence I used 'ThisWorkbook.path'
2) I pointed the SQL statement to a Worksheet in the Excel file called 'Guest Speakers'
3) I need to also allow it to filter for a column name called 'Status', where it should = 'Signed'

But the error still persists


```
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
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
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
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, 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 `Guest Speakers$` WHERE `Status` = `Signed`", _
        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 (Sep 23, 2019)

In Word, connect the document to the datasource so it becomes a true mailmerge mail document, then apply the required filter. Having done that, run the following macro and check the output against your code:

```
Sub Test()
With ActiveDocument.MailMerge
  If .MainDocumentType <> wdNotAMergeDocument Then
    MsgBox "Mail Merge Data Source Name:" & vbCr & .DataSource.Name
    MsgBox "Mail Merge Connect String:" & vbCr & .DataSource.ConnectString
    MsgBox "Mail Merge Query String:" & vbCr & .DataSource.QueryString
  Else
    MsgBox "Not A Merge Document"
  End If
End With
End Sub
```


----------



## herman925 (Sep 23, 2019)

Macropod said:


> In Word, connect the document to the datasource so it becomes a true mailmerge mail document, then apply the required filter. Having done that, run the following macro and check the output against your code:
> 
> ```
> Sub Test()
> ...



Thanks Paul. Having checked that I have a few findings:
1) A bunch of the Jet OLEDB is missing. I guess it's intentional?
2) I got the wrong SQL filter. But it seems to not affect anything. Changed it and the error persists
3) The only thing that's possible is that the Excel file is on a business OneDrive folder. When I use the Immediate in VBA using '?ThisWorkBook.fullname', this is the output:

```
?ThisWorkbook.FullName
https://uedhk.sharepoint.com/sites/sec-resources/Shared Documents/01 - General Office/SEC Departmental OSP.xlsm
```

But instead, from your shared 'Test' macro, it returns the Local drive (D:\One Drive\The Education University of Hong Kong\EdUHK SEC Department - Documents\01 - General Office\SEC Departmental OSP.xlsm)



When I press debug, the VBA engine points to the  With .MailMerge    section. It's probably due to something I did, and probably nothing about the properties of the .mailmerge submembers


----------



## Macropod (Sep 23, 2019)

AFAIK, mailmerge doesn't work with data files in sharepoint folders.


----------



## herman925 (Sep 23, 2019)

Macropod said:


> AFAIK, mailmerge doesn't work with data files in sharepoint folders.



But the file is synced locally

Also, just to make sure I didn't make that statement out of no where, I also tried to do it locally. I copied the entire OneDrive synced file into Drive D, and tried again:

This time something weird happened. The error didn't come up BUT I'm stuck at this weird 'Select Table' prompt. I think it's coz I did something wrong in the SQL statement but I can't figure out why

```
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
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
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
      .Destination = wdSendToNewDocument
      .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 `'Guest_Speakers$'` 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
```

Also, I found your response to another user regarding sharepoint: https://www.mrexcel.com/forum/gener...arepoint.html?highlight=sharepoint+mail+merge

I guess it is doable, ultimately?


----------



## Macropod (Sep 23, 2019)

The string:
`'Guest_Speakers$'`
should probably be:
`Guest_Speakers$`


----------



## herman925 (Sep 23, 2019)

Macropod said:


> The string:
> `'Guest_Speakers$'`
> should probably be:
> `Guest_Speakers$`



I checked and verified and I think it's not this part that rendered the error. I tested with the code like below now:


```
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:=True, ReadOnly:=False, 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
```


I specifically quoted out the 'WHERE' condition and it worked fine! (Locally......not on Sharepoint). However when I reapplied the WHERE section it doesn't work again. I believe the ` symbol is placed wrongly there

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)


----------



## Macropod (Sep 23, 2019)

What was the exact QueryString returned by the macro?


----------



## gssachin (Nov 6, 2016)

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 (Sep 23, 2019)

herman925 said:


> 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 (Sep 23, 2019)

Macropod said:


> 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 (Sep 23, 2019)

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 (Sep 23, 2019)

Macropod said:


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



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 (Sep 23, 2019)

herman925 said:


> 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 (Sep 23, 2019)

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


----------



## Macropod (Sep 23, 2019)

herman925 said:


> 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 (Sep 23, 2019)

Macropod said:


> 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 (Sep 23, 2019)

Macropod said:


> 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 (Sep 23, 2019)

herman925 said:


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





herman925 said:


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


----------



## gssachin (Nov 6, 2016)

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.


----------



## herman925 (Sep 23, 2019)

Macropod said:


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



My apologies. But really, is there anything I can do to avoid the duplicate issue?


----------



## herman925 (Sep 23, 2019)

Macropod said:


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



Also, Paul, I saw u teaching the other guy to do Mail Merge when both his files are stored on Sharepoint (but with a local copy). How did you manage to do that?

https://www.mrexcel.com/forum/gener...epoint-4.html?highlight=sharepoint+mail+merge


----------



## Macropod (Sep 23, 2019)

herman925 said:


> My apologies. But really, is there anything I can do to avoid the duplicate issue?


Well, if you'd left those parts of the code alone, you wouldn't have any duplicates. What you've failed to understand is that the mailmerge main document is used to produce the output document. As written by me, the code closes the mailmerge main document once it has produced the output document; your modifications prevent the output document's production and instead leaves behind the mailmerge main document with all the mergefields still in place - and those fields won't retain their values unless you lock or unlink them - which destroys the document's utility for future merges.


----------

