How to automate and personalize your cold email in 20 minutes- for free!

The future is here, the future is now, and the future is weird. We are entering an ugly era, transitioning from a capped era of content production to an uncapped era. Just a few years ago, the fastest we could generate text-based content was capped at how fast a person could type. Now, the lid is off the pot. Essays, tweets, emails, articles (even newsletters!) are generated within seconds, seriously devaluing the utility of a piece of content.

This new reality has divided the camp into two distinct groups — one all-in on automated content and the other turned off by any automation. This false dilemma is not the way to proceed. Identifying where automation (and especially LLMs) can be the most beneficial is critical.

Today, we will examine cold emails and automate the outreach workflow. This kind of marketing tactic is a numbers game. With a naturally low conversion rate, you want to send as many emails as possible without draining resources. If you have a simple CRM or target list in Google Sheets, let’s see how we can automate a tedious process and help you become more efficient.

For this example, we’ll pretend we’re a marketing automation agency that finds clients based on their previous campaigns, and pitches to automate their next one.

P.S. if you want to learn how to build new automation projects to supercharge your business or career, my email list gets them all first! It’s free at learnprompter.com

Here’s what our automation flow will look like:

The tech stack we will use is as follows:

1. Google Cloud
2. Google Sheets
3. VSCode (or the IDE of your choice)
4. OpenAI
5. Gmail

Make sure these are downloaded and/or set up before attempting this script! Before we start, here’s some exciting news to help you with this project:

Prompter has its own GPT!

I’m thinking about what’s next for the future of long-form interactive content. A core weakness of tutoring or educational content is the lack of back-and-forth between “teacher” and “student.” PrompterGPT aims to fix that. All articles and projects have been pre-uploaded to PrompterGPT’s memory, allowing you to troubleshoot errors in the code, customize the project to fit your needs, or even learn more about a particular module or application.

I’m incredibly excited to see where this goes! You can check it out here:

PrompterGPT

Section 1: CRM Set Up

First, let’s set up our CRM. We will use a simple one with just four fields: our prospect’s name, business, email, and a short note about their last marketing campaign. This last field is how we will create a more personalized email. Your CRM should look like this:

Make sure you create this sheet using the same Google account from which you will send emails.

Section 2: Environment Set Up

First, make sure you have Python and VSCode set up. If you have never done this, here’s a short and easy tutorial link. Set up a folder for this project and open it in your VSCode environment.

The next thing we will do is set up Google Cloud. This allows us to interact with our applications, such as Google Drive, Google Sheets, and Gmail.

Here’s how to set up the project in Google Cloud:

1. Visit Google Cloud Console:

• Open your web browser and go to the Google Cloud Console.
• Sign in with your Google account if you’re not already signed in.

2. Create a New Project:

• In the Google Cloud Console, on the top toolbar, click on the project dropdown (next to “Google Cloud Platform”).
• Click on “New Project” in the top right corner of the dropdown.
• Give your project a name, for example, AutoEmail.
• Click “Create”.

Step 2: Enable APIs

1. Enable Gmail, Sheets, and Drive APIs:

• Once your project is created, ensure it is selected in the top toolbar.
• Navigate to “APIs & Services” using the left sidebar, then click on “Dashboard”.
• Click on “+ ENABLE APIS AND SERVICES” at the top of the page.
• Search for “Gmail API”, click on it, and then click “Enable”. Repeat this step for “Google Sheets API” and “Google Drive API”.

Step 3: Configure OAuth Consent Screen

1. Configure the OAuth Consent Screen:

• Go back to the “APIs & Services” dashboard.
• On the left menu, click on “OAuth consent screen”.
• Select “External” for the user type if you plan to use applications outside of your organization. Click “Create”.
• Enter the app name, user support email, and developer contact information.
• IMPORTANT: Add your email address under “Test users” to be able to use this application.
• Click “Save and Continue” until you complete the setup.

Step 4: Create Credentials

1. Create OAuth 2.0 Credentials:

• In the “APIs & Services > Credentials” section, click on “Create Credentials” at the top.
• Select “OAuth client ID”.
• Application type should be “Desktop app” for this script.
• Give your client ID a name, for example, Desktop Client for Email Automation.
• Click “Create”.
• After the credentials are created, click “Download JSON” on the OAuth 2.0 Client IDs section. This downloads your credentials file.
• Rename the downloaded file to client_secret.json and save it to your project folder.

Step 5: Prepare for Python Code

1. Move the client_secret.json to Your Project Folder:

• Ensure the client_secret.json file is in the same folder as your Python script. This file contains sensitive information and should be kept secure.

Section 3: Adding Python Code

Now it’s time to add your Python code to a new file in the same directory as your .json file.

First, let’s start with the required packages.

In the terminal part of your VSCode environment, type the following in and hit enter to install the needed packages:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib gspread openai

Next, create a new Python file called “main.py” and add the following code:

import os
import pickle
import gspread
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from openai import OpenAI

