Mail Merge with conditonal parameters

[JohnnyBravo]

Ok, I've decided that my first posting was wayyy long and I figure most people didn't have time to read it. So I've taken one piece of it and decided to make a separate post.

I don't even know if I'm using the term "with conditonal parameters" correctly or not, but anyways here's what I've got.

We're sending out an RFI to several hospitals across the country. Some of these hosp. specialize in more than one type of transplant, for example: Heart, Liver & Lung. And some others only specialize in Bone Marrow.

Before i got here, someone created a general letter with paragraphs that relate to all 3 major types (categories) of transplants:

A) solid organ (emcompasses things like Heart; Liver; Lung)
b) bone marrow (no sub-items for this cat.)
c) stem cell (no sub-items for this cat.)

In the mail merge form letter, I've inserted a field for the type of transplant which gets inserted into the first paragraph.

So what I have been doing is (after the mail merge) manually examining each merged document, looking at the first paragraph and deleting specific portions of the letter that do not relate to that hospital. That's time consuming and inefficient - I've got 80+ RFIs to send out.

Is there some way that VBA can look for a certain word in the beginning paragraph (or in a specific bookmark) and then based on the key word(s), delete specific portions of the letter?

BTW, I'm very impressed by the level of knowledge here. So many good pple who know their stuff - I'm really glad I found this site.

[fumei]

Yes. By using bookmarks.

Bookmarks can be nested - that is, a section of text that is bookmarked, may contain a portion within it that is also bookmarked. Howeverjust to make it simple, I am attaching a ZIP file with a little demo.

It has a few paragraphs in it. "Blah blah blah....." The FIRST paragraph also has the text "ABC Hospital" in it. There is also a paragraph, a different paragraph, that is bookmarked. The bookmark name is "OuttaHere". In the paragraph is some text "Take this paragraph out", but the text itself is not really relevant.

There is an ActiveX command button in the document. If you have macro security set to high. this will not work. You may get a warning regarding it, as ActiveX controls set off Word security warnings.

You can run the code by either going into the VBE and running the routine UseBookmarks; or finding UseBookmarks in the list of available macros - Tools > Macro > Macros (or press Alt-F8).

Or you can click the button. In any case, the routine UseBookmarks will fire, and the bookmarked paragraph will be deleted. The code is in the attached file, but here it is.
[vba]Sub UseBookmarks()
Dim oRange As Word.Range
Set oRange = ActiveDocument.Range.Paragraphs(1).Range
With oRange.Find
.Text = "ABC Hospital"
.Execute
If .Found = True Then
ActiveDocument.Bookmarks("OuttaHere").Range.Delete
End If
End With
Set oRange = Nothing
End Sub

Private Sub cmdDeleteBM_Click()
Call UseBookmarks
End Sub[/vba]

What this does is create a Range object of Paragraph(1), the first paragraph. it searches the first paragraph for the text "ABC Hospital". If it finds it, it deletes the range of the bookmark "OuttaHere". Note that you must delete the Range of the bookmark. If you just delete the bookmark...then just the bookmark is deleted, not the text.

So there you go. VBA looking for a specific word(s), and based on that word (being found, or not), deleteing a specific portion of the document.

To take a look, download the attached file, and unzip it. Open it and run the code, as per the mention above.

[JohnnyBravo]

Fumei, thanks for that. But instead of using the button, how do you make that code execute auotmatically after the mail merge process?

Secondly, in your code above: .Text = "ABC Hospital"

I changed mine to Heart. However, as i explained in my OP, there is more than one type of organ for Category 1.

How do you specify multiple words - like liver, and/ or heart?
Also, does capitalization matter?

I tried these two and neither of them work.

.text = "heart", "liver"
.text "heart"; "liver"

[gmaxey]

Sure.

You can look for the text in the a numbered paragraph or named bookmark and then based on it being found you can delete whole numbered paragraphs or any defined text range (e.g., a bookmark)

Here is just some quick coding to get you started:

[VBA]
Sub Test()
Dim myRng As Range
Set myRng = ActiveDocument.Paragraphs(1).Range
' or Set myRng = ActiveDocument.Bookmarks("NamedBookmark").Range
With myRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "bone marrow"
If .Execute Then
ActiveDocument.Paragraphs(5).Range.Delete
' or ActiveDocument.Bookmarks("DeleteThisText").Range.Delete
End If
End With
Set myRng = Nothing
End Sub[/VBA]

[JohnnyBravo]

Greg, please see my follow up questions to Fumei above. Thanks.

[gmaxey]

First fumei, I am sorry for basically duplicating your post. I didn't see it until after I hit the send button.

Johnny I would have to study a bit on running the macro automatically following the merge.

As for a array of words then you could use an array in the macro:

