I've got a form letter which I'm using to mail merge with an Access db. I've got part of it working but it's limited in what it can do.

I need to have it modified so that it keeps looping all the way through the end of the merged document - rather than just 1 single record. How do I that? I've never written a looping VBA command before.

Secondly, in the first 'With' selection, it's not always going to be heart, other organs in this category may include Liver and some other types. So how have it search for multiple words so that I don't have to a separate selection.find line for each word?

Any help would be appreciated. Thanks.
=================================================================

Sub Delete_paragraphs()
'
' test_parse Macro
' Macro recorded 10/15/2005 by John
'
Selection.GoTo What:=wdGoToBookmark, Name:="Transplant_Type"
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Heart"
ActiveDocument.Bookmarks("spk_pancrease_only").Range.Delete
ActiveDocument.Bookmarks("BMT_para").Range.Delete
End With
With Selection.Find
.Text = "Kidney"
ActiveDocument.Bookmarks("BMT_para").Range.Delete
With Selection.Find
.Text = "Autologous"
ActiveDocument.Bookmarks("solids").Range.Delete
ActiveDocument.Bookmarks("spk_pancrease_only").Range.Delete
.Text = "Allogeneic"
ActiveDocument.Bookmarks("solids").Range.Delete
ActiveDocument.Bookmarks("spk_pancrease_only").Range.Delete
End Sub

Are you saying you've completed the merge and your document is effectively a set of documents, each representing a different record from the Access db?

I think you might find it easier to do a different merge for each organ and run it against a query in your db to limit the records to those appropriate for each merge.

If I understand correctly, something like this might loop through the records in the already merged document.

Sub StepThroughRecs()
With ActiveDocument.MailMerge.DataSource
For I = 1 To .RecordCount
.ActiveRecord = wdNextRecord
' Run Some Code on the individual record here
I = I + 1
Next I
End With
End Sub

There are many other options to loop but you want to avoid a loop that doesn't end.

>Are you saying you've completed the merge and your document
>is effectively a set of documents, each representing a
>different record from the Access db?
>
>I think you might find it easier to do a different merge for
>each organ and run it against a query in your db to limit the
>records to those appropriate for each merge.

Yes, that's right Steve. The only problem is that the database doesn't contain the "Transplant Type" (organ) information so I have been typing that in by hand. To further clarify, what i've got is a form letter going out to various hospitals around the country regarding organ transplants - basically they boil down to 3 types. Category 1, 2, & 3 and each of them except for category 2, include subitems within that category. So some hopsitals specialize in Category 1 only, but often a hospital may specialize in a combination of 2 or 3 of these categories. The macro that I wrote is a basic crude attempt so that based upon what I call the "keyword" (Transplant Type), only certain paragraphs get deleted.

This is my first time trying to write a VBA code of my own so i'm really faltering badly. I heard there is a conditional mail merge feature available in Word using If...Then statements but i'm not sure if that will meet my needs.

Your copy and paste comment should work just fine.

The frustrating part of all of this is that I think there may be easier ways to achieve what you want but we're dealing with medical information and privacy issues. Were it not for that, I'd ask you for an emailed copy of the database.

>Your copy and paste comment should work just fine.
>
>The frustrating part of all of this is that I think there may
>be easier ways to achieve what you want but we're dealing with
>medical information and privacy issues. Were it not for that,
>I'd ask you for an emailed copy of the database.

Steve, you have no idea how how fustrating this has been for a newbie like myself - I'm really lost here but I'm determined to somehow make this work. This is a major part of my daily duties at this temp job and I've already fallen behind in my workload because of this. I have got to somehow make this process easier because there are several other manual steps involved here which I don't need to get into, such as preparing binders with various documents in it. Very similar to what I'm doing in MS Word.

Tonight I created a created a fictional database in the mail merge wizard. Just a few records - do you want me to send it along w/ the form letter ?

EDIT: Actually Steve, even the actual db at work doesn't contain any private patient information. The merge fields simply pertain to only the address/ contact infomration and the organ transplant type they specialize in.

If the db doesn't contain patient info and it isn't otherwise confidential, I can look at it. Send to syandl@comcast.net.

In the end, it might be easier to create a different template for each of the multiple categories, even if that ends up being a dozen or more. They can all be based on the same parent document but customized for the institution type.

How do you know organ type if it isn't in the database? I suspect you have a separate table where you look up info that isn't already in the db.

>How do you know organ type if it isn't in the database? I
>suspect you have a separate table where you look up info that
>isn't already in the db.

Yes, i have a hard copy here with a list of hospitals and the transplant type they specialize in. I've been manually typing that part into the final letter. I'll send it right away - thanks Steve.

I think you may want to do much of this in the database (if you can edit the Access database) or export the key table to Excel and add a few fields. The merged document is actually a single document married to a set of records so edits to parts of the document that are not part of some merge field will be the same for each record. You could create some new calculated fields that would contain the entire paragraph you want inserted for certain records and actually place the paragraph in the database multiple times. With short paragraphs like your letter has, this might be an easier solution that what you started.

When you do the mail merge in Word, you can filter which records get merged based on values in some field (effectively running a query). When the small copy of the database pops up in the wizard, you click one of the down arrows in one of the column headings and select 'Advanced' Suppose you have a column heading named "Organ", then you can only merge to the records where the value in that field is "heart" as one example.

If you're only doing one or a few letters at a time, you might still want to use VBA but you might actually be better off with the VBA located in the Access database and have it create new word documents as required. If you do stick with the VBA route, here are a few answers to your earlier questions.

Typically, if you're moving through the document and editing paragraphs and bookmarks, you want to return to the selection point you were at when you started the routine. Do this with something like:
----------------
Dim rngStartMark As Word.Range
Set rngStartMark = Selection.Range
' Run your find and modify routine
rngStartMark.Select
----------------------

If you want to run some part of the code only if the word "heart" appears somewhere in the range labeled by the bookmark named "keyrange" you could have something like:
----------------------
If InStr(ActiveDocument.Bookmarks("keyrange").Range.Text, "heart") > 0 Then
' Run some code
End If


Edit to add some ideas on using Excel to do the work before a mail merge:

Suppose you export your table from the Access database as an Excel Workbook. The table will appear on Sheet1. Let's say you set up Column K with the header "Organ" and you populate the column with the organ(s) appropriate for each record. Now, you have a block of text that you want inserted in your letter, only for those institutions where "pancreas" is one of the organs listed in that field. Go to Sheet2 of the database and enter the text into cell A1 of that sheet (you can enter up to 32,767 characters in each cell, even though only 1,024 will be displayed in the cell, all will be displayed in the formula bar). Now, go back to Sheet1 and set up a new column in your table with header label "TextA". Move to the cell just below TextA (row2, your first record) and enter the Excel formula:
=IF(ISERROR(SEARCH("pancreas",K2,1)),"",Sheet2!$A$1)
Drag the formula by the lower right cell corner (the autofill handle) to copy the cell down. If you use this field as part of a mail merge now, the text block will only be inserted in the letters prepared for those institutions where the record had pancrease listed in column K.

Dude that is one smart & clever idea - I'll give that a go and report back. Thanks Steve!