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.