How to Automatically Generate and Email Sales Invoices from Google Forms? (Part 4)

This Python-based automation workflow tutorial has significantly benefited the business by streamlining the entire invoicing process, from retrieving customer data in Google Sheets to generating and sending personalised sales invoices via email with PDF attachments. Automating what was previously a manual task reduces human errors, saves time, and ensures faster communication with customers. Using secure email protocols and environment-based credential management improves data security, helps the business run more efficiently, and enhances the customer experience and scalability.

Preliminary
Before I begin, please activate the virtual environment and install the required dependencies.

python -m venv venv
venv\Scripts\activate
pip install dotenv

Since both the smtplib and email modules are standard libraries, they were pre-installed within Python. Dotenv accesses the API key and client secret in the .env file.

Then, we need to set up the file and folder structure, as below:

I will continue from the previous tutorial (part 3), as the files and folders remain the same as shown in the diagram above, except that I need to create a new file, email_util.py, and amend both pdf.py and gsheet.py to send an email to the customer. 

Step 1: Amend the gsheet.py to retrieve the customer name and email.

In part 1, when I loop over the data from the Google Sheet, I will retrieve the entire row because I need that information to create the PDF sales invoice. However, when I need to send an email, what I need to extract are the customer's name and email from the Google Sheet. Then, with this data, I will insert it into the email body. Therefore, I need to amend the code setup in gsheet.py.

def get_gsheet_data():
   '''
   The code is from Part 1
   '''
   # Skip the header row (first row) and only process the data rows
   data_rows = content[1:]
   
   # Create a list to store relevant info
   processed_data = []
   
   # Loop through each data row
   for row in data_rows:
      # Only print non-empty rows
      if any(cell.strip() for cell in row):
         
         # Extract customer name and email from specific columns
         customer_name = row[1]
         customer_email = row[3]
         
         # add the customer name and email to the empty list
         processed_data.append({
          "customer_name": customer_name,
          "customer_email": customer_email,
          # Store full row for future use if needed
          "full_row": row})
          
    return processed_data

Step 2: Configure Email Settings

Before I can send an email, I need to create a Gmail app password from this website: "https://myaccount.google.com/apppasswords". I need to log in to my Google account and perform two-step verification. I named it "Email_sales_invoice" for quick reference, as shown in the diagram below. Then, a Windows "generated app password" will pop up; simply copy the password and paste it into the .env file, and I will load the app password in email_util.py, as shown in the file and folder diagram above.

Next, I will proceed with setting up my email configuration, and this step allows me to access the Gmail service. Therefore, I need to import the relevant module as follows:

import smtplib, ssl
import os
from email.message import EmailMessage
import mimetypes
import dotenv 

# Gmail SMTP server address
SMTP_HOST = "smtp.gmail.com" 

# Port for TLS (secure email sending)  
SMTP_PORT = 587      

# Your Gmail email address (sender)         
SMTP_USER = "your email account">

# Load environment variables from .env file
dotenv.load_dotenv()

# Get your Gmail App Password from the environment variable
SMTP_PASS = os.getenv("GMAIL_PASSWORD")

You may use other email servers, such as Yahoo or Outlook. However, for this tutorial, I selected Gmail as my email server.

Step 3: Configure Email Settings

Now, in my email_util.py, I need to create and configure the relevant email fields, including sender, recipient, subject, and body, and also add an attachment. When all fields are ready, I will connect to the server, encrypt the connection, and finally send the email. The code is set up as follows:

def send_email_with_attachment(from_addr, to_addrs, subject, body,
   attachment_path=None):    
   
    # Create the email message object
    msg = EmailMessage()
    
    # Set sender email address
    msg["From"] = from_addr
    
    # Set recipient(s); supports both single email or list of emails
    msg["To"] = ", ".join(to_addrs) if isinstance(to_addrs, (list, tuple)) 
       else to_addrs
       
    # Set email subject
    msg["Subject"] = subject
    
    # Set email body (plain text)
    msg.set_content(body)

    # Attach file (e.g., PDF) if a file path is provided
    if attachment_path:
        # Guess the MIME type based on file extension
        mime_type, _ = mimetypes.guess_type(attachment_path)
        
        # Default to binary stream if type cannot be detected
        mime_type = mime_type or "application/octet-stream"
        
        # Split MIME type into main type and subtype (e.g., application/pdf)
        maintype, subtype = mime_type.split('/', 1)

        # Open the file in binary mode and attach it to the email
        with open(attachment_path, "rb") as f:
            msg.add_attachment(f.read(),
                               maintype=maintype,
                               subtype=subtype,
                               filename=os.path.basename(attachment_path))

    # Create a secure SSL context
    context = ssl.create_default_context()
    
    # Connect to SMTP server (Gmail)
    with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
    
        # Start TLS encryption for a secure connection
        server.starttls(context=context)
        
        # Login using your email and app password
        server.login(SMTP_USER, SMTP_PASS)
        
        # Send the email
        server.send_message(msg)

Step 4: Call the function to send an email

Before we call the function, I need to extract the customer name and email from step 1 so I can personalise each email by greeting them by the right customer name and send it by referencing their respective email address. This process is to avoid confusion and build trust so the right email goes to the right recipient. 

from gsheet import get_gsheet_data
from email_util import send_email_with_attachment, SMTP_USER

# Get data from Google Sheets
data = get_gsheet_data()

# Loop through each row (entry) in the data
for entry in data:   

    # Extract customer name from the current row
    customer = entry["customer_name"]
    
    # Extract email from the current row (IMPORTANT: make sure this key exists)
    email = entry["email"]
    
    # Send email with attachment
    send_email_with_attachment(
    
       # Sender email (your Gmail)
       from_addr=SMTP_USER,
       
       # Recipient email & Use the email retrieved from Google Sheets
       to_addrs={email}, 
       
       # Email subject
       subject = f"Sales Invoice from How App and Web",
      
       # Email body with dynamic customer name
       body = f"""Dear {customer},

         Please find attached your sales invoice for your recent purchase with 
         How App and Web.
         If you have any questions or need further assistance, feel free to 
         Reply to this email.

        Thank you for your business!

        Best regards,
        How App and Web, Team
       """,
    
      # Path to the PDF invoice file
      attachment_path="sales_invoice/my_invoice.pdf"  
)

Once everything was set, I could now invoke the function to send an email in pdf.py. Therefore, I run the following Python script in the terminal:

python sales_invoice/pdf.py

The email was sent out in my sent box. 

Final Wrap-up: 

This tutorial sets up a secure Gmail SMTP configuration using environment variables to protect sensitive credentials while enabling reliable email delivery. It includes a reusable Python function that sends personalised emails with PDF attachments, making it flexible for different use cases. By integrating data from Google Sheets, the system automates sending invoices to multiple customers with dynamic content like names and email addresses. Overall, it provides a scalable and production-ready automated invoice emailing solution, easily extendable with features like dynamic PDF generation or Flask integration. 

⬅ Previous Part

 To view the full code, please click here

🚀 Place Order Now 🖼️ View Real Projects ❓ Ask a Question First 💰Try the 98% Open Rate Method