Automatic emailing Excel content via Outlook (for Student grading)

[bhchen]

Dear all:

I'm a college teacher and have been using Excel to post students' various
quiz/exam grades on the web. However, starting from this semester, we were
asked to respect students' privacy of NOT sending/posting the grades of the
entire class anymore.

Since I usually have several hundreds of students' grades to take care of
each semester, it really becomes a burden if I had to look up each student's
class taken/grade/email address and send out individual email notices.
Therefore, I'm wondering if there's any solution in Excel's macro or
programming capability to automatically send each individual student's grade
to him/her once I have their grades calculated in Excel?

What I have in mind is that I'll have the following info in an Excel file:

Cloumn A: Student's name
Column B: Student's ID
Column C: Student's email address
Column D: Name of the course
Column E: Name of the test (e.g. quiz #1, midterm exam, final exam, ...etc.)
Column F: grade or score
Column G: percentile for the preceding grade in the class
Column H: course coordinator's email address

After I'm done with the grading process, I wish to use my email app (MS
Outlook 2007) to automatically send each student with their perspective grade
info in the following format:

Dear [Column A]:
For the test Column E] of the course [Column D] you've registered, your
grade is [column F] with a [Column G]% among your classmates. If you have any
question on your grade, please contact the course coordinator at [Column H],
thanks.


Since I'm only an average Excel user w/o any knowledge on how to compose any
codes/macros, I wonder if this request is do-able withing Excel itself? I'll
highly appreciate if I could get any help/suggestion from you. Many thanks in
advance!

Best,

 

[JP]

If you have the Word/Outlook/Excel trio, consider using mail merge.
Just write the email in Word, using the Excel workbook as the data
source, and merge to email, using the email addresses stored in the
workbook. Each recipient will receive their own customized email. If
each student occupies one row, and you have 200 students, that means
200 customized emails with the information from each row sent to that
student.

http://support.microsoft.com/kb/294694

--JP

 

[Luke M]

Yes, this can be done. The main trick is setting up the data correctly, which
it looks like you've done already. Check out Ron de Bruin's exhaustive list
of topics regarding sending email from XL (There's even a few free add-ins,
aka no coding!):

http://www.rondebruin.nl/sendmail.htm

Alternatively, would it be possible to use a basic cypher/coding of the
student's info so that you can still post the entire list? In other words,
replace the students name with some pre-assigned scramble of letters so that
each student can look up their own scores w/o being able to identify what
anyone else got. This is something some of my professors implemented.