[VBA]Sub Test()
Dim vWords As Variant
Dim i As Long
Dim findString As String
Dim myRng As Range
Set myRng = ActiveDocument.Paragraphs(1).Range
' or Set myRng = ActiveDocument.Bookmarks("NamedBookmark").Range
findString = InputBox("Type the words to find separated by |")
vWords = Split(findString, "|")
For i = 0 To UBound(vWords)
With myRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = vWords(i)
If .Execute Then
'ActiveDocument.Paragraphs(5).Range.Delete
On Error Resume Next
ActiveDocument.Bookmarks("DeleteThisText").Range.Delete
On Error GoTo 0
End If
End With
Next i
Set myRng = Nothing
End Sub[/VBA]

So when the Inputbox pops up type in heart|liver.

Note the | is arbitary, you could specific some other separator.

In this basic code the Caps doesn't matter

[gmaxey]

I did fing this in Google



[VBA]

Sub MergeMania()


' Do some macro commands....
'
'


With ActiveDocument.MailMerge
.DataSource.FirstRecord = 1
.DataSource.LastRecord = .DataSource.LastRecord
.Destination = wdSendToNewDocument
.Execute
End With


'Do some more macro commands...
'
'
End Sub
[/VBA]



Mail merge is not my cup of tea so I am just guessing here. I suppose the do some more macro commands would be where you put the earlier code. Your document is now going to be made up of lots of sections. A section for every separate letter. So you will have to come up with something like:



For j = 1 to ActiveDocument.Sections.Count

Set myRng = ActiveDocument.Sections(j).Paragraphs(1).Range

blah, blah

Next J

Again, this is just stab. Sorry.

[JohnnyBravo]

Greg, i just copied and pasted your example and changed the appropriate text. It's not working. When I try to run it, i get an error that pops up in the VBA editor.

"The requested member of the collection does not exist."

[fumei]

You certainly would get that if the bookmark to be deleted....uh....does not exist. Are you sure that you made the bookmark?

[JohnnyBravo]

Yes, i copied and pasted everything over; changed his bookmark name to the one in my document. I was a bit tired last night when I tried it - so maybe I missed something. I will try it again here at the office today.

The only thing i did not copy was the line re: input box. I just want the code to run after the mail merge. Does it matter that I did not copy that specific line?

[JohnnyBravo]

I'm really confused here. For my situation, which code should I follow? - Fumei's or Gmaxey's? Can anyone explain what the differences are between those two codings?

OK, nevermind, can someone tell me what's wrong with this coding? The merge part runs fine, but it won't go beyond that. I get an syntax error message in VBA editor at the highlighted line.

--------------------------------

[VBA]Sub mail_merge()
'
' mail_merge Macro
' Macro recorded 10/12/2005 by John
'
With ActiveDocument.MailMerge
.DataSource.FirstRecord = 1
.DataSource.LastRecord = .DataSource.LastRecord
.Destination = wdSendToNewDocument
.Execute
End With

Dim vWords As Variant
Dim i As Long
Dim findString As String
Dim myRng As Range
Set myRng = ActiveDocument.Bookmarks.("Transplant_Type").Range
' or Set myRng = ActiveDocument.Bookmarks("NamedBookmark").Range
findString = InputBox("Type the words to find separated by |")
vWords = Split(findString, "heart|liver|lung|kidney")
For i = 0 To UBound(vWords)
With myRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = vWords(i)
If .Execute Then
'ActiveDocument.Paragraphs(5).Range.Delete
On Error Resume Next
ActiveDocument.Bookmarks("spk_pancrease_only").Range.Delete
ActiveDocument.Bookmarks("bmt").Range.Delete
On Error GoTo 0
End If
End With
Next i
Set myRng = Nothing
End Sub
Sub categories_II_III()
Dim oRange As Word.Range
Set oRange = ActiveDocument.Range.Paragraphs(1).Range
With oRange.Find
findString = "bone marrow;BMT;bmt;stem cell"
vWords = Split(findString, ";")
.Execute
If .Found = True Then
ActiveDocument.Bookmarks("solidorgan").Range.Delete
End If
End With
Set oRange = Nothing
End Sub
Sub pancreas()
Dim oRange As Word.Range
Set oRange = ActiveDocument.Range.Paragraphs(1).Range
With oRange.Find
findString = "pancreas;Pancreas;spk;SPK;PAK;PTA"
vWords = Split(findString, ";")
.Execute
If .Found = True Then
ActiveDocument.Bookmarks("spk_pancreas_only").Range.InsertBefore
oRange.Typetext Text:="For those programs applying for " & _
"pancreas transplant, access to both the simultaneous pancreas " & _
"kidney (SPK) and pancreas (PAK/PTA) UNOS RFI " & _
"must be granted to the BQCT."
End If
End With
Set oRange = Nothing
End Sub

[/VBA]

[gmaxey]

I can't really run your code as I don't have the data sourece, etc.

