Emailing Directory/Catalog Mail Merge

[supportservice]

I am trying to use macropod's guide and got the macro in my email merge document. It created the EmailDataSource.doc

However it looks like this and did not email?

Files

Not sure what I am doing wrong. Could you help me?

Dear Accounts Receivables Department of BERRY NETWORK,

	Please charge credit card on file for the following invoice(s):

EmailAddress	Data
Invoice Number
12439588	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
09/25/2012	$5,184.17	$0.00	$5,184.17
TOTAL:	$5,184.17
09-11-2012


Dear Accounts Receivables Department of E.M.W.D. (Dump Fees),

	Please charge credit card on file for the following invoice(s):

Invoice Number
14919	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
09/23/2012	$4,280.00	$0.00	$4,280.00
TOTAL:	$4,280.00
09-11-2012


Dear Accounts Receivables Department of Ferguson Enterprises, Inc,

	Please charge credit card on file for the following invoice(s):

Invoice Number
8598515	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
09/05/2012	$508.96	$0.00	$508.96
TOTAL:	$508.96
09-11-2012


Dear Accounts Receivables Department of Hemet Industrial Supply,

	Please charge credit card on file for the following invoice(s):

Invoice Number
145890CM	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
08/16/2012	$-81.54	$0.00	$-81.54
145588	09/10/2012	$188.68	$0.00	$188.68
145715	09/10/2012	$238.31	$0.00	$238.31
145725	09/10/2012	$206.24	$0.00	$206.24
145843	09/10/2012	$75.08	$0.00	$75.08
145844	09/10/2012	$230.44	$0.00	$230.44
145853	09/10/2012	$40.32	$0.00	$40.32
145858	09/10/2012	$48.07	$0.00	$48.07
145899	09/10/2012	$63.91	$0.00	$63.91
145934	09/10/2012	$71.05	$0.00	$71.05
145939	09/10/2012	$20.15	$0.00	$20.15
145940	09/10/2012	$131.13	$0.00	$131.13
146029	09/10/2012	$2.25	$0.00	$2.25
146030	09/10/2012	$42.64	$0.00	$42.64
146032	09/10/2012	$37.26	$0.00	$37.26
146047	09/10/2012	$609.71	$0.00	$609.71
145856	09/10/2012	$17.85	$0.00	$17.85
146014	09/10/2012	$31.50	$0.00	$31.50
TOTAL:	$1,973.05
09-11-2012


Dear Accounts Receivables Department of HIRSCH PIPE & SUPPLY,

	Please charge credit card on file for the following invoice(s):

Invoice Number
2995750	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
08/23/2012	$26.37	$2.64	$26.37
2996669	08/24/2012	$14.14	$1.41	$14.14
2999403	09/10/2012	$4.13	$0.04	$4.13
2989172	08/18/2012	$28.77	$2.88	$28.77
2994885	08/23/2012	$341.27	$3.41	$341.27
2989127	08/18/2012	$11.44	$1.14	$11.44
2989132	08/18/2012	$4.71	$0.47	$4.71
TOTAL:	$430.83
09-11-2012


Dear Accounts Receivables Department of MWI Plumbing Wholesale,

	Please charge credit card on file for the following invoice(s):

Invoice Number
002555	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
09/15/2012	$37.06	$0.00	$37.06
TOTAL:	$37.06
09-11-2012


Dear Accounts Receivables Department of PETE'S ROAD SERVICE,

	Please charge credit card on file for the following invoice(s):

Invoice Number
690353-00	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
09/10/2012	$1,692.73	$0.00	$1,692.73
TOTAL:	$1,692.73
09-11-2012


Dear Accounts Receivables Department of The Plumbers Warehouse,

	Please charge credit card on file for the following invoice(s):

Invoice Number
0012416	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
08/29/2012	$338.62	$0.00	$338.62
0013160	08/31/2012	$30.49	$0.00	$30.49
TOTAL:	$369.11
09-11-2012


Dear Accounts Receivables Department of Western Drain Supplies,

	Please charge credit card on file for the following invoice(s):

Invoice Number
48744	Invoice Due Date	Invoice Amount	Discount Amount	Net Invoice
09/15/2012	$479.55	$0.00	$479.55
48797	08/23/2012	$2,161.23	$0.00	$2,161.23
48767	09/20/2012	$627.71	$0.00	$627.71
48841	08/28/2012	$75.70	$0.00	$75.70

 

[supportservice]

Oops, here's the link:

http://www.4shared.com/zip/JC87TvG_/EmailMerge-Template.html

 

[macropod]

