[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Mail merging to individual documents

Hi there,

I need to produce about 100 CPD certificates of attendance for emailing to our delegates.
What is the best way to complete this?
I have all the data and merge fields ready in a template but the only way I can find to do it is to "Edit Individual Documents" but this produces one document with 100 pages. I need 100 documents with 1 page each.

I can not really do an email merge as I need consistency in the results that the delegates will receive and if viewing as plain text it would not look good as it will remove the company logo and signature pictures etc.

Any advise on how to do this would be appreciated. I appreciate that Microsoft Word 2007 may not have this feature but would like to know what does.

Thanks,
Jack
0
JackHodson
Asked:
JackHodson
  • 14
  • 11
  • 2
  • +1
1 Solution
 
GrahamSkanCommented:
It is possible, but it needs some VBA coding.

There are two possible approaches. The simpler (because I've already do it) is to split the result document into separate parts. There may be a problem with naming them meaningfully, but the code is here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_21607752.html

Another way is to run the merge under VBA control. I have done that too, but I can't find the code at the moment.

I have to go shopping now (with my partner, so it won't be quick), but I'll pick this up when we get back this afternoon.
0
 
JackHodsonIT ManagerAuthor Commented:
OK, many thanks. Does not sound simple - I was hoping for a 3rd party piece of software to facilitate this.

I will have a look at the other thread. Never really done too much with VBA so will be a learning curve!
0
 
GrahamSkanCommented:
I'm now back at home and looking for the code for the other method.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GrahamSkanCommented:
Found it. I'm not sure if it was tested, but it looks OK. You need to decide on which field(s) are to be used for naming the output documents. I've used CustID
Sub MergeToSeparateDocs()
    Dim wdDocMain As Word.Document
    Dim r As Integer
    Dim strFileName As String
    Set wdDocMain = ActiveDocument
   
    With wdDocMain.MailMerge
        .DataSource.ActiveRecord = wdLastRecord 'ensure records are counted
        For r = 1 To .DataSource.RecordCount
            .DataSource.FirstRecord = r
            .DataSource.LastRecord = r
            .Destination = wdSendToNewDocument
            strFileName = "C:\MyFolder\" & .Fields("CustID") & ".docx"
            .Execute
            ActiveDocument.SaveAs strFileName
            ActiveDocument.Close wdDoNotSaveChanges
        Next r
    End With
End Sub

Open in new window

0
 
JackHodsonIT ManagerAuthor Commented:
Thank you. Don't suppose you have an idiots guide on how to use this code?
I have only dabbled in this very briefly before.

Many thanks,
Jack
0
 
GrahamSkanCommented:
0
 
dlc110161Commented:
Here is a great tool for creating individual letters / documents either as Word documents or PDF documents.

http://www.gmayor.com/individual_merge_letters.htm

Hope this helps.
Dawn Bleuel
Word MVP
0
 
softpro2kCommented:
I have found this code working.

1) Make a folder named "SplitDoc" under C: Drive where all splitted files will be saved

Public Sub SplitWordDoc()
 
Dim sPath As String
Dim sName As String
Dim p As Long
Dim docNew As Document
Dim rngSource As Range
 
'gets document application path to provide saving location
sPath = "C:\SplitDoc\"
sName = Replace(ActiveDocument.Name, ".doc", "", Compare:=vbTextCompare)
 
'go to start of document
Selection.HomeKey wdStory
 
Application.ScreenUpdating = False
 
'get current page count
ActiveDocument.Repaginate
 
'for each page in the document
For p = 1 To ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
 
    'select the page
    ActiveDocument.Bookmarks("\Page").Range.Select
    'move left 1 character
    Selection.MoveLeft wdCharacter, 1, wdExtend
    'set the range to be copied to new document
    Set rngSource = Selection.Range.FormattedText
 
    'create new document
    Set docNew = Documents.Add
    'copy page contents
    docNew.Range.FormattedText = rngSource
    'save the document
    docNew.SaveAs sPath & sName & "_Page" & p & ".doc"
    'close the document
    docNew.Close True
 
    'go to the next page
    Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext
 
Next p
 
'go to start of document
Selection.HomeKey wdStory
 
Application.ScreenUpdating = True
 
End Sub

Open in new window

0
 
GrahamSkanCommented:
softpro2k,

If each mail merge record produces more than one letter page then the letters themselves will split across more than one document. The mail merge result document is actually divided by next page section breaks, so if you split on those, you are more likely to get an acceptable result.

That is what happens in the link:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_21607752.html

that I posted in my first comment. It names each document according to its first word, but that, of course, could be changed to some other unique and meaningful text.

Here is the version that the questioner accepted.

Sub SplitMergeResult()
    Dim sec As Section
    Dim rng As Range
    Dim strName As String
    Dim DocA As Document
    Dim DocB As Document
    Dim rngStart As Integer
   
    If Selection.Sections(1).Index <> 1 Then
        MsgBox "Please select the first name and restart this macro"
        End
    End If
    rngStart = Selection.Start
    Set DocA = ActiveDocument
    For Each sec In DocA.Sections
        Set rng = sec.Range
        rng.MoveEnd wdCharacter, -1 'omit section break
        rng.Copy
        rng.Start = sec.Range.Start + rngStart
        strName = Trim$(rng.Words(1).Text)
        Set DocB = Documents.Add
        DocB.Range.Paste
        DocB.SaveAs strName & ".doc"
        DocB.Close
    Next sec
End Sub

Open in new window

0
 
softpro2kCommented:
Hello GrahamSkan,

Yes, I had not considered this scenario "...each mail merge record produces more than one letter page"

You are right.

Regards.
0
 
JackHodsonIT ManagerAuthor Commented:
Hi GrahamSkan,

I have now tried to setup this Macro.
I changed the 'Cust ID' for 'Email' which is one of the columns in my spreadsheet that I am merging this document from.

When I run the Macro I get a Microsoft Visual Basic box come up with:

Run-time error '13':
Type mismatch

It refers to this line:
strFileName = "C:\MyFolder\" & .Fields("Email") & ".docx"

I have created the folder 'MyFolder' on my C:\ root.

Any advice would be appreciated.

Thanks,
Jack

0
 
JackHodsonIT ManagerAuthor Commented:
I tried to use the option to SplitMergeResult but this results in:

Run-time error '4198'
Command failed

It did however make one out of 84 documents for me.

I think I would prefer to use the MergeToSeparateDocs macro though.

Especially if it is possible to change the .docx to .pdf or is this asking too much?
0
 
GrahamSkanCommented:
Here is a corrected version of the MergeToSeparateDocs. I got my field objects confused.

It has also been tweaked to save as a pdf
Sub MergeToSeparateDocs()
    Dim wdDocMain As Word.Document
    Dim wdDocResult As Word.Document
    Dim r As Integer
    Dim strFileName As String
    Set wdDocMain = ActiveDocument
   
    With wdDocMain.MailMerge
    .
        .DataSource.ActiveRecord = wdLastRecord 'ensure records are counted
        For r = 1 To .DataSource.RecordCount
            .DataSource.FirstRecord = r
            .DataSource.LastRecord = r
            .Destination = wdSendToNewDocument
            strFileName = "C:\MyFolder\" & .DataSource.DataFields("CustNo") & ".pdf"
            .Execute
            ActiveDocument.SaveAs strFileName, wdFormatPDF
            ActiveDocument.Close False
        Next r
    End With
End Su

Open in new window

0
 
JackHodsonIT ManagerAuthor Commented:
Thanks. There was a . on line 9 which I removed, hopefully correctly as it threw an error and it sprung in to life and looking like it was mail merging. However in the output folder, there was on 1 PDF file which was the last record of the merge. Looked OK though so if we can get it to work for the previous 83 it would be great.

I tried just merging just 5 records but again it only stored the final one in the list I selected from.

Thanks for your help.
Jack
0
 
GrahamSkanCommented:
Yes, I see the stray dot.

Have you chosen a field name that will be unique for the file name differentiator?

This code as a debug line. This will output to the Immediate window.
Sub MergeToSeparateDocs()
    Dim wdDocMain As Word.Document
    Dim wdDocResult As Word.Document
    Dim r As Integer
    Dim strFileName As String
    Set wdDocMain = ActiveDocument
   
    With wdDocMain.MailMerge
            .DataSource.ActiveRecord = wdLastRecord 'ensure records are counted
        For r = 1 To .DataSource.RecordCount
            .DataSource.FirstRecord = r
            .DataSource.LastRecord = r
            .Destination = wdSendToNewDocument
            strFileName = "C:\MyFolder\" & .DataSource.DataFields("CustNo") & ".pdf"
            .Execute
            ActiveDocument.SaveAs strFileName, wdFormatPDF
            Debug.Print "File Saved: " & strFileName
            ActiveDocument.Close False
        Next r
    End With
End Su

Toggle HighlightingOpen in New WindowSelect All

Open in new window

0
 
JackHodsonIT ManagerAuthor Commented:
Sorry, still no joy.
Here is a screen shot I get of the debug after a

Compile error:
Syntax error

Jack
Clipboard01.jpg
0
 
GrahamSkanCommented:
Hi Jack.

The only thing that I can see wrong is the spurious ' Toggle Highlight...'  line which came when I copied from the EE code snippet box in the wrong way. Just delete it.
0
 
JackHodsonIT ManagerAuthor Commented:
Hi Graham,

OK, have removed that but the old problem remains. Only saving the last record as a PDF. The other look like they are being merged but the end result in 'MyFolder' is just one, 1 page PDF file.

Jack
0
 
GrahamSkanCommented:
There should be a list from this line:

Debug.Print "File Saved: " & strFileName

 in the Immediate Window. Can you say what it is, please?
0
 
JackHodsonIT ManagerAuthor Commented:
Yes, it has:

File Saved: C:\MyFolder\test@test.co.uk.pdf

There are 84 lines of this  - I am merging 84 records.

Very odd.
0
 
GrahamSkanCommented:
In your line 14:

           strFileName = "C:\MyFolder\" & .DataSource.DataFields("CustNo") & ".pdf"

you seem to have "test@test.co.uk" in the "CustNo" field of every record. You need to have something that is unique between records. (The field name doesn't have to be 'CustNo'. That is just an example)
0
 
JackHodsonIT ManagerAuthor Commented:
I don't have test@test.co.uk. I actually have "Email" as this is one of the columns in the Excel I am merging from.

I just added a column to the Excel called "Ref".

Line 14 now reads:
 strFileName = "C:\MyFolder\" & .DataSource.DataFields("Ref") & ".pdf"

The Ref column goes:
0001
0002
0003 etc


Same problem as before.

I tried to merge 6 records and I got:

File Saved: C:\MyFolder\0006.pdf
File Saved: C:\MyFolder\0006.pdf
File Saved: C:\MyFolder\0006.pdf
File Saved: C:\MyFolder\0006.pdf
File Saved: C:\MyFolder\0006.pdf
File Saved: C:\MyFolder\0006.pdf

Here is my full code.
Sub MergeToSeparateDocs()
    Dim wdDocMain As Word.Document
    Dim wdDocResult As Word.Document
    Dim r As Integer
    Dim strFileName As String
    Set wdDocMain = ActiveDocument
   
    With wdDocMain.MailMerge
            .DataSource.ActiveRecord = wdLastRecord 'ensure records are counted
        For r = 1 To .DataSource.RecordCount
            .DataSource.FirstRecord = r
            .DataSource.LastRecord = r
            .Destination = wdSendToNewDocument
            strFileName = "C:\MyFolder\" & .DataSource.DataFields("Ref") & ".pdf"
            .Execute
            ActiveDocument.SaveAs strFileName, wdFormatPDF
            Debug.Print "File Saved: " & strFileName
            ActiveDocument.Close False
        Next r
    End With
End Sub

Open in new window

0
 
GrahamSkanCommented:
Hmm. I must admit that I haven't tested the code for about five years. I'll do that now.
0
 
GrahamSkanCommented:
My apologies. There was a line missing. Thank you for your patience.

Here is a tested version.
Sub MergeToSeparateDocs()
    Dim wdDocMain As Word.Document
    Dim wdDocResult As Word.Document
    Dim r As Integer
    Dim strFileName As String
    Set wdDocMain = ActiveDocument
   
    With wdDocMain.MailMerge
        .DataSource.ActiveRecord = wdLastRecord 'ensure records are counted
        For r = 1 To .DataSource.RecordCount
            .DataSource.FirstRecord = r
            .DataSource.LastRecord = r
            .Destination = wdSendToNewDocument
            .DataSource.ActiveRecord = r   '<---- was missing
            strFileName = "C:\MyFolder\" & .DataSource.DataFields("Title") & ".pdf"
            .Execute
            ActiveDocument.SaveAs strFileName, wdFormatPDF
            ActiveDocument.Close False
        Next r
    End With
End Sub

Open in new window

0
 
JackHodsonIT ManagerAuthor Commented:
Great, it works brilliantly, thank you.
My last question. If I wanted to give the file name something that is not in the data merge fields, is this possible?

            strFileName = "C:\MyFolder\" & .DataSource.DataFields("Email") & ".pdf"

I would like it to have a file name of:

"Certificate of Attendance - Title First Name Last Name.pdf"

Thanks again.

0
 
GrahamSkanCommented:
You can use any field in the datasource that gives a unique and valid file name. It doesn't have to be used on the document as a Merge field.
0
 
JackHodsonIT ManagerAuthor Commented:
Thank you. Has saved me an awful lot of time for the future now.
0
 
GrahamSkanCommented:
And thank you for helping me debug an old macro that had become damaged.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 14
  • 11
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now