{"id":5394,"date":"2013-05-05T10:24:24","date_gmt":"2013-05-05T16:24:24","guid":{"rendered":"http:\/\/sanuja.com\/blog\/?p=5394"},"modified":"2020-05-31T13:46:00","modified_gmt":"2020-05-31T19:46:00","slug":"extract-data-from-rows-for-email","status":"publish","type":"post","link":"https:\/\/sanuja.com\/blog\/extract-data-from-rows-for-email","title":{"rendered":"Extract data from rows for email"},"content":{"rendered":"

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.<\/p>\n

<\/p>\n

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).
\n

\"Sheet
Sheet for the loop iteration in VisualBasic.<\/figcaption><\/figure><\/p>\n

VB Script<\/h2>\n

If you have not work with VB Scripts before, please read the introduction article, here<\/a>. 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!).<\/p>\n

Private Declare Function ShellExecute Lib \"shell32.dll\" _\r\nAlias \"ShellExecuteA\" (ByVal hwnd As Long, ByVal lpOperation As String, _\r\nByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _\r\nByVal nShowCmd As Long) As Long\r\nSub SendEMail()\r\n    Dim Email As String, Subj As String\r\n    Dim Msg As String, URL As String\r\n    Dim r As Integer, x As Double\r\n    For r = 2 To 150 'Looping the rows 2-150, extracting data one row at a time'\r\n'       Extract the email address found in every row  (limit defined in the for loop above) at cell number 2\r\n        Email = Cells(r, 2)\r\n        \r\n'       Message subject\r\n        Subj = \"Your final exam grade are up!\"\r\n\r\n'       Compose the message\r\n        Msg = \"\"\r\n        Msg = Msg & \"Dear \" & Cells(r, 1) & \",\" & vbCrLf & vbCrLf\r\n        Msg = Msg & \"I am pleased to inform you that we have completed submitting your exam averages as follows...\" & vbCrLf\r\n        Msg = Msg & \"Quiz No. 1: \"\r\n        Msg = Msg & Cells(r, 3).Text & \",\" & vbCrLf\r\n        Msg = Msg & \"Quiz No. 2: \"\r\n        Msg = Msg & Cells(r, 4).Text & \",\" & vbCrLf\r\n        Msg = Msg & \"Quiz No. 3: \"\r\n        Msg = Msg & Cells(r, 5).Text & \",\" & vbCrLf\r\n        Msg = Msg & \"Overall Average: \"\r\n        Msg = Msg & Cells(r, 6).Text & \".\" & vbCrLf & vbCrLf\r\n        Msg = Msg & \"Sanuja Senanayake\" & vbCrLf\r\n        Msg = Msg & \"Instructor\"\r\n        \r\n'       Replace spaces with %20 (hex)\r\n        Subj = Application.WorksheetFunction.Substitute(Subj, \" \", \"%20\")\r\n        Msg = Application.WorksheetFunction.Substitute(Msg, \" \", \"%20\")\r\n                \r\n'       Replace carriage returns with %0D%0A (hex)\r\n        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, \"%0D%0A\")\r\n'       Create the URL\r\n        URL = \"mailto:\" & Email & \"?subject=\" & Subj & \"&body=\" & Msg\r\n\r\n'       Execute the URL (start the email client)\r\n        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus\r\n\r\n'       Wait two seconds before sending keystrokes\r\n        Application.Wait (Now + TimeValue(\"0:00:02\"))\r\n        Application.SendKeys \"%s\"\r\n    Next r\r\nEnd Sub<\/pre>\n
There are several impotent codes in this program. The first line is a command to open shell32.dll <\/em>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.<\/em><\/p>\n
The Sub SendEMail()<\/em> 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()<\/em> at line 5 and it ends at End Sub<\/em> at line 47. Between the lines 5 and 47, we excite several commands in one of which is a for loop<\/u>. 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<\/em>, 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.<\/p>\n
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<\/em>. Let’s say you only have 20 students, rewrite the for loop<\/em> as For r = 2 To 22<\/em>. 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<\/em> will only extract data from rows 151 to 162 and compose emails accordingly.<\/p>\n
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<\/em> in the current for loop<\/em> , where  XXX is the cell number.<\/p>\n
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;<\/p>\n