Mail merge - Multiple entries

[conksu]

If I want to do a mail merge, merging excel entries in word, how can I included multiple entries per identifier? For example, if I had a list of airline tickets matched with ID numbers and I wanted to send one email with all airline tickets matched with their ID number, how would I do that? Is there some kind of loop I can do?

[Bernie Deitrick]

conksu,

You would need to set up a table like so in Excel:

identifier Ticket1 Ticket2 ..... TicketN
Abcde 12456 12457
Fghijk 23456
Lmnop 34567 34568 34569

and then put the Ticket1...TicketN fields into your mail merge document.

You could set up the table using a macro, if that is the sticking point.

HTH,
Bernie
MS Excel MVP


"conksu" <conksu.23pdam_1140732600.4993@excelforum-nospam.com> wrote in message
news:conksu.23pdam_1140732600.4993@excelforum-nospam.com...
>
> If I want to do a mail merge, merging excel entries in word, how can I
> included multiple entries per identifier? For example, if I had a list
> of airline tickets matched with ID numbers and I wanted to send one
> email with all airline tickets matched with their ID number, how would
> I do that? Is there some kind of loop I can do?
>
>
> --
> conksu
> ------------------------------------------------------------------------
> conksu's Profile: http://www.excelforum.com/member.php...o&userid=31870
> View this thread: http://www.excelforum.com/showthread...hreadid=515995
>

[conksu]

The way my table is set up now is:

Name Ticket Ticketinfo1 Ticketinfo2....
ABCD 12134 Orig Dest
EFGH 94753 Orig Dest

etc.

My problem is, I will need to include the ticket info also, and I would like to include a chart in each mailing, similar to the original excel table, with ticket number and other info. But each entry is listed uniquely by ticket number rather than name, so one person could have multiple tickets that need to be included.

[Bernie Deitrick]

conksu,

Select a single cell in your database of tickets, then run the macro below. You will need to adjust
the row 1 headings on the new sheet but the macro will simplify your life a lot.

HTH,
Bernie
MS Excel MVP

Sub DBtoCrossTab()
Dim myCell As Range
Dim myTable As Range
Dim mySht As Worksheet
Dim myRow As Long

Set myTable = ActiveCell.CurrentRegion

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Cross Tab").Delete
Application.DisplayAlerts = True

Set mySht = Worksheets.Add
mySht.Name = "Cross Tab"

myTable.Rows(1).EntireRow.Copy mySht.Rows(1)

Set myTable = myTable.Offset(1, 0).Resize _
(myTable.Rows.Count - 1, myTable.Columns.Count)

For Each myCell In myTable.Columns(1).Cells
If IsError(Application.Match(myCell.Value, _
mySht.Range("A:A"), False)) Then
myCell.EntireRow.Copy _
mySht.Range("A65536").End(xlUp)(2).EntireRow
Else
myRow = Application.Match(myCell.Value, _
mySht.Range("A:A"), False)
myCell.Offset(0, 1).Resize(1, myTable.Columns.Count - 1).Copy _
mySht.Cells(myRow, 256).End(xlToLeft)(1, 2)
End If
Next myCell

End Sub


"conksu" <conksu.23w7cy_1141051501.0119@excelforum-nospam.com> wrote in message
news:conksu.23w7cy_1141051501.0119@excelforum-nospam.com...
>
> The way my table is set up now is:
>
> Name Ticket Ticketinfo1 Ticketinfo2....
> ABCD 12134 Orig Dest
> EFGH 94753 Orig Dest
>
> etc.
>
> My problem is, I will need to include the ticket info also, and I would
> like to include a chart in each mailing, similar to the original excel
> table, with ticket number and other info. But each entry is listed
> uniquely by ticket number rather than name, so one person could have
> multiple tickets that need to be included.
>
>
> --
> conksu
> ------------------------------------------------------------------------
> conksu's Profile: http://www.excelforum.com/member.php...o&userid=31870
> View this thread: http://www.excelforum.com/showthread...hreadid=515995
>

[conksu]

Yeeeeeaaaahhhh.....

I don't really know macros, so I don't know what that all means. I copied what you wrote and copied it into a macro, but the result was nothing I could use. I'm sure I'm doing something wrong, but if you could explain this I'd be your best friend.

[Bernie Deitrick]

conksu,

If you have a table that is set off by fully blank columns and fully blank
rows from the rest of your data, and you don't have any blanks within your
data table, then the macro will copy the first row (the headers of your
table) to the first row of a new worksheet, then look at the values in the
first column of the table, and copy the information in the table to either a
new row (if the name in the first column hasn't appeared before) or to the
same row but in columns to the right (if the name has already appeared).

If your data table looked like this:

Name Ticket Ticketinfo1 Ticketinfo2
ABCD 12134 Orig1 Dest1
EFGH 94753 Orig2 Dest2
ABCD 12135 Orig3 Dest3
EFGH 94754 Orig4 Dest4
IJKL 87654 Orig5 Dest5

Then the resulting table would look like

Name Ticket Ticketinfo1 Ticketinfo2
ABCD 12134 Orig1 Dest1 12135 Orig3 Dest3
EFGH 94753 Orig2 Dest2 94754 Orig4 Dest4
IJKL 87654 Orig5 Dest5

If your data table has blanks - for example, not all tickets have the field
Ticketinfo2 filled in, then the macro won't work as needed.

If you need help implementing the macro, you can send me a copy of your
workbook, and I will figure out how to modify the macro (or your workbook,
or both) to get it to work.

HTH,
Bernie
MS Excel MVP


"conksu" <conksu.23wipa_1141066200.7113@excelforum-nospam.com> wrote in
message news:conksu.23wipa_1141066200.7113@excelforum-nospam.com...
>
> Yeeeeeaaaahhhh.....
>
> I don't really know macros, so I don't know what that all means. I
> copied what you wrote and copied it into a macro, but the result was
> nothing I could use. I'm sure I'm doing something wrong, but if you
> could explain this I'd be your best friend.
>
>
> --
> conksu
> ------------------------------------------------------------------------
> conksu's Profile:
> http://www.excelforum.com/member.php...o&userid=31870
> View this thread: http://www.excelforum.com/showthread...hreadid=515995
>