Trying to automate a hellish email process. Any ideas?

[ynotravid]

I'm trying to automate a terrible process that I inherited and I'm not sure how best to approach it.

I have to select about 12 to 24 tickets out of about 200, send surveys to the "customers" (these are actually my fellow employees), and then input the results into some spreadsheets so my bosses can have something to meet about.

The current process:
1. Run a report to get a list of all the tickets for the month from an Access program that I'm not allowed to edit.
2. Choose about 1 in 10 to send a customer satisfaction survey to.
3. Manually create email surveys to send to customers.
4. Wait for them to come back so I can manually enter the results into various Excel spreadsheets.

Limitations/Parameters:
1. I'm not allowed to edit the Access program.
2. I am in a highly controlled environment where everybody has Windows XP /w Office2003 installed.
3. I cannot use a website as this would violate a ton of IP rules and procedures.

I did take a stab at this but I ran into something that makes me think I'm going about this the wrong way...

I will elaborate in the next post.

 

[ynotravid]

I started by adding some VBA to one of the spreadsheets. Once I did this I noticed a problem:
Whatever I send as an email is not going to come back to me in a predictable format, therefore making it almost impossible to automate the retrieval of information. I mean, some people don't even bother filling out the answers, they just reply back saying something like "All 5's, great job!"

Creating an html email and using a form would solve this problem, except that whenever they hit Reply they are in edit mode which makes using forms a no go.

OK, I'm rambling. Bottom line, this thing is driving me crazy. If anybody has ideas on how I could make sense of this, I could sure use the help.

Thanks ahead of time!

 

[mundane]

I was going to suggest having them access an internal webserver for submitting an HTML form, but apparently that's unacceptable. Maybe you could send each of them a simple Excel worksheet where they are required to fill in the survey results. You should be able to use VBA to parse through their responses.

So, you should be able to automate (through VBA):
1) Grabbing the tickets
2) Selecting random subset
3) Emailing the ticket submitters an excel worksheet to complete, have it send you a list of those selected for reference.

Once the results are returned to you, another program would:
1) Parse through the results
2) Stuff into your summary excel spreadsheets.

It's really too bad you can't use a webserver for this.

 

[ynotravid]

"It's really too bad you can't use a webserver for this."

I know, it sucks. I'm big on web apps nowadays, and this is would be a perfect situation, but unfortunately it takes like 30-50K to get anything web related up. Rediculous, but that's how goes around here.

As far as sending them a spreadsheet goes, again a good idea but if these lazy bastardos around here have to open up a spreadsheet to fill it out, they won't do it. Even sending them to a link would have to be surrounded by promises that it's the easiest thing they have ever done.

This puzzle might be unsolvable.

Anyhow, thanks for the suggestions Diego!