# Mail Merge



## IBAUCLAPlaya (Apr 5, 2010)

I'm wondering if the following is possible with a mail merge:

We have an excel spreadsheet that has a list of all checks that haven't been cashed. We want to send an e-mail to each payee (over 200) and show what check(s) haven't been cashed. I understand how to use the mail merge function to e-mail all of the payees, but I want to include a list of their outstanding checks in the body of each e-mail. 

For example, payee ABC has 11 outstanding checks and I want to include a table that details check #, check date, and amount for payee ABC only. This e-mail would then be sent. 

Next payee DEF has 1 outstanding check and I want to include the same detail table in the body of the e-mail, just filtered on payee DEF. 

I have the master detail file, I would just need the mail merge to basically filter on the payee code and then paste the filtered table into the body of the e-mail.

Does this make sense? Is it possible?


----------



## Macropod (Apr 5, 2010)

Hi IBAUCLAPlaya,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/index.php?showtopic=731107
Do read the tutorial before trying to use the mailmerge document included with it.


----------



## IBAUCLAPlaya (Apr 6, 2010)

I read the tutorial and still don't quite understand it. Even when using the sample files, I can't get it to work. If I copy the code from page 3 of tutorial and paste to the sample merge file, I only am getting one record per recipient, even though State NSW should show 10 records. 

My real data file is as follows:
Col A - CheckNo
Col B - Date
Col C - PaidTo
Col D - Amt
Col E - E-mail

I want to group by Paid to, send one e-mail to each Paidto that includes a list of all of their checks and also totals them.

Please help!


----------



## Macropod (Apr 6, 2010)

Hi IBAUCLAPlaya,

Done correctly, the sample field would indeed produce 10 records for NSW on the same page. If you're only getting one record per page, that suggests you're running a letter merge, not a Catalogue/Directory merge. Even so, for what you're trying to do, you need the fields & vba code from pages 22-24.


----------



## IBAUCLAPlaya (Apr 12, 2010)

I'm having some trouble doing this as I really don't understand the tutorial very well. Can you please help me?

I can provide you with my Excel data file as well as the template for what I want each e-mail to look like. Can you setup the merge to work for me?

Thanks!


----------



## IBAUCLAPlaya (Apr 12, 2010)

Ok, so I think I have figured everything out except the e-mail section. When I run the merge normally, the output looks perfect. When I run the macro to e-mail it out, I get the following error message:

Run time error 5661
You cannot send a catalog created by merging documents directly to the mail, fax or a printer. 

Any ideas?


----------



## Macropod (Apr 12, 2010)

Hi IBAUCLAPlaya,

There are in fact two mailmerges to be done. The first is the catalog merge, which groups your data into a table with one row per group. The second, which uses the table from the first merge as its data source, is done as a normal letter merge.


----------



## USFMD82 (Apr 20, 2011)

Im still kind of lost on this, 

I have read through the directions and am still not getting the expected output.
I have my database setup as 

Vendor# - Key
WO# -Vendors will possibly have multiple WO#'s
Account # - exclusive to the WO# 
E-mail - The e-mail address I am sending to

I am not understanding what I am doing wrong here that is not allowing this to merge correctly. I want to send one e-mail to the vendor informing him of all late WO's he has and some vendor have multiple WO's. I have sorted the excel file by Vendor # so they are grouped correctly.

            Vendor #   Account   WO #   E-mail
687            123        M123   Vendor 687 e-mail
      687          123        M456   Vendor 687 e-mail       
687           456        M319   Vendor 687 e-mail       
881          789        M315   Vendor 881 e-mail       
1113          1111    M314   Vendor 1113 e-mail

Any help is greatly apprecciatted


----------



## Macropod (Apr 20, 2011)

Hi USFMD82,

A few more details please, like what part of the process you're having trouble with, what results you're expecting and what you're getting. Did you simply copy the relevant fields from the tutorial and amend just the mergefield names, as appropriate, or did you try to construct the field code from scratch?

if you're using Word 2007, 2010 or 2011, you might also be interested in: http://www.gmayor.com/MailMergeWithAttachments.htm


----------



## USFMD82 (Apr 20, 2011)

