Mail Merge using Multiple Rows

B

BruceH

New Member
Joined
May 19, 2011
Messages
1
Excel 2010 & Word 2010


I have a spreadsheet with 600 records (Rows). I am trying to populate a table in a word document from multiple records in spreadsheet as part of my mail merge.

As an example:

The first 40 rows need to be in the first document, the next 7 rows go into another document etc.

I have a column in the spreadsheet that could be used to identify which records need to go into each merge document, but I am stumped on how to get the merge process to populate my table (Or create a table) and also how to get it to generate the multiple documents.

I understand basic mail merges, but I am way over my level of expertise on this, do you have any ideas?
 
Sort by date Sort by votes
John_w said:
Your data seems different to what you posted in #5. Before I spend any more time on this, which data/layout is correct?

Also, with the macro recorder I was referring to the MS web page merge definition because I can see what the layout is meant to be. It will be difficult to create a merge definition manually or automatically without knowing exactly what you want your merged data layout to look like, so you need to tell me. Do you want something like the following?

VendorID1
DocNo1 PayDate ID InvoiceNo Payment EmailAddress
DocNo2 PayDate ID InvoiceNo Payment EmailAddress

-------- new page ------------

VendorID2
DocNo3 PayDate ID InvoiceNo Payment EmailAddress
DocNo4 PayDate ID InvoiceNo Payment EmailAddress
DocNo5 PayDate ID InvoiceNo Payment EmailAddress


Also, are DocNo PayDate ID VendorID InvoiceNo Payment EmailAddress the exact column headings (A-G) in row 1 of your Excel sheet?
Click to expand...

Thanks.

Sorry, I need exactly to the above. Is there any chance it pick up the email address and send out email?

DocNo (A) PayDate ID (B) PayID (C) VendorID(D) InvoiceNo(E) Payment( F)EmailAddress (G)
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
psamu said:
Is there any chance it pick up the email address and send out email?
Click to expand...
From what I've read - Directory mail merge and emailing recipient | Windows Secrets Lounge and Catalogue/Directory Mail Merge to email = Hitting a brick Wall! Help, Please...! - you can't combine a catalogue/directory mail merge and a mail merge to email. Therefore I think you'll need to find/write code to do the mail merge directly from Excel to Outlook, bypassing Word altogether - there should be example code and tutorials for this if you search.

Here is the merge definition for your data layout - I've added column headings to the layout.

{ IF { MERGESEQ } = "1" "{ MERGEFIELD VendorID }¶
Doc Pay Date Pay ID Invoice Payment" "" }{ SET Vendor1 {
MERGEFIELD VendorID } }¶
{ IF { Vendor2 } <> { Vendor1 }"¶
----------------Page Break -------------------
{ MERGEFIELD VendorID }¶
Doc Pay Date Pay ID Invoice Payment¶
{ MERGEFIELD DocNo } { MERGEFIELD PayDate } { MERGEFIELD PayID }
{ MERGEFIELD InvoiceNo } { MERGEFIELD Payment }" "{
MERGEFIELD DocNo } { MERGEFIELD PayDate } { MERGEFIELD PayID }
{ MERGEFIELD InvoiceNo } { MERGEFIELD Payment }" }{ SET
Vendor2 { MERGEFIELD VendorID } }

The following macro creates the above merge fields (there are tabs rather than spaces between each column heading and value, however these tabs don't show in the above definition. Put the code in the NewMacros module of your Word main document and then run Macro2.
Code: 
Sub Macro2()
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="IF "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGESEQ"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=" = ""1"" """
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeParagraph
    Selection.TypeText Text:="Doc" & vbTab & vbTab & "Pay Date" & vbTab & _
        "Pay ID" & vbTab & "Invoice" & vbTab & vbTab & "Payment"" """""
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="SET Vendor1 "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeParagraph
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=4
    Selection.TypeParagraph
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="IF "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="Vendor2"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=" <> "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="Vendor1"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=""""
    Selection.TypeParagraph
    Selection.InsertBreak Type:=wdPageBreak
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeParagraph
    Selection.TypeText Text:="Doc" & vbTab & vbTab & "Pay Date" & vbTab & _
        "Pay ID" & vbTab & "Invoice" & vbTab & vbTab & "Payment"
    Selection.TypeParagraph
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD DocNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayDate"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD InvoiceNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD Payment"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=""" """
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD DocNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayDate"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD InvoiceNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD Payment"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=""""
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="SET "
    Selection.TypeText Text:="Vendor2 "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=4
End Sub
You'll need to set up your main document as a Directory/Catalogue mail merge and assign the Data Source before doing the mail merge. But as I said, it looks like your request isn't possible using a Word mail merge. That's really all I can help you with on this.
 
Upvote 0
John_w said:
From what I've read - Directory mail merge and emailing recipient | Windows Secrets Lounge and Catalogue/Directory Mail Merge to email = Hitting a brick Wall! Help, Please...! - you can't combine a catalogue/directory mail merge and a mail merge to email. Therefore I think you'll need to find/write code to do the mail merge directly from Excel to Outlook, bypassing Word altogether - there should be example code and tutorials for this if you search.

Here is the merge definition for your data layout - I've added column headings to the layout.

{ IF { MERGESEQ } = "1" "{ MERGEFIELD VendorID }¶
Doc Pay Date Pay ID Invoice Payment" "" }{ SET Vendor1 {
MERGEFIELD VendorID } }¶
{ IF { Vendor2 } <> { Vendor1 }"¶
----------------Page Break -------------------
{ MERGEFIELD VendorID }¶
Doc Pay Date Pay ID Invoice Payment¶
{ MERGEFIELD DocNo } { MERGEFIELD PayDate } { MERGEFIELD PayID }
{ MERGEFIELD InvoiceNo } { MERGEFIELD Payment }" "{
MERGEFIELD DocNo } { MERGEFIELD PayDate } { MERGEFIELD PayID }
{ MERGEFIELD InvoiceNo } { MERGEFIELD Payment }" }{ SET
Vendor2 { MERGEFIELD VendorID } }

