PAFE Proctor Email Draft Generator

This toolset generates personalized Outlook draft emails for PAFE in-room proctors using:

1. A session contact CSV exported from the scheduling system
2. A PowerShell consolidation script that groups sessions by person
3. A mail merge script that fills an HTML email template and saves drafts in Outlook

The workflow prevents duplicate emails and ensures each volunteer receives one email listing all of their sessions.

---

Overview of Workflow

Session Export CSV
│
▼
Convert-ProctorCSV.ps1
│
▼
Consolidated CSV (one row per volunteer)
│
▼
Create-OutlookDrafts.ps1
│
▼
Draft Emails in Outlook

---

Requirements

Software

• Windows
• PowerShell 5.1+ (installed by default)
• Microsoft Outlook Desktop installed and configured
• Outlook account with permission to send from:

[president@pafenorthwest.org](mailto:president@pafenorthwest.org)

Outlook Setup

The Outlook profile running the script must:

• Have the mailbox president@pafenorthwest.org added OR
• Have Send As / Send on Behalf permissions for that mailbox.

---

Step 1 — Prepare the Input CSV

Export the proctor sessions to a CSV with one row per session assignment.

Required Columns

Column	Description
Date	Full session date/time
Session	Description of the session
Person	Volunteer name
Email	Email address
Phone	Phone number (optional)

Example Input CSV

Date,Session,Person,Email,Phone
04/07/2026 (Tue. 8:00AM - 12:00PM),Low Strings Concerto with Adjudicator2,Mike Person,name@gmail.com,4244242
04/07/2026 (Tue. 1:00PM - 5:00PM),High Strings with Adjudicator1,John Last,first.last@gmail.com,
04/08/2026 (Wed. 8:00AM - 12:00PM),High Strings with Adjudicator1,Roger,first_last@comcast.net,4244242

Notes

Rows with blank Email will automatically be skipped.

---

Step 2 — Consolidate Sessions by Person

Run the consolidation script.

.\Convert-ProctorCSV.ps1 `
  -InputPath .\pafe_sessions_contacts.csv `
  -OutputPath .\proctors-merged.csv `
  -SessionListFormat Html

What This Script Does

• Groups rows by email
• Combines sessions into a single column
• Produces one row per volunteer

Output CSV Format

Column	Description
Email	Email address
Person	Volunteer name
Phone	Phone number
SessionList	List of sessions separated by <br>

Example Output

Email,Person,Phone,SessionList
a_person@gmail.com,A Person,4242424,"04/07/2026 (Tue. 1:00PM - 5:00PM) — Piano Concerto with Adjudicator3<br>04/08/2026 (Wed. 1:00PM - 5:00PM) — High Strings with Adjudicator2"
tchen@gmail.com,T Chen,4244242,"04/07/2026 (Tue. 8:00AM - 12:00PM) — High Strings Concerto with Adjudicator4<br>04/08/2026 (Wed. 1:00PM - 5:00PM) — Cello Concerto with Adjudicator1"

---

Step 3 — Create the Email Template

Create an HTML template file.

Example:

VolunteerTemplate.html

Template Structure

The first line must contain the email subject.

Subject: PAFE Volunteer Confirmation – {{Person}}

All other content is HTML.

Example Template

Subject: PAFE Volunteer Confirmation – {{Person}}

<p>Dear {{Person}},</p>

<p>
Thank you for volunteering as an <strong>in-room proctor</strong>
at the Performing Arts Festival of the Eastside (PAFE).
</p>

<p>
Please confirm that <strong>{{Phone}}</strong> is the best phone
number to reach you.
</p>

<p>
<strong>You are currently signed up for the following sessions:</strong><br>
{{SessionList}}
</p>

<p>
Warmest regards,<br>
First Last<br>
President, PAFE
</p>

---

Supported Template Placeholders

These must match CSV column names.

Placeholder	Source
{{Person}}	CSV column
{{Phone}}	CSV column
{{SessionList}}	Consolidated session list
{{Email}}	CSV column

---

Step 4 — Generate Draft Emails

Run the draft creation script.

.\Create-OutlookDrafts.ps1 `
  -CsvPath .\proctors-merged.csv `
  -TemplatePath .\VolunteerTemplate.html `
  -FromAccount president@pafenorthwest.org

What This Script Does

• Opens Outlook
• Reads the template
• Replaces placeholders
• Creates one draft email per volunteer

Emails are saved in:

Outlook → Drafts

---

Preview Mode (Recommended First)

Before generating drafts:

.\Create-OutlookDrafts.ps1 `
  -CsvPath .\proctors-merged.csv `
  -TemplatePath .\VolunteerTemplate.html `
  -PreviewOnly

This prints rendered emails to the console without creating drafts.

---

Optional Features

Tag Drafts with a Category

-DraftCategory "PAFE Proctors"

Example:

.\Create-OutlookDrafts.ps1 `
  -CsvPath .\proctors-merged.csv `
  -TemplatePath .\VolunteerTemplate.html `
  -DraftCategory "PAFE Proctors"

---

Troubleshooting

Script opens in Notepad

Run scripts from PowerShell, not double-click.

powershell
cd path\to\scripts
.\Convert-ProctorCSV.ps1

---

Execution Policy Error

Allow scripts:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

---

Outlook Account Not Found

The script prints:

Outlook account <account> was not found

Ensure the mailbox is added to Outlook.

---

Phone Numbers Show as 4.26E+09

Excel converted the number to scientific notation.

Fix by formatting the phone column as Text before exporting.

---

Best Practices

• Always run Preview Mode first
• Test with a small CSV (2–3 rows)
• Review drafts before sending
• Keep templates under version control

---

Files in This Project

Convert-ProctorCSV.ps1
Create-OutlookDrafts.ps1
VolunteerTemplate.html
README.md

---

Maintainer

PAFE Technical Tools Performing Arts Festival of the Eastside