The tutorial has very specific instructions for using a Directory merge for email purposes. It would appear you have not followed them.

Cheers
Paul Edstein
[MS MVP - Word]

 

[supportservice]

Thank you

I used the page 22 macro and made edits to fit.

It crashes when generating the EmailDataSource.doc
Though I can then open it to see the results.
But the 1st record is missing the date.
The last record is missing the total for the last Vendor.

Then how does it email?

I'll review the tutorial again but I followed the Directory and used your example. It merges great to a Doc but not to Email.

So I added the macro, with edits to fit, from page 22. It runs but the 4 issues.

 

[supportservice]

According to page 22, it says

Merging by Catalog/Directory to E-Mail
Microsoft hasn’t provided a means for using a Directory/Catalog merge to generate a series of e mails based on grouped data. Merging to e mail produces one email message per record, which doesn’t look especially professional when you have multiple records for the same client. Here’s a workaround:

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(s) with as many tabs as needed to keep the results of the data fields in your primary data source separate, 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.

Note: The ‘RunMerge’ macro that drives this process (see below) assumes your e mail mailmerge main document will be named ‘Email Merge Main Document’. If that’s not what you name your’s, you’ll need to change the corresponding name in the ‘RunMerge’ macro.

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 like:

«City» «Representative» «Sales»[/code]

1. I have the email mailmerge main document: "EmailMerge-Template.doc"
2. I am confused on the "a field for the data"? Unless it's the data that's in the "EmailMerge-Template.doc". If so that's done as well.
3. I am not sure that I understand what you mean on the "recipient field if you want that information to appear in the body of the email.?
4. I created the 'RunMerge' macro using the VBA provided. Made the column changes to 5 from the 2. Renamed the 'Email Merge Main Document' to 'EmailMerge-Template'. Which contains the information / "data" I need in the email body.

The ‘RunMerge’ macro takes care of the whole process, executing a Directory/Catalog merge on the raw data to consolidate the groups, then taking the consolidated data and executing an e mail merge to generate the e mails. For testing purposes, you might prefer to change the ‘wdSendToEmail’ statement in the ‘RunMerge’ macro to ‘wdSendToNewDocument’.

I ran the 'RunMerge'. It created the 'EmailDataSource.doc' which I can open after it crashes (issue 1) with the information except for the issues mentioned.
1. Date is missing on the first Vendor information
2. Total is missing on the last Vendor information
3. It is not merging to email?

 

[supportservice]

It did not do what your example shows:

The ‘RunMerge’ macro saves a document named  ‘EmailDataSource.doc’ in the same folder as your ‘Email Merge Main Document’, containing the e mail mailmerge data in a table like:
Recipient	Data
ACTmanager@nowhere.com	City	Representative	Sales
Canberra	Yang	$2,100

The table with the data is there but not the email. It did not email either.
It had the issues on the 1st Vendor and last Vendor.
And it crashing.

Hopefully I didn't confuse this.

Thank you

 

[macropod]

There is no need to continually quote slabs of the tutorial at me - I wrote it, after all. Nor is there any need to post large blocks of your output, correct or otherwise - a representative sample is sufficient.

The instructions in the tutorial for email work fine if they are followed - just as the instructions for creating a sorted output file per you other thread worked fine when you followed the instructions. I really don't want to go through all the to-ing and fro-ing we had in that other thread again just because you're not paying close attention to the instructions. All of this:

It crashes when generating the EmailDataSource.doc
Though I can then open it to see the results.
But the 1st record is missing the date.
The last record is missing the total for the last Vendor.

Then how does it email?

tells me you're not paying attention to what the tuorial says you should be doing.

Cheers
Paul Edstein
[MS MVP - Word]

 

[supportservice]

Two of us are going through each step and the confusion, as stated above remains.

I am quoting the parts where I seem to be stuck / falling short?

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 like:

{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}"{IF{MERGEFIELD VendorName}<> {Key}
"{SET Key {MERGEFIELD VendorName}}

|{MERGEFIELD EmailAddress} | InvoiceNo | InvoiceDueDate | NetInvoice|
"}
| | {MERGEFIEL PayInvoiceNo} |MERGEFIELD InvoiceDueDate} | MERGEFIELD NetInvoice |

Note: Your Directory/Catalog mailmerge main document shouldn’t have anything else – not even extra empty
paragraphs; otherwise you may not get the expected results.

Also, insofar as the final output is concerned, the paragraph tab formatting in this document is of no consequence – it only matters if you want to use the ‘EmailDataSource.doc’ file that is produced as a by-product of the process for some other purpose.

