Single but Multiple records

[LDanix]

I have a spreadsheet in Excel that contains my employees' data (name, ID,
manager, etc.) along with "coaching issues". Every item has its own column.
The majority of the time, a person will be listed several times with several
different coaching issues.

Is it possible for the Mailmerge to create one page that includes the
employee's data (one time) and all the records under his/her name (such as in
a table format); and do this for all employee's listed on the spreadsheet? I
am trying to get away from creating a page for every issue under the same
employee.

My idea is to create a small but comprehensive report that I may be able to
give each employee at the end of the month. One glance sees everything,
without having to flip from page to page for the same employee.

 

[Peter Jamieson]

Word isn't designed for this but see Cindy Meister's article at

http://homepage.swissonline.ch/cindymeister/

go to Mailmerge FAQ, Special merges, "multiple items per condition".

If you have Access, a better way to do this would probably be to store your
data in Access, or import it or link to it, and use Access reporting
instead. Logically, you would need to separate your data into two tables.
More up-front work though!

 

[Doug Robbins]

If you use a Catalog (or in Word XP and later, a Directory) type mailmerge
main document with the merge fields in the cells of a single row table in
the mailmerge main document with the "key" field in the first column, and
then you execute that merge to a new document and run the following macro on
that document, it will produce a result similar to that which you are after:

' Macro created by Doug Robbins to create multiple items per condition from
a directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat <> tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[jacquieamer \(removethis\)]

In the spreadsheet list the employee data, but have
columns named "CoachingIssue1" "Coaching Issue2", etc.

 

[maria]

Hi jacqui! Did you solve your issue? I have the same question as you. Would
be possible with this Macro??
Thanks!!

 

[Doug Robbins - Word MVP]

See fellow MVP Macropod's "Word 97-2007 Catalogue/Directory Mailmerge
Tutorial" at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included
with it as you must get the mail merge main document set up exactly as
required.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[maria]

Hi Doug,

Unfortunately I follow all the steps into the Tutorial but is not possible
for me to run the Macro as it always shows an error message.
Could be possible for you to send me an example maybe?

Thanks a lot!!!

 

[Peter Jamieson]

What is the error message?

When do you see it?

Which version of Word?

What is the data source?

Peter Jamieson

 

[maria]

hi Peter,

i'm sending you an example of that I wanted to merge:

City Employee Sales
Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0
I need to send ONLY ONE e-mail for "Atlanta" with the 3 employees (Smith,
Gates & Henderson) details.
When I running the Macro, after insert the "merge fields" the message error
is the following: "Run-Time error 5941" the Macro that I'm using is exactly
as the tutorial (I copied from the tutorial).

thank you very much, I really appreciated you help on this!

 

[Doug Robbins - Word MVP]

Did you copy both macros - Sub EmailMergeTableMaker() and the Sub
TableJoiner()?

Have you created AND EXECUTED the multi-table merge to create a document
like that shown in the tutorial?

Is that document the active document when you run the EmailMergeTableMaker
macro?

If you click on Debug, what line of code is highlighted in yellow?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[maria]

Yes I copied both Macros and also I created the Multi table with <<>> fields.
The line of code highlighted in yellow is "With .Tables(1)"
Another question is, should I run both Macros separtly (create 2 macros) or
only one that contains the first and the second one is ok?

Thank you very much for your time!

 

[Doug Robbins - Word MVP]

Why don't you send me all of the documents that you have created (the mail
merge main documents, the data source and the documents created by executing
the merges) so that I can see exactly what you have.

However, if your data is in a table in the following format:

Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0

Or you execute a directory type mail merge to create a document with a table
such as that. If you run the following macro when that document is the
active document, it will ask you for the email address for each city and
then compose and send email messages with the subject of [City] Sales Data
and the following typical information in the body of the email message

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

