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. :thumb

[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.
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

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. http://vbaexpress.com/forum/images/smilies/102.gif

.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:


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

[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:

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

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





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




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.

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

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

[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.
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."

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?

[TonyJollans]

Well, there is no way the merge can possibly process data that is input afterwards.

I'll see if I can knock up a working model, subject to the specialisms being in the merge data, of course.

[fumei]

I posted the code for this, using bookmarks. Go back and look at it again. The paragraphs to be deleted have to be bookmarked as well.

[JohnnyBravo]

Well, there is no way the merge can possibly process data that is input afterwards.

I'll see if I can knock up a working model, subject to the specialisms being in the merge data, of course.



I posted the code for this, using bookmarks. Go back and look at it again. The paragraphs to be deleted have to be bookmarked as well.

Ok guys, we are completely miscommunicating here. First Gerry, yes, I saw the code for deleting paragraphs and yes it works (sort of) - thank you for that. But I posted back to this thread because,

#1) I wanted to clarify if Tony was talking about the "IF" conditional mail merge feature and if so, how to use that for my purposes.

#2) Your code works for only one keyword. If you see my posting from earlier today, there is not just one keyword involved here - but often several of them. So again - referring to my posting from today, you'll see that there can be a combination of several different keywords and based on that, some paragraphs remain and some paragraphs get removed.

Furthermore, upon examination I think your code is effectively for 1 single document. Does it really work for a merged document of consisting of several records??

Tony, I realize what you are saying. I'm not looking to merge data that in the manner you think. What I do is AFTER the mail merge, I enter in the transplant type (we'll call it the keyword) for each merged record at the end of paragraph #1. Then manually BY HAND, I delete certain paragraphs and sentences based on those keywords. Notice I said keywords because often times it's a combination of the 2 categories which I explained in my posting today.

I really don't know how else to explain my project here. We are going around in circles?

[fumei]

We are going around in circles.

My code was for the purposes of getting things started, sooooooo

1. Of course it is for one document. The document after the mail merge IS one document.

2. Adding several keywords is simply expanding the code. You can effectively making any combinations you want.

JohnnyBravo, this is a question of LOGIC. Get your logic right and then the coding will eventually work out.

The LOGIC is:

an input parameter determines if specific paragraphs are deleted, oir not.

The code does exactly that. Now if you have multiple parameters...that is OK. You just build the logic structure to deal with it. However, the basic logic remains the same. A parameter deternines if specific paragraphs are deleted. For the logic to work the specific paragraphs have to be specified. How could it be otherwise? Therefore the specific paragraphs need to be bookmarked.

Tony is talking about the IF within the merge, and YOU are talking about logic (an IF) after the merge. As Tony posted, these are different.

[geekgirlau]

The IF merge code will ONLY work if the transplant type is part of your data source. I would question WHY you would want to manually enter a value after performing the merge, however there may be some reason for this that is not apparent to the uninitiated!

[TonyJollans]

What I do is AFTER the mail merge, I enter in the transplant type (we'll call it the keyword) for each merged record at the end of paragraph #1. Then manually BY HAND, I delete certain paragraphs and sentences based on those keywords. Notice I said keywords because often times it's a combination of the 2 categories which I explained in my posting today.

For this process, after the mail merge, you will need some code - I don't know if it's easier to add or remove paragraphs - I think, probably, adding is easier if you set up some Autotext entries.

But I'm interested. What criteria do you use for adding the transplant type? Even if it has to be a manual process, could it not be (manually) added to the data source before the merge? (And what format is the data source?)

[JohnnyBravo]

Well I apologize for causing such mass confusion :) - I did not mean to.

The data source that contains all the addressee's contact information is in an Access database. Since it does not contain the transplant type information, I have been been manually typing that in after the mail merge. Long and totally inefficient I know.

This is a temp job so I have not been given all the tools I need to do this effectively. I have been told that the there is a database that does contain the transplanty type for the respective hospitals we deal with but that is in yet another Access database which I am not privy to. If that was the case, this thread would have been solved in a heartbeat considering the expertise among you.

Sad but true - I think will have to let this thread die unless I can convince my supervisor to give me access to that database. I really appreciate everyone coming to this thread and helping out. You guys are nice to donate your time & efforts to all the people that come here seeking free help.

[TonyJollans]

Do you have update authority to the Access database? No it doesn't matter - do you have Access? If so, create a new database and add a table of hospitals with transplant types, link to all the tables in the other database (you must have read access to it or you couldn't run the merge), create a query joining the two together and base your mail merge on that query. I'm going out again shortly but will try and post a sample in the morning.