Mail Merge

[IBAUCLAPlaya]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.