'To run this macro it is necessary to set a reference via Tools>References
'in the Visual Basic Editor to the Microsoft Outlook [version number] Object
Library
Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Dim emaddress As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim mysubject As String, message As String, Title As String
' Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
'oOutlookApp.DefaultProfileName
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(1, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
For i = 2 To stab.Rows.Count
Set tcat = ttab.Cell(1, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat = tcat Then
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n).Range = data
Next n
Else
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat &
"Email Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
With ttab
For j = .Rows.Count To 2 Step -1
.Rows(j).Delete
Next j
End With
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
End If
Next i
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat & "Email
Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
' Close Outlook if it was started by this macro.
If bStarted Then
oOutlookApp.Quit
End If

'Clean up
Set oOutlookApp = Nothing




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[maria]

Hi Doug,
I created an excel file with the data source. A template in word where I
excecute the merges fields and then I create the merge documents.

This is exactly what I'm looking for:

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

After obtain the merge doc, it ask me for the name of each person, that's
correct and it works!!!, I already clik the option Microsoft Outlook 11.0
Object
Library.

Besides, I tried with the Macro you sent me above but also show me an error
message.

I wanted to send you all the files that I have, but I cannot attach any file
here.
But, keep in mind, that I only use an excel file (with data source) a word
template where I merge the fields and also the people directory from my
outlook.

I only get this: mail 1: Atlanta Smith $3,000
mail 2: Atlanta Gates $50,000
mail 3: Atlanta Henderson $10,000

And I trying to get : mail 1: Atlanta Smith $3,000
Gates
$50,000
Henderson
$10,000
mail 2: Houston.....etc.

Thanks a lot!!!!!!!!




--
mery

Why don't you send me all of the documents that you have created (the mail
merge main documents, the data source and the documents created by executing
the merges) so that I can see exactly what you have.

However, if your data is in a table in the following format:

Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0

Or you execute a directory type mail merge to create a document with a table
such as that. If you run the following macro when that document is the
active document, it will ask you for the email address for each city and
then compose and send email messages with the subject of [City] Sales Data
and the following typical information in the body of the email message

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

'To run this macro it is necessary to set a reference via Tools>References
'in the Visual Basic Editor to the Microsoft Outlook [version number] Object
Library
Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Dim emaddress As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim mysubject As String, message As String, Title As String
' Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
'oOutlookApp.DefaultProfileName
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(1, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
For i = 2 To stab.Rows.Count
Set tcat = ttab.Cell(1, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat = tcat Then
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n).Range = data
Next n
Else
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat &
"Email Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
With ttab
For j = .Rows.Count To 2 Step -1
.Rows(j).Delete
Next j
End With
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
End If
Next i
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat & "Email
Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
' Close Outlook if it was started by this macro.
If bStarted Then
oOutlookApp.Quit
End If

'Clean up
Set oOutlookApp = Nothing




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Doug Robbins - Word MVP]

Are you using a Directory Type mail merge main document?

You can send the files to me at (e-mail address removed)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,
I created an excel file with the data source. A template in word where I
excecute the merges fields and then I create the merge documents.

This is exactly what I'm looking for:

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

After obtain the merge doc, it ask me for the name of each person, that's
correct and it works!!!, I already clik the option Microsoft Outlook 11.0
Object
Library.

Besides, I tried with the Macro you sent me above but also show me an
error
message.

I wanted to send you all the files that I have, but I cannot attach any
file
here.
But, keep in mind, that I only use an excel file (with data source) a word
template where I merge the fields and also the people directory from my
outlook.

I only get this: mail 1: Atlanta Smith $3,000
mail 2: Atlanta Gates $50,000
mail 3: Atlanta Henderson $10,000

And I trying to get : mail 1: Atlanta Smith $3,000
Gates
$50,000
Henderson
$10,000
mail 2: Houston.....etc.

Thanks a lot!!!!!!!!




--
mery

Why don't you send me all of the documents that you have created (the
mail
merge main documents, the data source and the documents created by
executing
the merges) so that I can see exactly what you have.

However, if your data is in a table in the following format:

Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0

Or you execute a directory type mail merge to create a document with a
table
such as that. If you run the following macro when that document is the
active document, it will ask you for the email address for each city and
then compose and send email messages with the subject of [City] Sales
Data
and the following typical information in the body of the email message

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

'To run this macro it is necessary to set a reference via
Tools>References
'in the Visual Basic Editor to the Microsoft Outlook [version number]
Object
Library
Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Dim emaddress As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim mysubject As String, message As String, Title As String
' Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
'oOutlookApp.DefaultProfileName
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(1, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
For i = 2 To stab.Rows.Count
Set tcat = ttab.Cell(1, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat = tcat Then
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n).Range = data
Next n
Else
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat
&
"Email Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
With ttab
For j = .Rows.Count To 2 Step -1
.Rows(j).Delete
Next j
End With
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
End If
Next i
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat &
"Email
Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
' Close Outlook if it was started by this macro.
If bStarted Then
oOutlookApp.Quit
End If

'Clean up
Set oOutlookApp = Nothing




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Yes I copied both Macros and also I created the Multi table with <<>>
fields.
The line of code highlighted in yellow is "With .Tables(1)"
Another question is, should I run both Macros separtly (create 2
macros)
or
only one that contains the first and the second one is ok?

Thank you very much for your time!
--
mery


:

Did you copy both macros - Sub EmailMergeTableMaker() and the Sub
TableJoiner()?

Have you created AND EXECUTED the multi-table merge to create a
document
like that shown in the tutorial?

Is that document the active document when you run the
EmailMergeTableMaker
macro?

If you click on Debug, what line of code is highlighted in yellow?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
hi Peter,

i'm sending you an example of that I wanted to merge:

City Employee Sales
Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0
I need to send ONLY ONE e-mail for "Atlanta" with the 3 employees
(Smith,
Gates & Henderson) details.
When I running the Macro, after insert the "merge fields" the
message
error
is the following: "Run-Time error 5941" the Macro that I'm using is
exactly
as the tutorial (I copied from the tutorial).

thank you very much, I really appreciated you help on this!
--
mery


:

What is the error message?

When do you see it?

Which version of Word?

What is the data source?

Peter Jamieson


Hi Doug,

Unfortunately I follow all the steps into the Tutorial but is not
possible
for me to run the Macro as it always shows an error message.
Could be possible for you to send me an example maybe?

Thanks a lot!!!

--
mery


:

See fellow MVP Macropod's "Word 97-2007 Catalogue/Directory
Mailmerge
Tutorial" at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document
included
with it as you must get the mail merge main document set up
exactly
as
required.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself
of
my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via
msnews.microsoft.com
Hi jacqui! Did you solve your issue? I have the same question
as
you.
Would
be possible with this Macro??
Thanks!!
--
mery


:

In the spreadsheet list the employee data, but have
columns named "CoachingIssue1" "Coaching Issue2", etc.
-----Original Message-----
I have a spreadsheet in Excel that contains my
employees' data (name, ID,
manager, etc.) along with "coaching issues". Every item
has its own column.
The majority of the time, a person will be listed
several times with several
different coaching issues.

Is it possible for the Mailmerge to create one page that
includes the
employee's data (one time) and all the records under
his/her name (such as in
a table format); and do this for all employee's listed
on the spreadsheet? I
am trying to get away from creating a page for every
issue under the same
employee.

My idea is to create a small but comprehensive report
that I may be able to
give each employee at the end of the month. One glance
sees everything,
without having to flip from page to page for the same
employee.
.

 

[maria]

I Just sent you an e-mail with all the files!! Thanks!!
--
mery

Are you using a Directory Type mail merge main document?

You can send the files to me at (e-mail address removed)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,
I created an excel file with the data source. A template in word where I
excecute the merges fields and then I create the merge documents.

This is exactly what I'm looking for:

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

After obtain the merge doc, it ask me for the name of each person, that's
correct and it works!!!, I already clik the option Microsoft Outlook 11.0
Object
Library.

Besides, I tried with the Macro you sent me above but also show me an
error
message.

I wanted to send you all the files that I have, but I cannot attach any
file
here.
But, keep in mind, that I only use an excel file (with data source) a word
template where I merge the fields and also the people directory from my
outlook.

I only get this: mail 1: Atlanta Smith $3,000
mail 2: Atlanta Gates $50,000
mail 3: Atlanta Henderson $10,000

And I trying to get : mail 1: Atlanta Smith $3,000
Gates
$50,000
Henderson
$10,000
mail 2: Houston.....etc.

Thanks a lot!!!!!!!!




--
mery

Why don't you send me all of the documents that you have created (the
mail
merge main documents, the data source and the documents created by
executing
the merges) so that I can see exactly what you have.

However, if your data is in a table in the following format:

Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0

Or you execute a directory type mail merge to create a document with a
table
such as that. If you run the following macro when that document is the
active document, it will ask you for the email address for each city and
then compose and send email messages with the subject of [City] Sales
Data
and the following typical information in the body of the email message

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

'To run this macro it is necessary to set a reference via
Tools>References
'in the Visual Basic Editor to the Microsoft Outlook [version number]
Object
Library
Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Dim emaddress As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim mysubject As String, message As String, Title As String
' Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
'oOutlookApp.DefaultProfileName
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(1, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
For i = 2 To stab.Rows.Count
Set tcat = ttab.Cell(1, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat = tcat Then
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n).Range = data
Next n
Else
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat
&
"Email Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
With ttab
For j = .Rows.Count To 2 Step -1
.Rows(j).Delete
Next j
End With
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
End If
Next i
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat &
"Email
Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
' Close Outlook if it was started by this macro.
If bStarted Then
oOutlookApp.Quit
End If

'Clean up
Set oOutlookApp = Nothing




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Yes I copied both Macros and also I created the Multi table with <<>>
fields.
The line of code highlighted in yellow is "With .Tables(1)"
Another question is, should I run both Macros separtly (create 2
macros)
or
only one that contains the first and the second one is ok?

Thank you very much for your time!
--
mery


:

Did you copy both macros - Sub EmailMergeTableMaker() and the Sub
TableJoiner()?

Have you created AND EXECUTED the multi-table merge to create a
document
like that shown in the tutorial?

Is that document the active document when you run the
EmailMergeTableMaker
macro?

If you click on Debug, what line of code is highlighted in yellow?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
hi Peter,

i'm sending you an example of that I wanted to merge:

City Employee Sales
Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0
I need to send ONLY ONE e-mail for "Atlanta" with the 3 employees
(Smith,
Gates & Henderson) details.
When I running the Macro, after insert the "merge fields" the
message
error
is the following: "Run-Time error 5941" the Macro that I'm using is
exactly
as the tutorial (I copied from the tutorial).

thank you very much, I really appreciated you help on this!
--
mery


:

What is the error message?

When do you see it?

Which version of Word?

What is the data source?

Peter Jamieson


Hi Doug,

Unfortunately I follow all the steps into the Tutorial but is not
possible
for me to run the Macro as it always shows an error message.
Could be possible for you to send me an example maybe?

Thanks a lot!!!

--
mery


:

See fellow MVP Macropod's "Word 97-2007 Catalogue/Directory
Mailmerge
Tutorial" at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip

 

[Doug Robbins - Word MVP]

And I have sent a response. The main issue here was that your mail merge
main document was not of the required Directory type.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

I Just sent you an e-mail with all the files!! Thanks!!
--
mery

Are you using a Directory Type mail merge main document?

You can send the files to me at (e-mail address removed)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Hi Doug,
I created an excel file with the data source. A template in word where
I
excecute the merges fields and then I create the merge documents.

This is exactly what I'm looking for:

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

After obtain the merge doc, it ask me for the name of each person,
that's
correct and it works!!!, I already clik the option Microsoft Outlook
11.0
Object
Library.

Besides, I tried with the Macro you sent me above but also show me an
error
message.

I wanted to send you all the files that I have, but I cannot attach any
file
here.
But, keep in mind, that I only use an excel file (with data source) a
word
template where I merge the fields and also the people directory from my
outlook.

I only get this: mail 1: Atlanta Smith $3,000
mail 2: Atlanta Gates $50,000
mail 3: Atlanta Henderson $10,000

And I trying to get : mail 1: Atlanta Smith $3,000
Gates
$50,000
Henderson
$10,000
mail 2: Houston.....etc.

Thanks a lot!!!!!!!!




--
mery


:

Why don't you send me all of the documents that you have created (the
mail
merge main documents, the data source and the documents created by
executing
the merges) so that I can see exactly what you have.

