Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-11-2013, 09:41 AM
Baldeagle Baldeagle is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2012
Posts: 59
Baldeagle is on a distinguished road
Default Mailmerge to Email with a mailmerge attachment

I got help last May on this subject and the result was successful. I am now trying something similar but different. The following is the Macro which I used previously but this time it says that there is a problem at the section highlighted in red. The only difference that I can see is that my merged documents have 2 pages each whereas last year there was only one. Is this the cause of my problem and if so how do I modify the Macro to keep the 2 pages together when it does the split?


Code:
Sub SplitMergeForm() 
     '
     ' SplitMergeForm Macro
     '
     ' splitter Macro modified to save individual Gift Aid (and Tax) Allocation Forms with
     
     ' information from data source. The filename data must be added to     
     ' the top of the merge letter - see web article.     
    Dim sName As String 
    Dim docName As String 
    Dim Letters As String 
    Dim Counter As Long 
    Dim oDoc As Document 
    Dim oNewDoc As Document 
    Set oDoc = ActiveDocument 
    oDoc.Save      
    Selection.EndKey Unit:=wdStory 
    Letters = Selection.Information(wdActiveEndSectionNumber) 
    Selection.HomeKey Unit:=wdStory 
    Counter = 1 
    While Counter < Letters 
        Application.ScreenUpdating = False 
        With Selection              
            .HomeKey Unit:=wdStory 
            .EndKey Unit:=wdLine, Extend:=wdExtend 
            .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend              
        End With 
        sName = Selection 
        docName = "C:\Our Folders\IMAP\Form " & sName & ".doc"          
        oDoc.Sections.First.Range.Cut 
        Set oNewDoc = Documents.Add          
         'Documents are based on the Normal template         
         'To use an alternative template follow the link.
        With Selection 
            .Paste 
            .HomeKey Unit:=wdStory 
            .MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend 
            .Delete 
        End With 
        oNewDoc.SaveAs FileName:=docName, _ 
        FileFormat:=wdFormatDocument, _ 
        AddToRecentFiles:=False 
        ActiveWindow.Close 
        Counter = Counter + 1 
        Application.ScreenUpdating = True 
    Wend      
    oDoc.Close wdDoNotSaveChanges      
End Sub

Last edited by macropod; 02-05-2013 at 08:21 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 01-16-2013, 09:39 AM
Baldeagle Baldeagle is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2012
Posts: 59
Baldeagle is on a distinguished road
Default

I have been exploring further the problem that I wrote about last week. I am now convinced that my problem stems from the fact that my attachments has 2 pages. I have attached the relevant attachment to this message. When I run the Macro - SplitMergeForm it chokes after the first page, I assume because there is no email address at the top of the second page.
How can I get the Macro to group 2 pages into each attachment?
Attached Files
File Type: doc CHY2 Cert - Charity.doc (86.5 KB, 5 views)
Reply With Quote
  #3  
Old 02-05-2013, 05:43 AM
Baldeagle Baldeagle is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2012
Posts: 59
Baldeagle is on a distinguished road
Default

Has nobody any bright ideas that would solve my problem??? Are the experts all off on their holidays???
Reply With Quote
  #4  
Old 02-05-2013, 08:25 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,296
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

I'd suggest checking the validity of what's in your sName & docName variables. You may have content that is not valid for a filename. I doubt the added page is an issue.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 02-06-2013, 05:58 AM
Baldeagle Baldeagle is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2012
Posts: 59
Baldeagle is on a distinguished road
Default

Thanks for your comments. I have had another look at what is happening in relation to the certificate that I am trying to split. Attached is an abridged version of the file that is created after merging the data (I have eliminated most personal information and have just included 2 records) - this is called 'CHY2 Merged.doc'. After I run the Macro 'SplitMergeForm' I get the error message 'Run time error '4198''. But the result is a document which I have called 'CHY2 - Result of Macro.doc' which has just the first page of the first certificate. When I look at 'CHY2 Cert Merged.doc' it now has the first page of the first certificate missing! I have attached this file and have called it 'CHY2 Merged (After Macro).doc'

Does this make any sense to you?
Attached Files
File Type: doc CHY2 Merged (After Macro).doc (89.0 KB, 2 views)
File Type: doc CHY2 - Result of Macro.doc (75.0 KB, 3 views)
File Type: doc CHY2 Merged.doc (97.0 KB, 8 views)
Reply With Quote
  #6  
