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:
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):
Remainder of code for this code module continued in next post.
Lewis
Bookmarks