Macro to email student info from a row to email address within row

[rich_d]

Hello,

I have a student mark book in excel, and I want a macro which will to send the assignment/homework scores for each student to that student, in an email with a bit of other text in the body of the email.

To describe the spreadsheet clearly:

Column A = student no.
Column C = student name
Column D = student email
Column E - J = student info that doesn't need to be sent
Column K onwards = assignment/homework scores that should be send out

Row 1 = random info that doesn't need to be sent
Row 2 = assignment/homework titles, e.g. assignment 1, assignment 2, etc. - these also need to be included in the email, so the scores all make sense and aren't just a random list of numbers.
Row 3 onwards = student info, scores, etc.


The email should be something like:

Dear student name (student no.)

Below are your assignment grades so for for this course blah blah

Assignment 1: 74
Assignment 2: 45
Assignment 3: 0

Regards, Blah blah, your teacher.​

I've searched around a bit and found some codes that are somewhat close, but being a science teacher I'm so far out of my depth with this stuff!

The computing teachers and IT guys here don't really know how to do what I'm after. I really hope someone here can help. This will make what would otherwise be an awful task (copying scores for 100+ students, every week, each into a new email) so quick and easy!

 

[hippiehacker]

try

Sub rich_d()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")


For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
Set OutMail = OutApp.CreateItem(0)
 strbody = "Dear " & Cells(i, "C") & vbNewLine _
            & vbNewLine _
            & "Below are your assignment grades so for for this course blah blah" & vbNewLine _
            & vbNewLine _
            & "Assignment 1: " & Cells(i, "K") & vbNewLine _
            & "Assignment 2: " & Cells(i, "L") & vbNewLine _
            & "Assignment 3: " & Cells(i, "M") & vbNewLine _
            & vbNewLine _
            & "Regards, Blah blah, your teacher."
            
 On Error Resume Next
  With OutMail
   .To = Cells(i, "D")
   .Subject = "assignment/homework scores"
   .Body = strbody
   .send
  End With
 On Error GoTo 0
Set OutMail = Nothing
Next i
End Sub