Complex coding help needed

[JohnnyBravo]

Hi guys, you've got a great forum here! I could use some help with a mail merge project which involves several steps. This post is going to be long - sorry in advance.

I'm working on a large mail merge project where I need to send out an RFI to various hospitals across the country. Each of these hospitals get 3 things:

1) A cover letter addressed to the appropriate person at the hospital. It's actually an instruction letter telling them what the terms of the RFI are.

2) a floppy disk w/ the all the pertinent documents (password protected) and 3) A binder with all the relevant information.

Each of these hospitals specialize in one of the 3 major types of transplants such as Solid Organ transplant which encompasses things like Heart, Liver, Kidney and so forth. So depending on which hospital we're dealing with, I copy the appropriate word documents off the network drive onto a floppy diskette. I'll get to this part later.

Before i arrived, they were doing it in a totally labour intensive & inefficient manner. They didn't even know how to use mail merge in Word.

Step 1: Using their current instruction letter, I setup a mail merge document. For the mail merge records, the contact info is manually typed in. Although that information is stored in an Access database, I have no idea how to use the two programs in conjuction w/ the mail merge. That's ok. I've got bigger questions/ problems I need to address.

Step 2: If a hospital specializes in Heart, lung & liver for example, I attach 3 separate addendums (MS word docs) that goes into detail for each type of organ transplant. These are addendums located in a specific folder on the network drive. I'll call it Section I.doc; Section II.doc; & Section III.doc for brevity sake. So what goes on the floppy disk is those 3 word docs - password protected along with an excel worksht that is also password protected.

Right now what happens is that after I open each word addendum, I manually type the name of the hospital in the header section. Close header/footer. Then put a password protect so that it cannot be modified. Then I'll create a subfolder under the "Letters Sent" directory and save it so that the original (generic) letter doesn't get overwritten.

How do you get VBA to create a subfolder named as the same name as the hospital name in the header section?

I'm very much of a VBA newbie and know very little. I took a small baby step in trying to get a portion of this automated.

Problem: I don't know how to put a password protect AND save it to network drive and floppy disk. The copy that's saved to the floppy has password protect, but the copy on the hard drive isn't for some reason. How do you make it effective for both?

ActiveDocument.Protect Password:="test1", NoReset:=False, Type:= _
wdAllowOnlyReading, UseIRM:=False, EnforceStyleLock:=False
ActiveDocument.SaveAs FileName:="A:\Section I.doc", FileFormat:= _
wdFormatDocument
ChangeFileOpenDirectory "C:\2005 RFI\Lettters Sent\ABC Hospital"
ActiveDocument.SaveAs FileName:="Section I.doc", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False


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

With Step 2: The ABC Hops. example is easy. However, there are several hospitals that specialize in more than 1 type of transplant say, Heart, Liver & Lung. In those cases, I need to retrieve those 3 word attachements and repeat the steps for all 3.

Isn't there some way that whenever I select the word attachments (In Windows Explorer), that as soon as Word opens them up, that VBA can do the steps which are highlighted in pink text above? Essentially i need VBA to create a new subfolder (under the Letters Sent directory) named with the Hospital name. Then save all the word addendums to that directory.

Furthmore, can VBA then move on the 2nd record (in mail merge) so that the next time I manually select the corresponding Word addendums, it will insert the correct name of the hospital? I don't need 50 "Section I.doc" addendums all saying ABC Hopsital.

I know there are alot of VBA gurus here, if you have any better ideas or suggestions on this, I am all ears.

If any one of you guys can help out with this, I sure would appreciate it. It would really mean a lot to me - thanks.

[mdmackillop]

Hi Johnny,
Welcome to VBAX
Here's a copy of a mail merge process I'm working on, which might be a way forward for you. Basically a selection of MergeDocuments are saved in a folder, and a list of Names, Addresses, Project details are stored in a table in a Word document. There is a form which lets you create a new document to any recipient. The created document is given a unique number and saved into a subfolder depending upon its file name. With a little work, this could be adjusted to a network situation.
I've not tried to address your Floppy usage at the moment.
To try this out, unzip the file and open DoMerge.doc.

[JohnnyBravo]

Thank you so much mdmadkillop, I will take a look at it in a few min. I'm sure I'll have some questions later.

BTW, that moose stew recipe sure sounds good!

[fumei]

I am a little confused. You are talking mailmerge, yet also talking making some selection of files using Windows Explorer. These are kind of mutually exclusive.

As for making subfolder. You can use FileSystemObject. You MUST have a reference to Microsoft Scripting Runtime to use it. I tend to use early binding (for those you may wish to comment). I have my reasons.

Sub MakeSubFolder()
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder

' if you in fact DO have the text you
' want in the Header. You do not state
' which header - but I will assume you
' are taking it from Primary. Also, it is
' being picked from Section 1.
' If you need help with this - ask.
' strip off the last character, if required. This
' takes the whole range, and so has the
' paragraph mark. Do I stripped it off.
Dim strHospital As String
strHospital = Left(ActiveDocument.Sections(1). _
Headers(wdHeaderFooterPrimary).Range.Text, _
Len(ActiveDocument.Sections(1). _
Headers(wdHeaderFooterPrimary).Range.Text) - 1)


' in any case you have the String you want
' to use for a subfolder
' make the FileSystemObject..uh, object
Set fso = CreateObject("Scripting.FileSystemObject")

' this is the top folder you want to make
' a sub folder FROM
Set fld = fso.GetFolder("C:\2005 RFI\Lettters Sent")

' uh...by the way...letters has TWO t's.
' then simply add another folder to it
' using the string variable
fld.SubFolders.Add strHospital

' and destroy the objects for clean up
Set fld = Nothing
Set fso = Nothing
End Sub

Assuming the header range text = ABC Hospital

then C:\2005 RFI\Lettters Sent\ABC Hospital now exists as a folder. Now when you have files that are to be saved, simply write them there directly. No need to ChangeOpenDirectory.

If you are still within scope of the subroutine - that is, you are still running code and the string variable strHospital exists - you could use the FileSystemObject...uh, object...properties, like:

ActiveDocument.SaveAs FileName:=fld.Path & _
strHospital & Application.PathSeparator & _
"Section1.doc"

Good project. You may be a newbie, but by the time you get this thing slick and finished you WILL know a lot more.

[JohnnyBravo]

Good project. You may be a newbie, but by the time you get this thing slick and finished you WILL know a lot more.

Oh god i'm way in way over my head now! :bug:

Listen, I'm not even a newbie - I'm a pre-newbie!

I'm at work right now and i'll try out the suggestions given thus far as soon as I get a chance to.

I know my posting was long - I hate to make long posts like that but there was no other way around it. I'll post back and try to clarify some of the confusion?

[fumei]

Don't worry about it. We would rather have good (long) posts that clearly state the situation, than short ones that do not give enough information.

[mdmackillop]

Hi Johnny,
Re my enclosure, this is intended as as "standard" we use for each contract of a certain type. A set of standard merge documents is stored in the mergeletters folder. The data for the merge is stored in 1234 Data.doc. The other folders are used to store the created mailmerge documents according to the MySettings function, automatically numbered. Opening DoMerge opens a UserForm which allows selection of User and MergeDocument.
The 1234 type numbering is a bit overcomplicated, and I'm looking to simplify this further for my own use.
As a confessed newbie, I would not expect you to undestand all the coding, but if the principle is of interest, I'm happy to modify it to suit your purpose.
Regards
MD