send-camper-acceptance-emails.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "import ssl\n",
    "import smtplib\n",
    "\n",
    "from email.mime.multipart import MIMEMultipart\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.application import MIMEApplication\n",
    "\n",
    "import ywc_email_template"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 26 entries, 0 to 25\n",
      "Data columns (total 8 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   twa_ref          26 non-null     object\n",
      " 1   camper_title     26 non-null     object\n",
      " 2   camper_fullname  26 non-null     object\n",
      " 3   camper_major     26 non-null     object\n",
      " 4   camper_desc      26 non-null     object\n",
      " 5   recipient        26 non-null     object\n",
      " 6   to_email         26 non-null     object\n",
      " 7   cc_emails        2 non-null      object\n",
      "dtypes: object(8)\n",
      "memory usage: 1.8+ KB\n"
     ]
    }
   ],
   "source": [
    "letters = pd.read_excel(\"camper-acceptance-letters/รายชื่อหนังสือรับรอง camper.xlsx\")\n",
    "letters.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "letters = letters[letters[\"twa_ref\"] == \"สผวท. 066/66\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25    สผวท. 066-66.pdf\n",
       "Name: twa_ref, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "letters[\"twa_ref\"].apply(lambda x: \"สผวท. \" + x.split(\" \")[1].replace(\"/\",\"-\") + \".pdf\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "letters[\"filename\"] = letters[\"twa_ref\"].apply(\n",
    "    lambda x: \"สผวท. \" + x.split(\" \")[1].replace(\"/\", \"-\") + \".pdf\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"camper-acceptance-letters/เอกสารโครงการ Young Webmaster Camp ครั้งที่ 19.pdf\", \"rb\") as file:\n",
    "    ywc19_pdf_file = MIMEApplication(file.read())\n",
    "    ywc19_pdf_file.add_header(\n",
    "        \"Content-Disposition\",\n",
    "        \"attachment\",\n",
    "        filename=\"เอกสารโครงการ Young Webmaster Camp ครั้งที่ 19.pdf\",\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "\n",
    "sender_email = \"ywc@webmaster.or.th\"\n",
    "sender_password = getpass.getpass(\"Password: \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully sent สผวท. 066-66.pdf\n"
     ]
    }
   ],
   "source": [
    "context = ssl.create_default_context()\n",
    "\n",
    "with smtplib.SMTP_SSL(\"smtp.gmail.com\", 465, context=context) as smtp:\n",
    "    smtp.login(sender_email, sender_password)\n",
    "\n",
    "    for letter in list(letters.itertuples()):\n",
    "        msg = MIMEMultipart()\n",
    "\n",
    "        msg[\"From\"] = sender_email\n",
    "        msg[\"To\"] = letter.to_email\n",
    "\n",
    "        recipients = list()\n",
    "        recipients.append(letter.to_email)\n",
    "        if not pd.isnull(letter.cc_emails):\n",
    "            recipients += letter.cc_emails.split(\",\")\n",
    "            msg[\"Cc\"] = letter.cc_emails\n",
    "\n",
    "        msg[\"Subject\"] = (\n",
    "            \"[YWC19] ส่งเอกสารรับรองการคัดเลือกเข้าร่วมโครงการ - \"\n",
    "            + letter.camper_fullname\n",
    "        )\n",
    "\n",
    "        with open(f\"camper-acceptance-letters/letters/{letter.filename}\", \"rb\") as file:\n",
    "            letter_pdf_file = MIMEApplication(file.read())\n",
    "            letter_pdf_file.add_header(\n",
    "                \"Content-Disposition\",\n",
    "                \"attachment\",\n",
    "                filename=f\"หนังสือรับรองการคัดเลือกเข้าร่วมโครงการ YWC19 - {letter.camper_fullname}.pdf\",\n",
    "            )\n",
    "            \n",
    "        \n",
    "        msgText = MIMEText(\n",
    "            ywc_email_template.build_camper_acceptance_letter_email(\n",
    "                camper_title=letter.camper_title,\n",
    "                camper_fullname=letter.camper_fullname,\n",
    "                camper_major=letter.camper_major,\n",
    "            ),\n",
    "            \"html\",\n",
    "        )\n",
    "        msg.attach(msgText)\n",
    "        msg.attach(letter_pdf_file)\n",
    "        msg.attach(ywc19_pdf_file)\n",
    "        \n",
    "        smtp.sendmail(\n",
    "            sender_email,\n",
    "            recipients,\n",
    "            msg.as_string(),\n",
    "        )\n",
    "        print(f\"Successfully sent {letter.filename}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}