client = OpenAI(api_key='ENTER YOUR OPENAI KEY')
from googleapiclient.discovery import build
import base64
from email.mime.text import MIMEText
import logging
# Setup logging
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s')
# Define the OAuth2 scopes
SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/gmail.compose",
    "https://www.googleapis.com/auth/gmail.modify",
    "https://www.googleapis.com/auth/gmail.send",
    "https://www.googleapis.com/auth/drive"
]
# Function to authenticate and create the Sheets and Gmail API client
def authenticate_google_apis():
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
            logging.debug("Loaded credentials from token.pickle.")
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
            logging.debug("Refreshed credentials.")
        else:
            flow = InstalledAppFlow.from_client_secrets_file('client_secret.json', SCOPES)
            creds = flow.run_local_server(port=0)
            logging.debug("New credentials obtained.")
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
            logging.debug("Credentials saved to token.pickle.")
    return creds
def get_google_sheets_data(creds, sheet_name):
    try:
        gc = gspread.authorize(creds)
        sheet = gc.open(sheet_name).sheet1
        return sheet.get_all_records()
    except Exception as e:
        logging.error(f"Error accessing Google Sheets: {e}")
        raise
def generate_email_content(name, business, campaign_details):
    try:
        completion = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {f"role": "system", "content": f"""
    You are writing a cold email to solicit marketing services by my company Acme Marketing. We use automations to enhance marketing campaigns.
             Write an email to {name} for their business {business} and reference their most recent marketing campaign: {campaign_details}. Share how we can improve their costs through automation.
               Sign it off with Nishad P., Acme Marketing, my email npasricha@acmemarketing, and my phone number (123) 456 7890 Write it professionally. Do not include a subject line.
    """},
        ])
        return completion.choices[0].message.content
    except Exception as e:
        logging.error(f"Failed to generate email content: {e}")
        raise
def authenticate_gmail(creds):
    try:
        service = build('gmail', 'v1', credentials=creds)
        return service
    except Exception as e:
        logging.error(f"Error accessing Gmail API: {e}")
        raise
def create_draft(service, user_id, message_body):
    try:
        message = {'message': message_body}
        draft = service.users().drafts().create(userId=user_id, body=message).execute()
        logging.info(f"Draft created: {draft['id']}")
        return draft
    except Exception as e:
        logging.error(f"Error creating draft: {e}")
        raise
def main():
    creds = authenticate_google_apis()
    sheet_name = "Prompter Example CRM"
    data = get_google_sheets_data(creds, sheet_name)
    service = authenticate_gmail(creds)
    for record in data:
        try:
            email_content = generate_email_content(record['Name'], record['Business'], record['Most Recent Campaign Details'])
            message = MIMEText(email_content)
            message['to'] = record['Email']
            message['subject'] = 'Follow-up on your recent marketing campaign'
            raw = base64.urlsafe_b64encode(message.as_bytes()).decode()
            message_body = {'raw': raw}
            create_draft(service, 'me', message_body)
        except Exception as e:
            logging.error(f"Failed to process record for {record['Email']}: {e}")
if __name__ == "__main__":
    main()

Your environment should look roughly like this:

Next, we need to customize just three aspects of this code before we are ready to run:

1. Your OpenAI Key
2. Your Prompt
3. Your Google Sheet Name

Starting with your OpenAI API key (I teach you how to get that here), find the block of code near the top that looks like this:

client = OpenAI(api_key="ENTER YOUR OPENAI KEY")

Now, insert your key between the quotes. Remember to keep your key a secret; that is how your account is billed. (Each call only costs a fraction of a cent)

Next, let’s edit your prompt that generates the email content. Find this block:

completion = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {f"role": "system", "content": f"""
    You are writing a cold email to solicit marketing services by my company Acme Marketing. We use automations to enhance marketing campaigns.
             Write an email to {name} for their business {business} and reference their most recent marketing campaign: {campaign_details}. Share how we can improve their costs through automation.
               Sign it off with Nishad P., Acme Marketing, my email npasricha@acmemarketing, and my phone number (123) 456 7890 Write it professionally. Do not include a subject line.
    """},
        ])

Between the two triple quotation marks is where you’ll want to edit the kind of email you are writing. The variables in the { } brackets pull in each record’s value from your CRM. This allows ChatGPT to reference small bits of data and not have to type each record’s name individually. (Note: We are not including a subject line, as this code just creates the body of the email.)

Finally, the last thing to do is edit the name of the Google Sheet this program is finding. Ensure that this line matches the name of your sheet exactly:

sheet_name = "Prompter Example CRM"

Section 4: Run our code!

Click the play button on the top left to run your code. Depending on how many records are in your CRM, each record will take around 3–5 seconds to generate a response for and create a draft in Gmail.

If this is your first time running the application, you will be taken to a new browser window and asked to sign into Google. Ensure that you are signing in with the same account you are permitted to as a “test user,” or else you will hit an error.

After the program finishes (new text will stop generating in the Terminal box in VSCode), check your Gmail! In the drafts inbox, fresh outreach emails are ready to send to your prospects.

That’s it! Your emails are ready to be sent. Make sure to customize the prompt as little or as much as you want to shorten the length, tune the content, or more.

Thanks for tuning in to this project. I believe this is a great way to automate a task that requires a lot of effort for such a low success rate.

If you’re having issues, check out PrompterGPT for troubleshooting. Just make sure to identify which project you’re working on!

If you liked this project or want to learn more about how you can use AI in your career, join my email list for free at learnprompter.com

See you next time!

— Nishad from Prompter