Then, instead of executing the mailmerge from either document in the normal manner, 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.

This is suggesting to create a 3rd document. It does not say to save it. Regardless it says to apply the macro in this 3rd document with just the coding as provided in the tutorial.

That was done and I am now getting, running it from this 3rd unnamed document with the macro, the following error after stating

"Opening this document will run the following SQL command:
[blank]
Data from your database will be place in the document. Do you want to continue?
[Show Help >>]

Yes No

Then when running the RunMacro I get the following error:

Microsoft Visual Basic
Object variable or With block variable not set.

It's an awesome tutorial! ... however it's either for a more advanced person / user then I or ?

The document shows email address and the data as what they should be showing

However when I run the macro, though it creates the EmailDateSource.doc, it says:

Microsoft Visual Basic
You cannot send a catalog created by merging documents directly to mail, fax, or a printer.

?

Two of us aren't figuring it out ... :-(

 

[macropod]

The tutorial says:

First, create your e mail mailmerge main document

That's one document. Then it says:

Next, set up a separate Directory/Catalog merge document

That's two documents. Then it says:

Then, instead of executing the mailmerge from either document in the normal manner, insert the following code into a normal vba code module in the Directory/Catalog mailmerge main document.

That's still only two documents - the macro code is added to the second one. I don't know where you get the idea of a third document at this stage from. There is no third document to see until after the macro has run - and that's just to confirm what was merged to each email.

Cheers
Paul Edstein
[MS MVP - Word]

 

[supportservice]

The document it creates "EmailDataSource.doc" would be the 3rd?

Yes, the 1st document is my "EmailMerge-Template.doc" with all the info and VBA.

the 2nd is just the code as it states in your tutorial, though it doesn't say save it? And in here add the 'RunMerge' macro. Which I did and got all the messages as noted.

Sorry :-(

 

[macropod]

The document you put the code into is not just a blank document. What does the rest of the text after "Next, set up a separate Directory/Catalog merge document" say?...

Cheers
Paul Edstein
[MS MVP - Word]

 

[supportservice]

I have the code you have in the tutorial and only that as specified in the tutorial.
What I am saying is it doesn't say to save it as anything but to simply then run the RunMerge macro from the 2nd document with the only code you have in your document.

{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}"{IF{MERGEFIELD VendorName}<> {Key}
"{SET Key {MERGEFIELD VendorName}}

|{MERGEFIELD EmailAddress} | InvoiceNo | InvoiceDueDate | NetInvoice|
"}
| | {MERGEFIEL PayInvoiceNo} |MERGEFIELD InvoiceDueDate} | MERGEFIELD NetInvoice |

 

[supportservice]

Would you by chance know the VBA for Access?

To email the detailed list to the Vendors in the body of the message?
If that's easier that would probably be better then using this Word method?

 

[macropod]

Whether you save the Directory/Catalog mailmerge main document before running the macro is of no consequence. Of course, if you want to be able to re-use it at a later date, saving it at some stage obviously makes sense.

As for Access, a web search for 'Access vba email' turns up numerous possibilities that a proficient Access vba coder (not me) might be able to adapt.

Cheers
Paul Edstein
[MS MVP - Word]

 

[supportservice]

Yes that is fine that it doesn't state save that document.
However I ran the RunMerge macro in that very 2nd document as stated.
I had someone else review it as well.

same thing. all the messages I posted comes up.
So we BOTH do not know why this is happening.

Thank you.

It would be great if this was figured out rather then venturing off onto that realm from fresh/new at this point.
Though I will search BUT with the amount of time invested in this, it would be great to get that one piece (I think one piece) figured out.?

 

[macropod]

Actually, regarding the save issue, if you don't save the Directory/Catalog mailmerge main document (in the same folder as the 'EmailDataSource' document) before running the macro, it won't have a file path. That could account for the "Object variable or With block variable not set" error.

The second error (ie "You cannot send a catalog created by merging documents directly to mail, fax, or a printer.") suggests you have configured the 'EmailDataSource' document as a Directory/Catalog mailmerge main document. The tutorial says not
to do this but to save it as a standard email mailmerge main document.

Regarding, the "The last record is missing the total for the last Vendor", please re-read the instructions under 'Inserting Content After the Repeated Data' on pages 8-10.

As for "the 1st record is missing the date", that suggests you haven't done the field coding correcly.

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Also regarding, the "The last record is missing the total for the last Vendor", please re-read the alternative instructions on pp23-24 about using the ‘TableSummarize’ macro for generating the group totals.

Cheers
Paul Edstein
[MS MVP - Word]

 

[supportservice]

ok thank you!