Mail Merging - Mergeseq + Send Via Email Merge

[toughiv]

Mail Merging Multiple Rows One Document

Dont know if what i'm attempting to do is possible. Im sure it must be. I am very stuck though! Tried for hours.



Please help?

Ultimatey, I am trying to mailmerge a spreadsheet relating to invoices. there are multiple rows for each company, however each row is a unique Invoice Number.

Instead of email mail merging normally, I want just one mail merge made for each company, where it lists the invoices there, rather than a whole new document per row. Otherwise I'd send one company 50 emails!

I have attached an example spreadhseet - and I have attached a screenshot of the word document Im working on.

Dont ask me where I got that code at the top of the word document - ive copied and pasted from somewhere - it might as well be written in italian. Im trying to match the mergefields with what I think it's trying to denote. Yet, I cant make an If then else mergefield?

Anyway - could you help?

Is it possible to do this sort of mail merge, then use the email column to designate where to send the finished merged product?

THANKS!

[Mjpunj]

You will want to export the spreadsheet to an Excel workbook and create a pivot table, then place an empty cell above each company name, then use a macro to take all of the data below each individual company name and transpose it into columns to the right of the data. I will attach the macro you will need to use as a text file.

[macropod]

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://windowssecrets.com/forums/sho...merge-Tutorial
or:
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

[macropod]

Cross-posted at: http://windowssecrets.com/forums/sho...Doc-THEN-email
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[toughiv]

Originally Posted by Mjpunj

You will want to export the spreadsheet to an Excel workbook and create a pivot table, then place an empty cell above each company name, then use a macro to take all of the data below each individual company name and transpose it into columns to the right of the data. I will attach the macro you will need to use as a text file.

This is great. Now mail merging is a lot simpler! Thanks.

There is one slight prob i have now - when the macro transposes - the value of the date columns then becomes numerical. Is there a way I can retain the format of dd/mm/yyyy?

[toughiv]

Originally Posted by macropod

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://windowssecrets.com/forums/sho...merge-Tutorial
or:
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

I tried the examples etc. But it all seems to go way over my head. I have tried copying the code from this video @3.38 http://www.youtube.com/watch?v=4Ck8KAwAQso: making sure I got the spacing etc correct. Then I messed around with the bit at the end to try it out.

I have attached my file - should you be kind enough / altruistic enough to have a look!

I keep getting MERGESEQ or it JUST shows the invoice ref / amount.

Nothing relating to the other fields? :S

[toughiv]

Originally Posted by toughiv

This is great. Now mail merging is a lot simpler! Thanks.

There is one slight prob i have now - when the macro transposes - the value of the date columns then becomes numerical. Is there a way I can retain the format of dd/mm/yyyy?

done this - just edited the pastevalues part to pasteall.

Thanks

[macropod]

Originally Posted by toughiv

I have tried copying the code from this video @3.38 http://www.youtube.com/watch?v=4Ck8KAwAQso: making sure I got the spacing etc correct. Then I messed around with the bit at the end to try it out.

I have attached my file - should you be kind enough / altruistic enough to have a look!

Your field code has a few errors. As for the video, I hadn't seen it before, but it is both misleading and inaccurate:
1. The field codes on the Microsoft website are just plain text and can't be used as is. The Microsoft website field codes are also quite different - I don't vouch for them; and
2. The field code the video purports to insert from the tutorial has different content to any that the tutorial has ever had. In other words, it wasn't imported as-is from the tutorial. What the video omits is that whoever produced it substantially edited the field codes beforehand. It also fails to mention that you need a dummy last record in the data source.

See attached revised version of your document. I should also point out that, whereas your thread title indicates a desire to "Send Via Email Merge", the process you're using is not for that - it is only for producing an output document or sending the output direct to the printer. The tutorial has an entirely different process for email merges.

[toughiv]

Originally Posted by macropod

Your field code has a few errors. As for the video, I hadn't seen it before, but it is both misleading and inaccurate:
1. The field codes on the Microsoft website are just plain text and can't be used as is. The Microsoft website field codes are also quite different - I don't vouch for them; and
2. The field code the video purports to insert from the tutorial has different content to any that the tutorial has ever had. In other words, it wasn't imported as-is from the tutorial. What the video omits is that whoever produced it substantially edited the field codes beforehand. It also fails to mention that you need a dummy last record in the data source.

