# Office 365 >  >  grades to students

## davidkayla

hi,
if you don't mind ill write this in lower case because i am mobile. 

my requirement is to provide my students their grades via a cloud based service.

at the moment i have an excel spreadsheet with the test criteria down the left and the student's names across the top.

at the moment i hide everyone else's grades and then copy to image one student's grades for each criteria (2 columns) and then email that image out. it can take a little while.

is there an easier way? 
i am very computer literate but didn't want to develop code.

i was thinking along the lines of providing them secret numbers and then somehow them accessing their results using these numbers.

----------


## AliGW

Welcome to the forum!

The same issues will apply to you as they do for this forum member (thread from a short while ago): https://www.excelforum.com/excel-for...rotection.html

----------


## AliGW

Have you considered a mail merge?

----------


## davidkayla

Hi,
Thanks for your response. Because I am busy marking at the moment I haven't got time to investigate the mail merge solution.
As I said before I presently cut the results and paste into an email. This isn't a bad solution and I only have to do it once every couple of weeks.

Maybe your mail merge suggestion would be ideal but this requirement seems as though a serious solution should be out there somewhere.

I use Blackboard as well and now I'm wondering if there is a way to import a spreadsheet into BB. I think not.
Next year we are using Canvas. I will write to the Canvas people to see if it possible using their tool.

I read they other guy/girl's request that you referred me to. They wanted the same thing!
It is my dream to allow my students all to access (privately) a spreadsheet in the cloud.
d.

----------


## AliGW

None of this is difficult, and you can set up a mail merge from Excel to Outlook, so you could automate your E-mails if you were so inclined. I would be surprised if the third-party options you mention DIDN'T accept data import, at least from a .csv file, which can be generated from Excel.

By the way, I'm a teacher, too.  :Smilie: 

EDIT: I found these.

https://help.blackboard.com/Learn/In...ith_Grade_Data

https://community.canvaslms.com/docs...585-4214164118

----------


## LJMetzger

Hi  davidkayla,

I prefer the VBA code method over Mail Merge, as Mail Merge always seems to give me problems.  Repetitive items  such as your application can be customized using code for specific needs.  The attached code uses Outlook to send the Emails.  Outlook tries to protect us from rogue code, so there is a setting that requires as a prerequisite:
a. Up to date anti-virus software
b. Administrator access - one time only

Please note that sending multiple Emails at once can be a disaster if the wrong data is sent.  Please verify data (by sending a test email to yourself first).  In addition, you may need special privileges from your Internet provider, who may think you are a spammer.


To prevent OutLook Security Message such as (and to remain secure):							
a.  A program is trying to send an e-mail message on your behalf, or							
b. A program is trying to access e-mail addresses:							
Make sure Anti-Virus software is up to date							
Go to Windows  'Start' Menu							
Right Click on 'Outlook 2016' and select 'Run as Administrator'							
File > Options > Trust Center > Trust Center Settings > Programmatic Access							
AntiVirus status : Valid' should be displayed in the middle of the Screen.		

Your requirements:
a. my requirement is to provide my students their grades via a cloud based service.
b. at the moment i have an excel spreadsheet with the test criteria down the left and the student's names across the top.
c. at the moment i hide everyone else's grades and then copy to image one student's grades for each criteria (2 columns) and then email that image out.

What the attached file does:
a. Sheet 'Sheet1' contains sample data that hopefully simulates your actual data.
b. Sheet 'Control' contains:
(1) CommandButtons to create one test email (to a specific email address), one production email, or production email for all students.  There is one set of CommandButtons that use the 'Display' option (which allows editing in Outlook before sending), and another set of CommandButtons that use the 'Send' option, that sends the Email over the Internet according to your Outlook 'Send' rules.
(2) Items that can be changed by the User such as:
(a) Test email address
(b) Email Subject
(c) Email Body
The Email body contains KEYWORDs which can be replaced by actual data, such as 'Student Name', and the 'Data Row' or rows to include in the Email.

To access a Module in VBA:
a. 'Left Click' on any cell in the Excel Spreadsheet.
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. 'Double Click' on the module you want to access in the 'Project Explorer'.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:



```
Please Login or Register  to view this content.
```


This option requires all variables to be declared and will give a compiler error for undeclared variables.

The following code specific to your application is included in ordinary code module *ModOutlookStudentEmails* (may be continued on the next post if Excelforum thinks the code is too long):



```
Please Login or Register  to view this content.
```


*Remainder of code for this code module continued in next post.*

Lewis

----------


## LJMetzger

Continuation of code from ordinary code module *ModOutlookStudentEmails*:



```
Please Login or Register  to view this content.
```


General Outlook code included in Ordinary Code Module *ModOutlookEmailGeneral*:



```
Please Login or Register  to view this content.
```


Lewis

Firewall problems: to use this code replace all *qqq* with* <*.  Firewall thinks the use of the '*<*' character is HTML code.

----------


## regreg

thank you. i did have this problem to

----------


## LJMetzger

My previous posts #6 and #7 used Email as text only.

The attached Excel file sends HTML Email(s) using Outlook and has the following capabilities:
a. Optional Email body 1 - can control Font characteristics
b. Optional HTML Range
c. Optional Email body 2 - can control Font characteristics
d. Optional Signature - Outlook default signature or any existing Outlook signature

In a. thru c. above certain KEYWORDS can be replaced with data (pseudo Mail Merge)

A few important routines are included below (for complete working code see the attached file):



```
Please Login or Register  to view this content.
```


Firewall problems: to use this code replace all *qqq* with* <*.  Firewall thinks the use of the '*<*' character is HTML code.


Lewis

----------

