Solved

vba code to split and save mail merged word document

Posted on 2014-02-14
8
4,764 Views
Last Modified: 2016-10-22
Hi Experts word 2007

I need vba code to split and save mail merged word document into individual word doc and saved to a specific file path...based on company name as unique identifier..

Unable find the code on the net..also need instructions on hiw the code works for learning purposes.
0
Comment
Question by:route217
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39858891
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 250 total points
ID: 39858923
In a letter-type of  mail merge the result document is separated into sections, one per record in the datasource.

Here is the code with some comments added.
Sub SplitMergeResult()
     'declare the variables
    Dim sec As Section 
    Dim rng As Range
    Dim strName As String
    Dim DocA As Document
    Dim DocB As Document
   
    Set DocA = ActiveDocument 'make the actived ocument the source document
    For Each sec In DocA.Sections 'step through each section 
        Set rng = sec.Range 'get the range of the section
        rng.MoveEnd wdCharacter, -1 'omit section break
        rng.Copy
        strName = Split(sec.Range.Paragraphs(1).Range.Text, vbTab)(0) 'find the new name in the text. It might well be in a different place in your document
        Set DocB = Documents.Add 'create a new blank document
        DocB.Range.Paste 'paste the text from the current section
        DocB.SaveAs strName & ".doc" 'save the new document and 
        DocB.Close 'close it
    Next sec 'now treat the nest section
End Sub 'all done, so end the procedure

Open in new window

0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39859398
Hi,

You could use this code  in you mail merge template document ( placed in the ThisDocument module )

it captures the mailmerge events
Dim WithEvents wdapp As Application
Dim bCustomProcessing As Boolean

Private Sub Document_Open()

Set wdapp = Application
bCustomProcessing = False
ThisDocument.MailMerge.DataSource.ActiveRecord = 1
ThisDocument.MailMerge.ShowWizard 1
With ActiveDocument.MailMerge
   If .MainDocumentType = wdFormLetters Then
       .ShowSendToCustom = "Custom Letter Processing"
   End If
End With

End Sub
Private Sub wdapp_MailMergeWizardSendToCustom(ByVal Doc As Document)

bCustomProcessing = True
Doc.MailMerge.Destination = wdSendToNewDocument
With Doc.MailMerge
    For rec = 1 To .DataSource.RecordCount
        .DataSource.ActiveRecord = rec
        .DataSource.FirstRecord = rec
        .DataSource.LastRecord = rec
        .Execute
    Next
End With

MsgBox "Merge Finished"
End Sub


Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
If bCustomProcessing = True Then
    With Doc.MailMerge.DataSource.DataFields
        sFirmPathName = .Item(1).Value ' First Column of the data
    End With
    DocResult.SaveAs "c:\Documents\" & sFirmPathName & "\MailDocument.docx", wdFormatXMLDocument
    DocResult.Close False
End If
End Sub

Open in new window


at line 37  replace the 1 of  ".Item(1).Value" with the column number of the FirmID
at line 39 Change the filename and Path to your convenience

Close the file and reopen before doing the merge, The Wizard will appear and at the last step Click "Custom Letter Processing"

Regards
EE-20140214.zip
1
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:route217
ID: 39859548
Thanks experts extremely appreciate the feedback.
0
 

Author Comment

by:route217
ID: 39860531
Hi experts

Can I run the vba via a button in the word document? If yes how what are the necessary steps...so I can learn.
0
 

Author Comment

by:route217
ID: 39864303
Hi experts

How do I run this macro.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39864388
For my Code
First Activate the document containîng the mailmerge
Press Alt+F11 to go to the Visual Basic Editor.

Insert the code  in the module ThisDocument int the Microsoft Word Objects of your file ( Not the Normal)
Click Ctrl+R to Toggle the Project Explorer

You wi8ll have to change the code to accomodate your need ( Change the path,, name of the document and so on)

then close the document and reopen it to make the macro work
Regards
0
 

Expert Comment

by:Vincent chuan
ID: 41854952
HI Rgonzo1971

i got a 2 issue after i try out the code


1) Dim WithEvents wdapp As Application , the code is highlighted in red
2) .MainDocumentType  , the code is highlighted in red
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mail merge IF Statements 8 56
Word 2010 assign Hyperlink to Sub code 5 28
word 2016 spell check 3 15
Paragraph formating? 2 25
The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question