 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / May 2004Using Mail Merge with multiple values
I created a Mail Merge document where I am using Excel to merge the data into my document. How can I list multiple records on the same document. Below are some Excel records that I am using. My document has a main header, which consist of the Patient's First Name, Last Name, Member#, Acct#, SSN and so on. The data displays on the header just fine. In the middle part of my document, I need to display some of the below listed information. For each patient, I need to list their corresponding DIAG code and DESC on that same form. For example, looking at the below data, Ted Smith's information should be displayed in the middle part of the document as follows. Code Desc 25000 DIABETES 4139 ANGINA 7140 ARTHRITIS After the above information is merged, then the next record for Mike Jones should be displayed on a new page. Is there a way to accomplish this task? My Excel spreadsheet consist of 49,000 rows, so doing something manual will take forever. If anyone knows of a way to help me or put me in the right direction, it will be greatly appreciated. Thanks, Patrick MEMBER# ACCT# LNAME FNAME DOB DIAG DESC 58936057600 17444 SMITH TED 07/03/1935 25000 DIABETES 58936057600 17444 SMITH TED 07/03/1935 4139 ANGINA 58936057600 17444 SMITH TED 07/03/1935 7140 ARTHRITIS 00496426000 12796 JONES MIKE 03/13/1931 4280 HEART FAIL 01242927100 28703 DOE JOHN 08/11/1929 25000 DIABETES 01242927100 28703 DOE JOHN 08/11/1929 41090 MYOCARDIAL Hi Patrick, Take a look at the discussion on doing "one-to-many" types of mail merges in the Special Merges section of the FAQ on my website. There are links to sample files/instructions for the three basic approaches. For simplicity's sake, if you can display the "many" list in a table format, I recommend the DATABASE field approach. > My document has a main header, which > consist of the Patient's First Name, Last Name, Member#, [quoted text clipped - 3 lines] > need to list their corresponding DIAG code and DESC on > that same form. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) Hi Cindy I am using merge to fax from Word 2002 and it is working fine. My database has format as below: Add1 Add2 Problem Fax aaa aaaa 11111 [fax:12345678] aaa aaaa 22222 [fax:12345678] bbb bbbb 123488 [fax:23456789] Using standard mail merge there will be 3 calls and 3 pages with problem and its description How can create multiple pages document (first 2 records) and only two calls will be needed to process all recepients list Thanks Zed > Hi Patrick, > [quoted text clipped - 21 lines] > This reply is posted in the Newsgroup; please post any follow > question or reply in the newsgroup and not by e-mail :-) Hi Zed, You may need to modify this to do exactly what you want: ' Macro to create multiple items per condition from a document created by a catalog or 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  SignaturePlease post any further questions or followup to the newsgroups for the benefit of others who may be interested. Unsolicited questions forwarded directly to me will only be answered on a paid consulting basis. Hope this helps Doug Robbins - Word MVP > Hi Cindy > [quoted text clipped - 42 lines] > > This reply is posted in the Newsgroup; please post any follow > > question or reply in the newsgroup and not by e-mail :-) Hi, Thanks for response I am interested how to solve my problem on conceptual level First question: Can be done without VBA If VBA has to be used , please provide only step by step procedure Thanks Zed > Hi Zed, > [quoted text clipped - 89 lines] > > > This reply is posted in the Newsgroup; please post any follow > > > question or reply in the newsgroup and not by e-mail :-) Hi Zed, > I am interested how to solve my problem on conceptual level > First question: Can be done without VBA > If VBA has to be used , please provide only step by step procedure Did you read up on the one-to-many information on my website, in the Special Merges section? I can assure you, we have no interest in re-typing that very basic information. That's why it's on the website :-) Follow-up questions to the various approaches listed there, yes. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) Hi Cindy, Thank you for sugestions and response Zed > Hi Zed, > [quoted text clipped - 17 lines] > This reply is posted in the Newsgroup; please post any follow > question or reply in the newsgroup and not by e-mail :-) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.