Old 02-06-2013, 02:02 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,296
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Baldeagle,

The problem isn't that you have two pages, but that you've created the second page via a 'Next Page' Section break. The macro you're using relies on Section breaks (which Word creates when executing a letter merge) for delineating the document boundaries. If you modify your mailmerge main document to use a manual page break instead of the 'Next Page' Section break to separate the letter's first and second pages, the macro will work correctly.

PS: Your document makes extensive and unnecessary use of textboxes/frames for the content. Such an approach makes maintenance far more difficult than it need be. And, since you're using Word 2007, I'd suggest modifying the Splitter macro to save the output files as PDFs so that you can ensure the layout remains as intended when opened by the recipients.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 02-06-2013, 03:05 PM
Baldeagle Baldeagle is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2012
Posts: 59
Baldeagle is on a distinguished road
Default

Thanks for your further help. The problem is that the CHY2 form was downloaded from the Irish Revenue Commissioners website and that is the way it comes - it is a .pdf form which can be completed on line and then printed. I don't know how my colleague in Limerick saved the original form as a Word document (I don't have that facility but I will explore it with him tomorrow).

I don't seem to be able to delete the 'Next Page Section break' and replace it with a manual page break. Is there any way that you can do that? I am attaching the original .pdf file as downloaded from the website. Is it possible for you to save that as a Word document with a manual page break? If you can, I would be very grateful.
Attached Files
File Type: pdf chy2cert-charity-2.pdf (266.1 KB, 7 views)
Reply With Quote
  #8  
Old 02-06-2013, 04:51 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,296
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Both your Word document and the pdf would take a fair bit of effort to convert to a Word document that doesn't use textboxes/frames. Try the following:
Code:
Sub SplitMergeForm()
    'Application.ScreenUpdating = False
     '
     ' SplitMergeForm Macro
     '
     ' splitter Macro modified to save individual Gift Aid (and Tax) Allocation Forms with
     
     ' information from data source. The filename data must be added to
     ' the top of the merge letter - see web article.
    Dim sName As String, docName As String
    Dim oDoc As Document, oNewDoc As Document
    Dim Rng As Range
    Dim i As Long, j As Long
    Set oDoc = ActiveDocument
    With oDoc
        j = .Sections.Count - 1
        For i = 1 To j Step 2
            Set Rng = .Sections(i).Range.Paragraphs.Last.Range
            With Rng
                .End = .End - 1
                sName = Replace(.Text, ".", "_")
                .Delete
                .Start = .Sections.First.Range.Start
                docName = "C:\Our Folders\IMAP\Form " & sName & ".doc"
                .MoveEnd wdSection, 2
                .Copy
            End With
            Set oNewDoc = Documents.Add
            With oNewDoc
                With .Range
                    .Paste
                    .ParagraphFormat.SpaceBefore = 0
                    .ParagraphFormat.SpaceAfter = 0
                End With
                .SaveAs2 FileName:=docName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
                .Close wdDoNotSaveChanges
            End With
        Next
        .Close wdDoNotSaveChanges
    End With
Application.ScreenUpdating = True
End Sub
Each output document will have an empty extra last page, but that's unavoidable unless you create a template that preserves the page layout, for calling via the Documents.Add process.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 02-11-2013, 04:40 AM
Baldeagle Baldeagle is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Apr 2012
Posts: 59
Baldeagle is on a distinguished road
Default

Thanks for the further help. I have used your latest code (with a couple of minor changes) and can now get the CHY2 Certificate to split properly. I appreciate your guidance very much.
Reply With Quote
Reply

Tags
attachments, email
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mailmerge to email as attachments j_Southern Mail Merge 4 10-05-2012 04:57 PM
Mailmerge to email awstyle Mail Merge 1 05-31-2012 08:13 AM
Mailmerge to Email with a mailmerge attachment Baldeagle Mail Merge 13 05-29-2012 02:04 PM
Email from MailMerge Baldeagle Mail Merge 4 04-06-2012 05:46 AM
How do you add text to email mailmerge? DerekScambler Mail Merge 10 04-19-2011 05:30 AM


All times are GMT -7. The time now is 03:17 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft