| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Single but Multiple records
Hi jacqui! Did you solve your issue? I have the same question as you. Would
be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
What is the error message?
When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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" wrote in message ... 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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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 "Doug Robbins - Word MVP" wrote: 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" wrote in message ... 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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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 ToolsReferences '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" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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" wrote in message ... 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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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 "Doug Robbins - Word MVP" wrote: 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 ToolsReferences '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" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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" wrote in message ... 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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.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" wrote in message ... Hi jacqui! Did you solve your issue? I have the same question as you. Would be possible with this Macro?? Thanks!! -- mery "jacquieamer (removethis) @earthlink.net" wrote: 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. . |
|
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
I Just sent you an e-mail with all the files!! Thanks!!
-- mery "Doug Robbins - Word MVP" wrote: Are you using a Directory Type mail merge main document? You can send the files to me at -- 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" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: Why don't you send me all of the documents that you have created (the 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 ToolsReferences '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 & 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" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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" wrote in message ... 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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.zip |
|
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Single but Multiple records
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 "maria" wrote in message ... I Just sent you an e-mail with all the files!! Thanks!! -- mery "Doug Robbins - Word MVP" wrote: Are you using a Directory Type mail merge main document? You can send the files to me at -- 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" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: Why don't you send me all of the documents that you have created (the 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 ToolsReferences '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 & 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" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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" wrote in message ... 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 "Peter Jamieson" wrote: What is the error message? When do you see it? Which version of Word? What is the data source? Peter Jamieson "maria" wrote in message ... 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 "Doug Robbins - Word MVP" wrote: 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%20Mailmerge.zip |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple records on single merge doc | Mailmerge | |||
| Multiple records on a single mail merge - another question | Mailmerge | |||
| Mailmerge multiple Excel records for one name to a single Word Doc | Mailmerge | |||
| Multiple records into a single document | Mailmerge | |||
| How do I get multiple Access records into a single mailmerge lett. | Mailmerge | |||
Linear Mode
