Help with holiday card order tracking system

[EBerger]

Hello,

I wasn't sure where to post this considering it involves Outlook, Excel and possibly Access but here it goes:

I work in the marketing department of a large law firm that has about 300 lawyers ordering holiday cards for their clients and prospective clients. The firm agrees on 5 styles of cards.

The way it has been handled this year (and years past) is that an e-mail will go around to the 300 lawyers. The e-mail has a picture of each card and they are numbered "Style01 - Style05" What each professional is supposed to do is to just hit "reply" to this email and type a short message with their card selections and quantities. For example:

Dear Marketing,

Here are my card selections for 2006:

Style01 - 400 cards
Style03 - 185 cards
Style05 - 75 cards

Thanks,

Joe Lawyer

Once all 300 e-mails are returned I go through and manually input each card order into an Excel spreadsheet. Then once I have the totals for each style of card I then place an order with the printing vendor. The manual entering into the spreadsheet from 300 (printed) e-mails is quite time consuming and leaves me stressed that I made a mistake and ordered too many/not enough of a card.

There has to be a better way, right? What I am hoping in the next year is to learn how to build an Outlook form that will keep track of the orders for me as they are inputted. In other words, the attorneys would receive an email with the pictures of the 5 cards and next to the pictures is an input quanty box. Once the quantities are filled in the attorney can hit send and their totals per card are written to an excel sheet (or Access DB). Each order from an attorney would be its own row and each style of card would have its own column. Also it would be nice if the "FROM:" line (the attorneys name) could be carried on to this sheet on the row with the quantities to identify who ordered what.

How much work does this involve? Would it take a major techie to figure this out? I have a year - the 2006 holiday cards are already ordered. What books would be helpful?

Any other suggestions

Thanks for reading!!!

 

[njimack]

Since you intend to take the output from Outlook and enter into Excel, could you not set up a shared workbook that the lawyers directly enter into?

I had a similar task recently, getting approx 50 finance staff to allocate a percentage of their time across 5 clients. I set up a shared workbook with a single sheet which listed their names down column A and the clients across row 1.

A Workbook_Open event would filter the list of staff based on the logon id of the current user. This meant as soon as someone opened the file, they would only see their name, plus the names of the clients. They then enter the time spent on each client and save the file. Since the file is shared, there's no limit (as far as I know) to the number of users who can be using the file at the same time.

 

[EBerger]

Thats not a bad idea. I think I'll look into it.

However, I am still interested in knowing if there is a way to create an Outlook form that has fields that get written to a file. Even a comma seperated value .TXT file would work.