I also don't see a higlighted ling. If this is the highlighted line:
Set myRng = ActiveDocument.Bookmarks.("Transplant_Type").Range
You could start by removing the period between Bookmarks.(

[fumei]

Ah, I see why you asked about using the underscore character. My lord...on my screen your post is MORE THAN two screen widths!

Here is that awful line properly underscored.
[vba]oRange.Typetext Text:="For those programs applying for" & _
"pancreas transplant, access to both the simultaneous " & _
"pancreas kidney (SPK) and pancreas (PAK/PTA) UNOS RFI " & _
"must be granted to the BQCT." [/vba]

Ack, the code is so hard to read that I am going to have to pull it out and look at it. Yikes.

The underscore is especially helpful in these cases of long strings. Use it in conjunction with the ampersand character (&). For each chunk of text make an ending quote, then a space, the ampersand, then a space, and then the underscore.

You MUST have each chunk of text (string) within quotes. Otherwise you get a parsing error.

Oh and if you have Intellisense turned on (which you do...don't you), then here is an oddity.

"blah blah"& _ (no space before &) will turn into "blah blah" & _ (with a space) automatically by Intellisense. It will NOT however make a space before the underscore. So "blah blah" &_ will NOT turn into "blah blah" & _ by Intellisense.

Why? Because back in the old days....ah, the old days...it was very common to use the underscore character in the names of things.

Sub Make_It_So()

Actually you still see this, but perhaps not as often. So Intellisense does not assume (good dog) that all underscores should have a space before them...which is true. But all ampersands (that are not being used as actual text that is) do.

[fumei]

Actually JohnnyBravo...could you go back and edit your post? Put the underscore into the post. You (and only you) can edit your previous posts. It would be MUCH appreciated, and it would give you some practice. Everybody wins!

[TonyJollans]

I've sort of scanned this thread so I could have missed something vital, but ..

Are you saying you want some text in the result of the merge subject to some of the data you are merging?

So if you merge "Heart" somewhere you want, say, paragraphs 1 and 3
And if you merge "Liver" you want, instead, paragraphs 1 and 2

If I am right, you can do it all with the merge - indeed that's what merge is all about - without any code. What version of Word are you using?

Or is it more complex? Or am I completely off the wall?

[fumei]

Tony, you could never be off the wall.....OK, maybe you could, but how would we tell?

[JohnnyBravo]

Actually JohnnyBravo...could you go back and edit your post? Put the underscore into the post. You (and only you) can edit your previous posts. It would be MUCH appreciated, and it would give you some practice. Everybody wins!

I would if i could. Perhpas i'm missing something here but I no longer see an 'Edit' option under my posts from yesterday. Is the forum scripted so that the editing a post is disabled after so many hours from the original posting (date/time)? I will keep your request in mind for future reference Fumei.

I've had a lot of coding given thus here and for a pre-newbie like me - it's a bit overwhelming especially when you don't even know what things like "Dim" and "Array" means or even when and how you're supposed to use them.

The mail merge project needs to move on - unfortunately i cannot spend any more time on this (trying to give myself a cram session in VBA). I will have to do best I can and will revisit this again when I have more time and make the code work. I do appreicate all the effort and coding you guys have provided - if i need to revisit this again I will post back. Thanks.

[TonyJollans]

Gerry, LOL - I like to keep a bit of mystery! Seems like I'm going to have one here as well.

[fumei]

....hmmmmm, uh, seems that way.

Tony, you like to keep a bit of mystery? I am SHOCKED and COMPLETELY SURPRISED. I don't know why I keep making these ridiculous leaps of intuition.

Looked at your avatar image lately........??? Hmmmm?






Oh crap.....I just looked at mine. It was one I simply grabbed the day I set my profile up - the same day I took the photo. It isn't even my dog...gonna actually have to think about something now. Darn you Jollans!

[JohnnyBravo]

Are you saying you want some text in the result of the merge subject to some of the data you are merging?

So if you merge "Heart" somewhere you want, say, paragraphs 1 and 3
And if you merge "Liver" you want, instead, paragraphs 1 and 2

If I am right, you can do it all with the merge - indeed that's what merge is all about - without any code. What version of Word are you using?

Tony, are you referring to the "IF" statement functionality in mail merge? At first I didn't know what you were talking about - I had to read your posting a couple of times over. I've never used that particular feature in mail merge but i'm not sure it would work for my situation. The problem is that the organ type is typed in manually at the end of paragraph #1 - after the mail merge.

Basically there are 3 categories:
1) Solids: Heart; Liver; Lung; Kidney; Pancreas *
2) BMT: Autologous; Allogeneic or both.
3) Stem Cell. No subitems in this category.

A hospital may specialize in just one or a combination of all 3 categories. For example: "Heart, Liver; Autologous/Allogeneic; Stem Cell."

So depending on which words are in this range (in purple), only certain paragraphs get deleted. * If one of the transplant types is: Pancreas, a particular sentence gets added to the end of paragraph #5.

Can the "IF" function in the mail merge do all that?