Create a new word doc in excel and merge data

[Kgbpizza]

This one might be a bit complicated:

I want the ability to create a new word document from inside excel and merge in certain data from the worksheet to specified fields in the word document (address, file number, fax number etc.). Ideally I would like to be able to select one "Contact" (maybe with a check box of some sort) and then select a custom toolbar button to create a letter with that Contact's information already in the fields.

Bonus points: Instead of a new word document, open a template from a template folder then save with a different file name and the above merged data in the specified fields.

Any help would be appreciated. I am a novice with excel and VBA but am slowly figuring it out.

[jaslake]

Hi Kgbpizza
I've developed this same process for our Neighborhood Association. The problem is that I've created the process for Excel 2007 and it would need to be modified for Excel 2003 (which you're profile indicates you use).

The process allows me to create "Dues Billing Statements" for specific residents that I choose. I use a multi-select ListBox on a UserForm for this selection. The main data file is filtered to a temporary worksheet based on the entries in the ListBox.

The filtering procedure then calls this code

Please Login or Register  to view this content.

You'll need to create your "Mail Merge" document in Word as a first step. The above code assumes the "Mail Merge" document resides in the same folder as the workbook running the code.

The procedure closes the "template" without saving and leaves open a new Word file with the merged information included.

If the procedures I've written are of interest to you let me know and we'll figure out a way to proceed.

John

[Kgbpizza]

I am actually using Excel 2007. Didn't realize you could select that when registering.

this does look like what I want it to do. The template word docs would be in the same folder as the spreadsheet (although I use the same "master spreadsheet" for multiple clients, I will be putting the templates into the client folder as well. Is it possible to select which cells go where in the word doc?

[jaslake]

Hi Kgbpizza

Regarding this question

Is it possible to select which cells go where in the word doc?

That's what this addresses

You'll need to create your "Mail Merge" document in Word as a first step.

Create the "Mail Merge" document in Word. If you're familiar with that process, you know that it will ask you to select recipients. Browse for your Excel data base and select your data sheet. Insert the merge fields into the "Mail Merge" document at the place you want them.

I'll be happy to help you with this process but, if you're not familiar with it, I'd prefer you played with it first to become familiar. That'll make our communication much easier.

If you wish me to build the entire process for you (I'd prefer you were involved for the learning experience) then:

• Create your Word document and indicate what goes where (that is, what Excel fields go where in the Word document). Post this to the Forum. For example
  • Insert <First Name><Last Name>
  • Insert <Email Address>
  • Etc <....>
• Prepare a dummy file of your Excel file (make certain ALL column headings are the same as your actual Excel file. Post this file to the Forum.

Get ready for a trip. This could be fun (or not).

John

[Kgbpizza]

Thankis John. I definitely want to be involved for learning purposes as I intend to duplicate the procedure for multiple letter templates depending on the purpose of the letter. For training purposes I would like to start with a basic fax cover letter. Once I figure out how to do it I should be able to make endless more templates.

As I said earlier, I will be keeping the templates in the same folder as the spreadsheet--I figured this is easier than keeping the templates in another folder.

If it makes it easier for you to visualize I attached a dummy workbook with my contact worksheet in it. My fax cover letter template is made-- I want to pull honorific, first name, last name and fax number from the contact sheet into the fields.

[jaslake]

Hi Kgbpizza

Please attach a copy of

fax cover letter template

so I can see what you have and to use for testing.

John

[Kgbpizza]

This is the template I will be using. Not in template file though since those can't be uploaded.

[jaslake]

Hi Kgbpizza

Attached are some files and code for you to play with.
Let me know of issues.

John

[Kgbpizza]

I have been playing with the code all day.

it works great in the files you sent me... exactly what I wanted it to do.

I have been trying to move them into my master spreadsheet, and alter it a bit (I made a new UserForm that will incorporate more buttons-end goal is to have each button link to a different template and merge different data).

I run into a problem when it tries to run PrintSelected Macro. I get an error message that says : "user-defined type not defined". I'm very confused.

I have attached my tester Master spreadsheet so you can see what I did and hopefully you can help me figure out where I went wrong.

[jaslake]

Hi Kgbpizza

I'm unable to duplicate the problem because the file you posted is digitally signed with an invalid digital signature (or so says Excel Security) and Macros are disabled. I'll be happy to troubleshoot this with you if you post a file I can open.

John

[Kgbpizza]

I enabled all macros and deleted my digital signature from the macros i built. Hope that works.

[jaslake]

Hi Kgbpizza

Can't run macros on this file either. See attached for message I get.

John

[Kgbpizza]

I think you have to enable all macros on your system to do it. If you don't want to do that, I could probably send you a non-macro copy and then post the code for the macros which would you could then paste in and make new macros on your system.--that might work.

[Kgbpizza]

Here is a macro-free workbook. Let me know if it opens and I can try to post code.

[jaslake]

Hi Kgbpizza

Nah, I don't want to do this

enable all macros on your system

Not even something I'll think about.

To remove digital signatures see http://office.microsoft.com/en-us/ex...px?redir=0#BM6

I believe that will do it.

John

[Kgbpizza]

I had already done that. I think I figured it out now though. I had made a self-certified certificate. I removed it permanently. I will attach both a macro file and a non-macro file.

[jaslake]

Hi Kgbpizza

Set a Reference to Microsoft Word 12.0 Object Library. Open Visual Basic (Developer->Visual Basic or Alt F11). In Visual Basic, select Tools->References. Check the Box next to Microsoft Word 12.0 Object Library. Click OK.

This will cure this error

"user-defined type not defined"

However, I don't see where you're opening Userform2.

John

[Kgbpizza]

Thanks. that solved the problem. I think it shoudl work now. I will be playing with today and trying to find any bugs.

[Kgbpizza]

I pasted the formula all the way down the "P" column so it auto does Lastname, Firstname when I put in a contact but stays blank if the contact is empty. Unfortunately the userform will include all the blank lines in List1. Is there a line of code I can enter to have the blank cells in column "P" ignored?

[jaslake]

Hi Kgbpizza

Change your UserForm Initialize Event Code to this

Please Login or Register  to view this content.

The blue lines are the lines of code I changed. I'll be traveling this weekend but I'll still be online. Response time will be lengthened.

Let me know of issues.

John

[Kgbpizza]

hi,

you have been an amazing help thus far. I hate to keep bugging you.

I had the merging working perfectly before the weekend. m I haven't changed anything in the macros, but now i get an error message when I run the macro to merge.

Run time error '5631':

Word could not merge the main document with the data source b/c the data records were empty or no data records matched your query options.


Any idea what happened?

[jaslake]

Hi Kgbpizza

"Bugging" isn't an issue. If I can help, I will.

I have no idea what happened; something changed if the merge worked consistently before. If you can't resolve the issue, I'd suggest you post ALL your files (Excel and Word) and I'll look at them to see if I can determine where the procedure(s) fails.

John

[Kgbpizza]

I've been plugging away at all day trying to see what happened. I am stumped.

[jaslake]

Hi Kgbpizza

I'll look at this tomorrow.

John

[jaslake]

Hi Kgbpizza


The error you described is caused by this code

Please Login or Register  to view this content.

You will have the same issue with all your Print/Fax routines. If you have issues or questions, let me know.

John