The following macro creates the above merge fields (there are tabs rather than spaces between each column heading and value, however these tabs don't show in the above definition. Put the code in the NewMacros module of your Word main document and then run Macro2.
Code: 
Sub Macro2()
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="IF "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGESEQ"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=" = ""1"" """
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeParagraph
    Selection.TypeText Text:="Doc" & vbTab & vbTab & "Pay Date" & vbTab & _
        "Pay ID" & vbTab & "Invoice" & vbTab & vbTab & "Payment"" """""
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="SET Vendor1 "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeParagraph
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=4
    Selection.TypeParagraph
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="IF "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="Vendor2"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=" <> "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="Vendor1"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=""""
    Selection.TypeParagraph
    Selection.InsertBreak Type:=wdPageBreak
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeParagraph
    Selection.TypeText Text:="Doc" & vbTab & vbTab & "Pay Date" & vbTab & _
        "Pay ID" & vbTab & "Invoice" & vbTab & vbTab & "Payment"
    Selection.TypeParagraph
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD DocNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayDate"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD InvoiceNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD Payment"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=""" """
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD DocNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayDate"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD PayID"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD InvoiceNo"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=vbTab
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD Payment"
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.TypeText Text:=""""
    Selection.MoveRight Unit:=wdCharacter, Count:=2
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="SET "
    Selection.TypeText Text:="Vendor2 "
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="MERGEFIELD VendorID"
    Selection.MoveRight Unit:=wdCharacter, Count:=4
End Sub
You'll need to set up your main document as a Directory/Catalogue mail merge and assign the Data Source before doing the mail merge. But as I said, it looks like your request isn't possible using a Word mail merge. That's really all I can help you with on this.
Click to expand...

Thank you, really appreciate your time on this. Like you mentioned directory emailing is difficult. I could not find anything doing with in excel and emailing this. I have in the excel workbook with email and list items. Is there any chance if you come across something please let me know. Thank you again.
 
Upvote 0
Now it is working as directory list, but when I click on email mail merge then it seperate the page as single item
 
Upvote 0
Dear Sir,

Kindly assist me with the same situation but my columns names are

Guest NameArrDepRoom TypeRoom NoRoom NightsPoints
Guest117-Mar-1318-Mar-2013RCLK218112
Guest117-Mar-1318-Mar-2013RCLK111112
-------- new letter ------------ 24
Guest25-Mar-136-Mar-2013KING62018
Guest25-Mar-137-Mar-2013KING292216
Guest213-Mar-1315-Mar-2013KING392216
Guest213-Mar-1315-Mar-2013KING467216
Guest215-Mar-1316-Mar-2013KING57618
Guest215-Mar-1316-Mar-2013KING13018
-------- new letter ------------ 72
Guest313-Mar-1314-Mar-2013KING20318
Guest313-Mar-1314-Mar-2013KING46618

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

-------- new letter ------------ & So on & rows numbers are not fix. If possible this can this be in table formet in word through mail merge.

Request you to kindly assist me on the same
Thanks in advance
Gulzar
 
Upvote 0
There is one ms word adins for this type of mail merge, that work with excel. Directory mail merge is the solution, but separating pages you may have to get something like from here.
/http://www.gmayor.com/ManyToOne.htm
Merge Many To One
 
Upvote 0
psamu said:
There is one ms word adins for this type of mail merge, that work with excel. Directory mail merge is the solution, but separating pages you may have to get something like from here.
/http://www.gmayor.com/ManyToOne.htm
Merge Many To One
Click to expand...


Dear Sir,

If possible could you assist me with Macro or tool something that can help me

Thanks for your assistance
Gulzar
 
Upvote 0
Hello,

I am trying the GMAYOR add in to add multiple rows but I am getting an error message :

"Object variable or With block variable not set".

Do you know how to fix it?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Mail merge vba from excel
Replies
2
Views
432
js_odom
J
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
259
Joe4
Joe4
L
  • Question
Same Doc - Different Spreadsheets
Replies
8
Views
597
loract
L
C
  • Question
MAIL MERGE EXCEL + WORD + VBA DIRECTORY PROBLEM
Replies
1
Views
575
Cleitonis
C
A
  • Question
VBA Code help: TO: CC: BCC in Mail Merge
Replies
0
Views
2K
agutosay
A

Forum statistics

Threads
1,214,422
Messages
6,119,395
Members
448,891
Latest member
tpierce

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top