Type, copy, click, paste, type, copy, click...
December 2, 2009 3:56 PM Subscribe
I've been manually sending out emails about test results and then entering them into a database - is there a way to automate so part of this process?
I'm a teaching assistant for a graduate department. Students in various classes come into the resource room in which I work and take a variety of tests that are required to pass their classes. They take the tests, we grade them, enter the results into a spreadsheet, and then send them an e-mail saying if they've passed of failed.
Since it's the end of the semester, I got caught doing about 30 of these today. Copying and pasting the same form letter over and over into an Outlook window after having entered these all into an Excel spreadsheet seemed stupid. I know there's gotta be a way to automate this process at least partially - can you help me with it?
Here's the general gist of it all.
Student takes test from one of 4 classes.
Results (a pass or fail) is entered into an excel spreadsheet. Each class has it's own spreadsheet. Each student needs to take multiple tests, and they can be taken in any order at any time during the semester. If the student fails, they must retake the test. They get an e-mail for every time they take a test, up to 3 times. Students can take multiple tests at once as well.
After the student takes the test, they are graded and entered into the database. The student's name, test type, and whether they passed or not, and the date is recorded. And e-mail is then sent to them with a list of which tests they took, and whether they passed or failed the tests. There's a bit more info on the letter, but that's all standard form stuff.
Which parts of this can be automated? And how do I do it? I know about mail merge, but because of the continual nature of this, I'm having trouble seeing how to make this work. If not, is this just something I'm going to have to do manually?
I have available a PC, Excel, and Microsoft Outlook.
I'm a teaching assistant for a graduate department. Students in various classes come into the resource room in which I work and take a variety of tests that are required to pass their classes. They take the tests, we grade them, enter the results into a spreadsheet, and then send them an e-mail saying if they've passed of failed.
Since it's the end of the semester, I got caught doing about 30 of these today. Copying and pasting the same form letter over and over into an Outlook window after having entered these all into an Excel spreadsheet seemed stupid. I know there's gotta be a way to automate this process at least partially - can you help me with it?
Here's the general gist of it all.
Student takes test from one of 4 classes.
Results (a pass or fail) is entered into an excel spreadsheet. Each class has it's own spreadsheet. Each student needs to take multiple tests, and they can be taken in any order at any time during the semester. If the student fails, they must retake the test. They get an e-mail for every time they take a test, up to 3 times. Students can take multiple tests at once as well.
After the student takes the test, they are graded and entered into the database. The student's name, test type, and whether they passed or not, and the date is recorded. And e-mail is then sent to them with a list of which tests they took, and whether they passed or failed the tests. There's a bit more info on the letter, but that's all standard form stuff.
Which parts of this can be automated? And how do I do it? I know about mail merge, but because of the continual nature of this, I'm having trouble seeing how to make this work. If not, is this just something I'm going to have to do manually?
I have available a PC, Excel, and Microsoft Outlook.
This process is generally called Email Merge. Apparently there is a tool in Word to do it.
posted by Phssthpok at 4:29 PM on December 2, 2009
posted by Phssthpok at 4:29 PM on December 2, 2009
It's probably too late for this period, but I'd probably approach that in reverse next time. Enter them in some database first, preferably one that can be queried over the web.
Then have an automatic script run every few hours, or every day, or on some schedule. It checks the database and mails out any "new" results, based on what it finds and what it knows that it mailed before. (Give that description to a programmer and check back in a few hours.)
This way you only administer the database, and the e-mailing "just happens".
posted by rokusan at 4:30 PM on December 2, 2009
Then have an automatic script run every few hours, or every day, or on some schedule. It checks the database and mails out any "new" results, based on what it finds and what it knows that it mailed before. (Give that description to a programmer and check back in a few hours.)
This way you only administer the database, and the e-mailing "just happens".
posted by rokusan at 4:30 PM on December 2, 2009
Response by poster: I have Office 07, so anything that works in that would be awesome.
What kind of language would that be programmed in, rokusan? I need a winter break project, and if it's something that isn't too crazy, I don't mind getting my hands dirty.
posted by SNWidget at 4:43 PM on December 2, 2009
What kind of language would that be programmed in, rokusan? I need a winter break project, and if it's something that isn't too crazy, I don't mind getting my hands dirty.
posted by SNWidget at 4:43 PM on December 2, 2009
What you describe sounds like a very straight forward mail merge product. You have a Word document - the form letter, and you need to have the fields from the spreadsheet entered automatically. It should be fairly simple to insert the of the various merge fields; name, tests taken, grade and date into the form letter. There are a lot of tutorials available. You can find some pretty good tutorials on YT and elsewhere, or you can use Microsoft's help.
posted by X4ster at 6:11 PM on December 2, 2009
posted by X4ster at 6:11 PM on December 2, 2009
Response by poster: I've used mail merge for doing things like labels, but I was unsure how to make it work with email, especially in that each student can take different tests at different times.
The only times I've ever used mail merge was with a static list of addresses, not something where I'd have to sift through the list for new entries and just mail those. Unless there's a way to do that.
posted by SNWidget at 6:29 PM on December 2, 2009
The only times I've ever used mail merge was with a static list of addresses, not something where I'd have to sift through the list for new entries and just mail those. Unless there's a way to do that.
posted by SNWidget at 6:29 PM on December 2, 2009
A homemade web based programming product will probably be the most "repeatable" for other staff but take longer to implement because you have to build it, while the Office 07 solution will probably be faster to implement but harder to transition to someone else who has to do the same thing should you leave the job. I don't know if that's a consideration for you. So I would try but try poking around in Excel's merge features. Also take a look at Access as I know there are Outlook tie-in features in the new version of that as well that might be easier to work with.
posted by amethysts at 6:30 PM on December 2, 2009
posted by amethysts at 6:30 PM on December 2, 2009
I'd also like to point out that the vast majority of colleges now have some sort of website program where you can post grades... Does the class have a website? Especially through something that blackboard or banner or something like that?
posted by brainmouse at 6:51 PM on December 2, 2009
posted by brainmouse at 6:51 PM on December 2, 2009
If you've got Excel and Outlook, it's a fair bet that Access is lurking about somewhere on your PC as well, and this job would be a good fit for its report generator. The Access report generator is very nice, and I believe Access can also be persuaded to use Excel spreadsheets as data tables or at least easily import from them into its own tables.
posted by flabdablet at 2:59 AM on December 3, 2009
posted by flabdablet at 2:59 AM on December 3, 2009
I'd do a merge as well. Set my columns as something like
FirstName LastName Test1 Date1 Test2 Date2 Test3 Date4 Test4 Date4
For Test1-4, the values you're entering are Pass or Fail.
And then just make 4 different merges from the one sheet, for each test respectively, and sort the columns by the values in testX, so all your passers were @ the top and all your failers were at the bottom, because I'm thinking there might be different text you have to add depending on their status.
And then I'd get bored with that, and I'd make a database with approximately the same values, but checkboxes for each test and a checkbox (probably) for "letter sent". And then I'd make custom queries for passers/failers for each test that pulled name/the 0 or 1 from pass fail, and the 0 or 1 from letter sent, and then displayed accordingly. I'd do an if/then so it would add a field if they failed. Save that query and/or export to excel, do a merge from it, of course saving the merge letter to automatically incorporate name/date/ and at the bottom add the merge field for if they failed, the special text. That way, if they pass, they don't see it.
(and then in the query I could run down and check all the "letter" sent boxes so I didn't merge them again.)
Did that make any sense?
posted by TomMelee at 4:56 AM on December 3, 2009
FirstName LastName Test1 Date1 Test2 Date2 Test3 Date4 Test4 Date4
For Test1-4, the values you're entering are Pass or Fail.
And then just make 4 different merges from the one sheet, for each test respectively, and sort the columns by the values in testX, so all your passers were @ the top and all your failers were at the bottom, because I'm thinking there might be different text you have to add depending on their status.
And then I'd get bored with that, and I'd make a database with approximately the same values, but checkboxes for each test and a checkbox (probably) for "letter sent". And then I'd make custom queries for passers/failers for each test that pulled name/the 0 or 1 from pass fail, and the 0 or 1 from letter sent, and then displayed accordingly. I'd do an if/then so it would add a field if they failed. Save that query and/or export to excel, do a merge from it, of course saving the merge letter to automatically incorporate name/date/ and at the bottom add the merge field for if they failed, the special text. That way, if they pass, they don't see it.
(and then in the query I could run down and check all the "letter" sent boxes so I didn't merge them again.)
Did that make any sense?
posted by TomMelee at 4:56 AM on December 3, 2009
« Older Form of Wallowing Garbage Scow! | FictionFilter: Inside the Kremlin during the Cold... Newer »
This thread is closed to new comments.
posted by amethysts at 4:28 PM on December 2, 2009