I am having trouble at the part where microsoft word comes in.

I have the table as listed above in one excel file, and my template letter typed up in the other.

My end goal is to have something to the efect

Sent to Vendor 687 e-mail
"Dear Vendor 687 please turn in your late WO's listed below

WO 123 M123 
WO 123 M456 
WO 456 M319"

Sent to Vendor 881  e-mail
"Dear Vendor 687 please turn in your late WO's listed below

WO 789 M315"

and so on, I just need to make sure that the vendor gets all theri applicable work orders, and this file is about 1200 vendors and 1500 late work orders.

I have the ile in excel and the Microsoft word doc, but when I set the merge to "Database" it still functions like its a letter. Do I need to have the merge excel file closed for it to work?

Im still confused by the previosuly mentioned im actually doing 2 merges one for the merging of the files to make each vendors work and loans and then anotehr merge that is physiclaly the e-mail merging.

Also I haven ot done any of the macros, I didnt quite follow where and when I use them.

Hope this is enough to go off of. I truly apprecciatte it.


----------



## IBAUCLAPlaya (Apr 5, 2010)

I'm wondering if the following is possible with a mail merge:

We have an excel spreadsheet that has a list of all checks that haven't been cashed. We want to send an e-mail to each payee (over 200) and show what check(s) haven't been cashed. I understand how to use the mail merge function to e-mail all of the payees, but I want to include a list of their outstanding checks in the body of each e-mail. 

For example, payee ABC has 11 outstanding checks and I want to include a table that details check #, check date, and amount for payee ABC only. This e-mail would then be sent. 

Next payee DEF has 1 outstanding check and I want to include the same detail table in the body of the e-mail, just filtered on payee DEF. 

I have the master detail file, I would just need the mail merge to basically filter on the payee code and then paste the filtered table into the body of the e-mail.

Does this make sense? Is it possible?


----------



## Macropod (Apr 20, 2011)

USFMD82 said:


> I am having trouble at the part where microsoft word comes in.


Umm, given that the whole process is driven from Word, that's a fairly ambiguous problem description too.

Have you created your e-mail mailmerge main document, as per the instructions? Have you also created the Word file, named ‘EmailDataSource.doc’ in the same folder as your ‘Email Merge Main Document’, again as per the instructions?


USFMD82 said:


> I have the table as listed above in one excel file, and my template letter typed up in the other.


A mailmerge can only use one Excel file for its data source. Your 'template letter' should be the 'e-mail mailmerge main document' Word document, as per the instructions.


USFMD82 said:


> I have the ile in excel and the Microsoft word doc, but when I set the merge to "Database" it still functions like its a letter. Do I need to have the merge excel file closed for it to work?


Why did you set the merge to "Database"? That's not what the instructions say! Besides, there's no such thing as a "Database" merge. The instructions say:


> First, create your e-mail mailmerge main document, setting it up for a normal e-mail merge with whatever text you need and a field for the data («Data»). You’ll need to format the paragraph containing the Data field with as many tabs as there are data fields in your primary data source, so as to layout the results correctly. You can also add a recipient field («Recipient») if you want that information to appear in body of the email.


and


> For the data source for the e-mail mailmerge main document, create a temporary Word file, named ‘EmailDataSource.doc’ in the same folder as your ‘Email Merge Main Document’


The instructions then say:


> Next, set up a separate Directory/Catalog merge document, in the same folder as your ‘Email Merge Main Document’


with a single paragraph containing field coding as per the example given there.


USFMD82 said:


> Im still confused by the previosuly mentioned im actually doing 2 merges one for the merging of the files to make each vendors work and loans and then anotehr merge that is physiclaly the e-mail merging.


That's correct - it's a two-stage process, due to the fact that Word doesn't provide the tools to do it in one stage and because the code to turn it into such would be much more complex.


USFMD82 said:


> Also I haven ot done any of the macros, I didnt quite follow where and when I use them


The 'where and when' are clearly stated in the tutorial:


> ... insert the following code into a normal vba code module in the Directory/Catalog mailmerge main document. Once you’ve done that, run the ‘RunMerge’ macro.


If you don't know how to install or run a macro, see: http://www.gmayor.com/installing_macro.htm


----------