See attached revised version of your document. I should also point out that, whereas your thread title indicates a desire to "Send Via Email Merge", the process you're using is not for that - it is only for producing an output document or sending the output direct to the printer. The tutorial has an entirely different process for email merges.

You are a genius. How long you been doing advanced MW?

I tried emailing, said it was incompatible. However, mail merging to new doc - then sending via email merge should work no?

Or would it require a different if then else rule? I.e. if company name = 1 then send to 'x' email?

Thanks Macro

[macropod]

As I said in my previous reply:
"The tutorial has an entirely different process for email merges."

I've been using Word for 25+ years.

[toughiv]

Originally Posted by macropod

As I said in my previous reply:
"The tutorial has an entirely different process for email merges."

I've been using Word for 25+ years.

Ok I took your advice and ditched trying to do it the other way. I have been working on the tutorial.

Seems easier than I thought at first. Maybe it's because I am becoming accustomed to all of the {MERGEFIELD} meanings.

I am running into a problem though.

When I am following the tutorial section : Merging by Catalog/Directory to E-Mail

I get stuck on exactly what it means to:

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.

I have succesfully merged and done the macro. Now however, it merges and just sends the text I wish for the body of the email to include. Im a little lost?

Any help would be appreciated.

*to clarify my issue -
1) I dont quite understand why I am putting data fields into the "email merge main document"
2) isnt that already done by the directory?
3) If not, what does it mean exactly? That I should match the same fields that are in the directory? i.e. Company_Name, etc...and then insert those into the "email merge main document"?
4) Why then does the macro enabled document contain a table at all? Surely that inserts itself into the "email main merge document" and aligns with the email body?

Hope that makes it clearer? Sorry for long post

Thanks

[macropod]

Originally Posted by toughiv

I have succesfully merged and done the macro. Now however, it merges and just sends the text I wish for the body of the email to include. Im a little lost?

Any help would be appreciated.

That is how the email merge is meant to work. Done properly, it will include the letter and the data as the email body. As I've already said, the process is quite different from what you were doing before. The reason for the setup it requires is that it actually performs two merges - one being a directory merge to consolidate the data to be emailed into a table that becomes a new mailmerge source file, the other to actually send the emails using that new source file. Instead of executing either merge manually, let the macro drive the entire process.

If you want to send emails with the letters as attachments, you'd need a different process from what the tutorial caters for. See, for example: http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm. Even that process assumes you have separate output documents that the macro there can attach. To create those, you'd need to use a mailmerge with the document form post #8 to create an initial output document, split that into separate documents, then complete the balance of the process in the above link.

[toughiv]

ah ok!

Thanks for your time and patience macro - really do appreciate it!

Have a great w/e (or whats left of it)

[toughiv]

Macro - I got a problem?

I tested the macro enabled mail merge today.

I only get one email sent?

I did it to a test account. All of the email receipients have the same email.

1) Do I have to input {MERGEFIELD Data} multiple times into my email merge main document? ... in order to get multiple entries?

2) Do i have to have the email merge main document connected to the same data source as the macro enabled directory?

3) The directory isnt set up to have multiple fields for each row...i.e. i havent transposed the rows...that's what im trying to get around.

So cannot see the directory needing multiple {MERGEFIELD INV_Ref} etc.

I hope you've heard of this before?

Thanks

[macropod]

Originally Posted by toughiv

I tested the macro enabled mail merge today.

I only get one email sent?

I did it to a test account. All of the email receipients have the same email.

Have you checked the contents of the 'EmailDataSource.doc' file to see what your directory merge generated? If you set up the Key field in your Catalog/Directory mailmerge main document to use the email addresses as the key, that would result in only one email being sent.

1) Do I have to input {MERGEFIELD Data} multiple times into my email merge main document? ... in order to get multiple entries?

2) Do i have to have the email merge main document connected to the same data source as the macro enabled directory?

3) The directory isnt set up to have multiple fields for each row...i.e. i havent transposed the rows...that's what im trying to get around.

You only need to follow the instructions. There is nothing extra (or less) that you need to do.