However, if your data is in a table in the following format:

Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0

Or you execute a directory type mail merge to create a document with a
table
such as that. If you run the following macro when that document is
the
active document, it will ask you for the email address for each city
and
then compose and send email messages with the subject of [City] Sales
Data
and the following typical information in the body of the email message

Atlanta Smith $3,000
Gates $50,000
Henderson $10,000

'To run this macro it is necessary to set a reference via
Tools>References
'in the Visual Basic Editor to the Microsoft Outlook [version number]
Object
Library
Dim source As Document, target As Document, scat As Range, tcat As
Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Dim emaddress As String
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim mysubject As String, message As String, Title As String
' Check if Outlook is running. If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
'oOutlookApp.DefaultProfileName
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(1, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
For i = 2 To stab.Rows.Count
Set tcat = ttab.Cell(1, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat = tcat Then
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n).Range = data
Next n
Else
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat,
tcat
&
"Email Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
With ttab
For j = .Rows.Count To 2 Step -1
.Rows(j).Delete
Next j
End With
ttab.Cell(1, 1).Range = scat
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(1, n).Range = data
Next n
End If
Next i
target.Range.Copy
emaddress = InputBox("Insert the email address for " & tcat, tcat &
"Email
Address")
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = tcat & " Sales Data"
.BodyFormat = olFormatHTML
.Display
Set objDoc = .GetInspector.WordEditor
Set objSel = objDoc.Windows(1).Selection
objSel.Paste
.To = emaddress
.Send
End With
Set oItem = Nothing
' Close Outlook if it was started by this macro.
If bStarted Then
oOutlookApp.Quit
End If

'Clean up
Set oOutlookApp = Nothing




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Yes I copied both Macros and also I created the Multi table with
<<>>
fields.
The line of code highlighted in yellow is "With .Tables(1)"
Another question is, should I run both Macros separtly (create 2
macros)
or
only one that contains the first and the second one is ok?

Thank you very much for your time!
--
mery


:

Did you copy both macros - Sub EmailMergeTableMaker() and the Sub
TableJoiner()?

Have you created AND EXECUTED the multi-table merge to create a
document
like that shown in the tutorial?

Is that document the active document when you run the
EmailMergeTableMaker
macro?

If you click on Debug, what line of code is highlighted in yellow?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of
my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
hi Peter,

i'm sending you an example of that I wanted to merge:

City Employee Sales
Atlanta Smith $3,000
Atlanta Gates $50,000
Atlanta Henderson $10,000
Houston Jones $8,000
Houston Kelley $9,000
Houston Peterson $0
I need to send ONLY ONE e-mail for "Atlanta" with the 3 employees
(Smith,
Gates & Henderson) details.
When I running the Macro, after insert the "merge fields" the
message
error
is the following: "Run-Time error 5941" the Macro that I'm using
is
exactly
as the tutorial (I copied from the tutorial).

thank you very much, I really appreciated you help on this!
--
mery


:

What is the error message?

When do you see it?

Which version of Word?

What is the data source?

Peter Jamieson


Hi Doug,

Unfortunately I follow all the steps into the Tutorial but is
not
possible
for me to run the Macro as it always shows an error message.
Could be possible for you to send me an example maybe?

Thanks a lot!!!

--
mery


:

See fellow MVP Macropod's "Word 97-2007 Catalogue/Directory
Mailmerge
Tutorial" at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip