I have made a macro that takes a table we use to track internal and external tasks for my company and our client and consolidates data into a daily report. We track 12 data fields, but the customer only tracks6. Right now the data is copied to a new spreadsheet in the existing workbook for our daily records with the 6 required fields and also saved to a new workbook with only the customer owned or shared tasks the name of the owners and grouped into two sections of either open or closed items. The table formating is removed.

The macro asks for the name of the project and creates a title on the spreadsheet with project name and date and the file is also saved by the same name and today's date. I dont know much of anything about macros or VBA, so Im sure its not efficient it was painful to create and took probably close to 100 hours (sadly), but the whole thing works just fine finally.

Unfortunately some of our management doesnt like excel spreadsheets and requires a daily update via email in MS word with a specific bullet point formatting.

This is where I completely run into a brick wall. The required format in MS word is simply the project name and below it two sections of open and closed items in bullet points. Additionally, the owner names are required to be in red.

As an example it might look like this

Project One 15Feb11
Open Items
  • Item 1 description - ABC Co. John Smith
  • Item 2 description - ABC Co. Jane Smith
Closed Items
  • Item 1 description - ABC Co. Jim Brown
  • Item 2 description - ABC Co. Amy White

I have researched about as much as I could and I just cannot figure out a way to even approach this. Even if I could figure out how to cleanly export as space delimited, Id still need to figure out how to get it into word, organize it in the bulleted format, color only two of the exported fields red, and save the word file. I am at an utter and total loss at this point.

Is this even possible? I can upload a copy of an example workbook if it would help. My macro is too long to paste in the forum.