Catalogue/Directory Mail Merge to email = Hitting a brick Wall! Help, Please...!

I have created a fairly large catalogue/directory mail merge. (please see attached files, please note these are test files due to data that would be included)

My objective is to create new client statements which can then be emailed direct to contact, with a sub objective of any code/layout generated be as generic as possible so that other areas of the business could use this.

I have created the statement with preferred layout but I am unable to get this to email our contact straight from directory merge. The conatct details will be in the excel file.

I have looked at a few examples but I cannot get this working for my situation.

Can this be done to accommodate what I am doing?

I have had my system crash a few times when running the merge to print never mind process it to email, but hopefully this is just my slowish work system.

Test.docx

Test.xlsx

If there are any improvements with what I have done please don’t hold back, anything that can be changed for the better is very much appreciated.

Hi Ross,

Congratulations on what you've got so far. I've had quite a few 'clients' who've struggled & failed with much simpler record groupings via catalogue/directory mailmerge.

Unfortunately, there's no way to get such a mailmerge to generate emails directly. That's why the tutorial also has a section on generating email output via a vba-driven mailmerge. That said, the process envisaged in the tutorial has the data being pushed directly into the emails, as body text. I suspect you'd rather have the statements attached as individual documents. In that case, you'll need to split the output you're now getting into separate files (see 'Split the single merged document into separate letters' at http://www.gmayor.com/individual_merge_letters.htm), then use a process like the one at http://word.mvps.org/FAQs/MailMerge/...ttachments.htm to generate the actual emails.

Hi Paul,

Thank you for you assistance!

It has taken some time to get to this point and frustratingly unable to get any further, following the splitter macro.

On running the macro, my merge disappears with no end result in the directory/path set.

I am on a server currently using directory/path of 'U:\My documents\Split\' in the macro.

Is there anything i am missing?

This is a very large merge file with 882 pages after merge.

Hi Hi Ross,

The splitter macro in the link is premised on the use of Section breaks, whereas your document outputs only page breaks. That can be fixed by changing the {QUOTE 12} field, which I've done in the attached document, along with a few other minor changes - to ensure your tables don't get split across page breaks and to remove an unnecessary extra paragraph break between the client tables.

I've also re-written the Splitter macro to output PDFs, using the client names for the filenames and a template to ensure the page layout is correct. That should both make generating the emails simpler, ensure the statements are accessible to people who can't read docx files and ensure the formatting & content remains as you intend (ie the recipients won't be easily able to alter the content).

Sub Splitter()
Application.ScreenUpdating = False
Dim i As Long, j As Long
Dim Rng As Range, StrPath As String
Dim oDoc As Document, oNewDoc As Document
Set oDoc = ActiveDocument
oDoc.Save
j = oDoc.Range.Sections.Count
StrPath = "U:\Users\" & Environ("UserName") & "\Documents\Split\"
i = 1
For i = 1 To j
  oDoc.Sections(i).Range.Copy
  Set oNewDoc = Documents.Add(Template:=StrPath & "Statement Template.dotx")
  With oNewDoc
    With .Range
      .Paste
      .Characters.Last.Previous.Text = vbNullString
      Set Rng = .Tables(1).Cell(Row:=2, Column:=2).Range.Paragraphs.First.Range
      Rng.End = Rng.End - 1
      DocName = StrPath & Rng.Text
    End With
    .SaveAs2 FileName:=DocName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    .Close wdDoNotSaveChanges
  End With
  i = i + 1
Next
oDoc.Close wdDoNotSaveChanges
Set Rng = Nothing: Set oNewDoc = Nothing: oDoc = Nothing
Application.ScreenUpdating = True
End Sub

Note: You'll need to put the template in whatever folder you use for the output (defined via the StrPath variable).

Enjoy.

PS: You'll probably need to re-connect the mailmerge main document to your data source.

What is in the 'name' cell for document 21? I suspect a character that is invalid for a filename. I assumed you'd only have valid filename charaters; the code to be modified to delete/replace invalid characters if that is what the problem is.

Regarding the filename, yes, that's possible, provided we can identify where each component should come from. Right now, I'm not even sure what Rng.Text returns.

As for the compile error with 'oDoc = Nothing', change that to:
Set oNewDoc = Nothing

Paul,

Ah!, alot of the clients names have a /

i.e Client T/A someone.

This is easy fixed here as I can change the name in excel before taking it to merge.

One thing I have noticed is that it skips every other file, so if I have list of A, B, C, D, E - It would only generate PDF's for A, C & E.

Any ideas?

Next step is emailing the PDFs.

Originally Posted by RossInk

Ah!, alot of the clients names have a /

i.e Client T/A someone.

This is easy fixed here as I can change the name in excel before taking it to merge.

You could more easily change:
Rng.Text
to:
Replace(Rng.Text,"/","") or Replace(Rng.Text,"/","_")
[QUOTE}One thing I have noticed is that it skips every other file, so if I have list of A, B, C, D, E - It would only generate PDF's for A, C & E.

Any ideas?[/QUOTE]
That was an oversight from my edits of the original code. Delete:
i = i + 1

Hi Paul,

I cant thank you enough.

I had played about with it but with no success.

I am now looking over the email script from the link you sent.

Will this work for what i am doing/wanting?

If I use the data source I started with as it contains all the details I need, will it list multiple lines for each client as they then contain sub clients. So effectivley I would email them multiple statements instead of one.

email list.docx

Also, from this how would I edit the content of the email?

Thanks again!

Hi Ross,

You'll need to get your 'email list' file to confiorm to the specs in the link. That means your first column needs to have the email addresses, not the client names and, similarly, that the second column needs to have the filenames - per your 3rd column but without the "/" characters in the client names. You can use Find/Replace after the list merge to remove the "/" characters. As for the email content, the macro in the link envisages the body text being produced by a standard mailmerge (see the last paragraph before the macro). This means each recipient can get a personalised letter.

Maybe someone deleted it... You could try adding it to your system once again, from the posts earlier in this thread.