If you are a teacher or a professor, you probably have several students in your class. Let’s say you would like to provide them with an update on their averages after every single quiz and exam they take. But you hate to email each and everyone of them because it consumes a lot of time to copy data from your Excel file of student records to individual emails. This is where the extract data from row by row using loop iteration comes to save you.

At the beginning of the year you may have created a spreadsheet with student names, email address, averages for each and every exam and maybe even student numbers. The following example is created to show such Excel file (exception to student ID, which you can enter in a new column).

VB Script

If you have not work with VB Scripts before, please read the introduction article, here. I assume you know the fundamentals of Excel Macros. You may copy and paste the following VB Script to your Excel file OR download the file from here. (I will upload the file tonight!).

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 150 'Looping the rows 2-150, extracting data one row at a time'
'       Extract the email address found in every row  (limit defined in the for loop above) at cell number 2
        Email = Cells(r, 2)
        
'       Message subject
        Subj = "Your final exam grade are up!"

'       Compose the message
        Msg = ""
        Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that we have completed submitting your exam averages as follows..." & vbCrLf
        Msg = Msg & "Quiz No. 1: "
        Msg = Msg & Cells(r, 3).Text & "," & vbCrLf
        Msg = Msg & "Quiz No. 2: "
        Msg = Msg & Cells(r, 4).Text & "," & vbCrLf
        Msg = Msg & "Quiz No. 3: "
        Msg = Msg & Cells(r, 5).Text & "," & vbCrLf
        Msg = Msg & "Overall Average: "
        Msg = Msg & Cells(r, 6).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "Sanuja Senanayake" & vbCrLf
        Msg = Msg & "Instructor"
        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
'       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
    Next r
End Sub

There are several impotent codes in this program. The first line is a command to open shell32.dll which is the Windows default email. Depend on which program is set as your default, it may open any email program such as Outlook, LiveMail, Thunderbird, etc. Please be advised that once this program is “RUN”, it will automatically start creating email temples and send them. You cannot stop and check unless you force terminate it or it comes across an error.

The Sub SendEMail() is calling in the function to automate email processing. A function is like a box which contains data specific to an operation. In this perticular example, the box starts at Sub SendEMail() at line 5 and it ends at End Sub at line 47. Between the lines 5 and 47, we excite several commands in one of which is a for loop. The for loop is a very impotent iteration command. But I don’t want to go into detail since it is widely used in several different programming languages. What it does is to repeat few operations over and over for the given set window. The window in this case is defined as r = 2 To 150, which is rows 2 to 150 (in other words, if each row is for a new student, 148 students can be covered). We started iteration from row 2, because the first row is used for titles; Name, Email, Quiz# and Overall.

But what if you don’t have 148 students? No problem. Any VB Script like this can be modified to serve specific needs. All you have to do is to change the iteration window to any value you like in the for loop. Let’s say you only have 20 students, rewrite the for loop as For r = 2 To 22. Now you have few students who decided to join the last later than others. So, you decided to enter their names at the very end of the file. If you wan to send an email with their grades, but you do not want to send the same email to all the students, you can change the upper limit of the for loop to avoid unnecessary emails. For example, For r = 151 To 162 will only extract data from rows 151 to 162 and compose emails accordingly.

Next, you have five different quizzes, a midterm and a final exam. To use this script, simply add more cell reader in the the for loop extracting the cell you want. It is as simple as adding Msg = Msg & Cells(r, XXX).Text & “,” & vbCrLf in the current for loop , where XXX is the cell number.

There are many other modifications you can do with this type of script. However, I am not going to spoon feed you on flexibility of VB. Instead, I encourage you to come up with your own modifications. The script is free to copy, modify, reuse and republish with no credit to sanuja.com. To give you an idea on what you can do with it, try these;

• change the plain text to bold, italicized, etc texts in the email body
• replace the for loop with a while loop to prevent run on program
• create an if statement in the for/while loop to prevent invalid email address errors (exceptions)
• check for multiple email addresses on the machine and generate a pop-up before run the loop to give user the choice
• personalized the email by only taking the first word in the name column (avoiding the full name)
• …and many more creative ideas!

Sample Email

Here is an example of this script generated email.

If you have questions on customization and you don’t understand my comments within the script, fire me an email and I will do my best to help you out. You can also read more Visual Basic articles here.