Automate Boring Office Tasks with ChatGPT and Python

Introduction

This tutorial provides step-by-step instructions on how to automate various office tasks using ChatGPT and Python. By leveraging these tools, you can streamline processes in Outlook, create PowerPoint presentations, generate interactive charts, merge PDFs, compose emails, and even build websites. This guide will help you implement these automations effectively.

Chapter 1: Distribute Excel Files with Outlook

To automate the distribution of Excel files via Outlook:

1. Prepare Your Data

   • Ensure you have an Excel workbook named financial data.xlsx with a sheet called email list.
   • In the email list sheet, columns should include:
     • Column A: Recipient Name
     • Column B: Recipient Email
     • Column C: CC Email
     • Column D: Attachment File Name

2. Create a Python Script

   • Use ChatGPT to generate a Python script with the following functionality:
     • Read data from the email list sheet.
     • Compose Outlook emails using the recipient's name and email addresses.
     • Attach files located in an attachments folder in the same directory.

3. Sample Code

   import pandas as pd
   import win32com.client as win32
   
   data = pd.read_excel('financial data.xlsx', sheet_name='email list')
   
   outlook = win32.Dispatch('outlook.application')
   
   for index, row in data.iterrows():
       mail = outlook.CreateItem(0)
       mail.To = row['Recipient Email']
       mail.CC = row['CC Email']
       mail.Subject = 'Financial Data'
       mail.Body = f"Hello {row['Recipient Name']},\n\nPlease find the financial data attached."
       attachment_path = f"attachments/{row['Attachment File Name']}"
       mail.Attachments.Add(attachment_path)
       mail.Display()  # Opens the email without sending it
   

4. Run the Script

   • Execute the script. Outlook will create emails with the specified attachments.

5. Handling Errors

   • If you encounter warnings, consult ChatGPT for modified code to address them.

Chapter 2: Automate PowerPoint Creation

To automate the creation of a PowerPoint presentation:

1. Prepare Your Data

   • Ensure you have Excel files in a specified input folder containing sales data.

2. Define the Automation Steps

   • The script should:
     • Read all Excel files in the input folder.
     • Exclude missing values and group sales by product.
     • Generate bar charts using a specified library (e.g., matplotlib or plotly).

3. Sample Code

   import pandas as pd
   import matplotlib.pyplot as plt
   from pptx import Presentation
   
   input_folder = 'input/'
   chart_folder = 'charts/'
   
   # Create charts from Excel files
   for file in os.listdir(input_folder):
       data = pd.read_excel(os.path.join(input_folder, file))
       grouped_data = data.groupby('Product')['Sales'].sum()
       plt.bar(grouped_data.index, grouped_data.values)
       plt.savefig(f"{chart_folder}/{file}.png")
       plt.close()
   
   # Create PowerPoint presentation
   prs = Presentation()
   for chart_file in os.listdir(chart_folder):
       slide = prs.slides.add_slide(prs.slide_layouts[5])  # Blank slide
       slide.shapes.add_picture(os.path.join(chart_folder, chart_file), left=0, top=0)
   prs.save('Financial_data.pptx')
   

4. Run the Script

   • Execute the script to generate a PowerPoint presentation with the created charts.

Chapter 3: Create Interactive Charts from Excel Data

To create an interactive chart:

1. Prepare Your Data

   • Use the same sales data from previous examples.

2. Define the Automation Steps

   • Sum up the sales for each country.
   • Generate an interactive chart with custom color codes.

3. Sample Code

   import pandas as pd
   import plotly.express as px
   
   data = pd.read_excel('financial data.xlsx')
   sales_summary = data.groupby('Country')['Sales'].sum().reset_index()
   
   fig = px.bar(sales_summary, x='Country', y='Sales', title='Sales by Country', color_discrete_sequence=['#FF5733'])
   fig.write_html('interactive_chart.html')
   

4. Run the Script

   • Execute the script to generate an HTML file with the interactive chart.

Chapter 4: Merge PDFs

To merge PDF files from two folders:

1. Prepare Your Data

   • Ensure you have a cover folder with cover PDFs and a draft folder with corresponding PDFs.

2. Define the Automation Steps

   • Match cover PDFs with draft PDFs based on naming conventions (e.g., numerical names).

3. Sample Code

   from PyPDF2 import PdfMerger
   import os
   
   merger = PdfMerger()
   cover_folder = 'cover/'
   draft_folder = 'draft/'
   output_folder = 'output/'
   
   for cover_file in os.listdir(cover_folder):
       draft_file = f"{cover_file.split('.')[0]}.pdf"  # Assuming naming convention
       merger.append(os.path.join(cover_folder, cover_file))
       merger.append(os.path.join(draft_folder, draft_file))
   
   merger.write(os.path.join(output_folder, 'merged.pdf'))
   merger.close()
   

4. Run the Script

   • Execute the script to create a merged PDF file.

Chapter 5: Write Emails

To compose a well-written email:

1. Define Your Request

   • Use ChatGPT to generate an email requesting a salary increase or any other formal request.

2. Sample Email Prompt

   • Provide ChatGPT with context about your work and reasons for your request.

3. Receive and Edit the Email

   • Review the generated email, make adjustments if necessary, and send it through your preferred email client.

Chapter 6: Create a Streamlit Website

To create a simple website:

1. Use ChatGPT to Generate Code

   • Request a Streamlit application code to showcase your work or automate tasks.

2. Sample Code

   import streamlit as st
   
   st.title('Automated Office Tasks')
   st.write('This app demonstrates how to automate various office tasks.')
   

3. Run the Application

   • Execute the Streamlit application and access it via your web browser.

Conclusion

This guide has covered various methods to automate tedious office tasks using ChatGPT and Python. By implementing these steps, you can significantly improve efficiency in your workplace. Experiment with the provided scripts and modify them to suit your specific needs. Happy automating!