mail merge for each individual excel doc in a folder

[salventuro]

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the fields to be merged from the data in excel spreadsheets.

Each of the spreadsheets are identical in layout, all data is in Sheet1, in each Excel workbook.

What I have done is, inserted six text fields in the Word document, that are mapped to Excel.


I can perform the mail merge manually. The issue is, I have over one thousand excel doc's to perform the merge on. Can a macro be used to loop through a folder, merge and propogate to a new Word doc for each excel doc?

This will create over one thousand word doc's, which will then be emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated Word doc be saved as 123abc.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!

[macropod]

Hi salventuro,

You've already asked this question in the microsoft.public.word.mailmerge.fields newsgroup - and been given some good information. You should at least have the courtesy to acknowledge that and, if the replies you got there don't meet your needs, you should say so - in the microsoft.public.word.mailmerge.fields newsgroup - and here.

[salventuro]

Hi macropod,

Yes, I have posted the same question on wordbanter, Great site by the way. Point taken, I should have mentioned that.

I had difficulties posting a follow up there, as you saw I posted once in error and then provided more info, when Doug mentioned I should have provided more details. (Thanks again Doug).

I tried to post to the newsgroup directly, but, got an error, "posting not allowed or permited". I have been subscribing to newsgroups for over five years and never got that error message.

I have been searching many sites, trying to get a solution to this. In my searches I came accross this site, it looked very good, so I decided to sign up and post here also.

I also noticed you are every where on the web, providing a huge amout of assistance to others. When I read your response here, I realised I looked like a bit of jerk for not mentioning it. When I posted here I didn't think anyone would be interested in my other post. But now I can see how that can be viewed as a no-no, to all the experts who provide information and assistance, for no fee and on their time.

I have a website cbtlessons (I can't post links yet) just add www and dot com), where I provide video instructions for newer PC users (and math for my kids and their friends). I pay for it out of my pocket and it's free to anyone.

Unlike these boards, where there are much more knowledge people than me, I don't have interaction with anyone who visits my web page.

So I dropped the ball here, I should have had more netiquette, and taken into account the community. My apology for this.

Thanks for the info on the the other fourm by the way. I am studying up on it, but am no closer to a solution.

Anyway, I hope everyone has a great day.
Gotta go now, my kids are fighting again, where's my ref whistle!

Sal.

[salventuro]

Doug, macropod.
Thanks for your willingness to assist. Unfortunately I ran out of time on this project.
I have decided to email the Excel workbook and the Word doc to the reps, they will merge the two, for the final list of forms to be completed by their clients.
I created the mail merge Word doc and added this code,
Sub AutoOpen()
With ActiveDocument
.MailMerge.Destination = wdSendtoNewDocument
.MailMerge.Execute
End With
End Sub
This was probably written by one of you two. LOL (Doug, you are quite the Word celebrity, authority, guru, maestro…). I feel privileged that you responded to my question.
I created an instructional video that I will embed in a PDF doc, showing how to save the attachments, run the mail merge and save the final Word document.
I will pursue my original question and I will find a solution. When I do, I will post it back in this thread.
My thanks once more.
Sal.