Running a hiking business means most of your time should be on the trail. But every week, there's a newsletter to write – that means checking our upcoming community events, adapting the description, extracting the key information and booking link... For me, that was consistently over an hour of work (or more, try getting this done after a full day of inspecting a trail / hiking out in the mountains).
The bigger problem: the days when it most needs to happen are the exact days we're unavailable. Weekends are for leading hikes. Weekdays are for trail inspections (or private hikes). The newsletter has a habit of slipping – arriving late, getting skipped, or coming out slightly inconsistent depending on how tired I am.
So I automated it.
The foundation
This wouldn't work without something I've been building for years: a structured Airtable database of every experience we offer. Each record holds everything – the hike name, distance in kilometers, elevation gain, difficulty rating, duration, transport accessibility, description, booking URL, and a hero image.
When planning the monthly program, there's a procedure for linking specific dates and guides to each experience. By the time any given week arrives, the "Hikes this week" view in Airtable is already populated and ready to go.
The stack
- Airtable – the source of truth for all event data
- Make – the automation layer that orchestrates everything (might be replaced by n8n in the near future)
- ChatGPT (gpt-4o-mini) – generates the HTML email content and subject line
- An FTP server – hosts permanent, optimized copies of the event images
- Mailchimp – where the finished draft campaign lands
How it works, step by step
1. Airtable fetches this week's events
Make reads all records from the "Hikes this week" view. It triggers once per record, so if there are three hikes this weekend, three separate bundles of data flow through the system – one per event.
2. Images are downloaded, resized, and re-uploaded
This step exists because Airtable's attachment URLs expire after roughly 24 hours. They're signed, temporary links – which means if you embed them directly in an email, they'll be broken by the time anyone opens it.
For each event, Make downloads the hero image, resizes it to 600px wide to keep file size reasonable for email, and uploads it to a dedicated folder on the FTP server. The result is a clean, permanent URL that will still resolve days or weeks later.
3. Event data is assembled into a structured block
A Text Aggregator module collects all the fields for each event – name, date, guide, price, distance, elevation, difficulty, transport info, meeting point, booking URL, description, and the new permanent image URL – into one structured text document. Each event block is separated by a delimiter so the AI knows where one ends and the next begins.
4. ChatGPT generates the full HTML email body
The assembled text goes to gpt-4o-mini along with a detailed system prompt. The model is instructed to:
- Generate one complete HTML event block per hike, following a fixed table-based email template
- Alternate background colors between events
- Format the date correctly, convert kilometers to miles, convert duration from seconds to hours
- Write 2–3 vivid but grounded sentences expanding on each description
- Generate 5–7 specific highlights per hike (most of the time, highlights are already specified in the original description)
- Display the correct guide photo (mapped by name) with a circular crop (the images are already existing in the media library of Mailchimp)
- Apply the right transport note – public transport hikes get a 🚊 accessibility note; car-dependent hikes get a 🚗 carpooling facilitation note
- Vary the call-to-action button text between events
- Close with the static sections that appear every week: reviews, calendar link, private hike offer, and partner logos
The output is raw HTML – ready to be passed into the Mailchimp campaign body.
5. A second module writes the subject line
A separate ChatGPT module receives a brief summary of this week's events and generates a concise, engaging subject line. Keeping this as its own module means you can tune the subject line prompt independently without touching the main email generator.
6. Mailchimp creates the draft campaign
The final module creates a draft Mailchimp campaign with a structured title (in the format NEWSLETTER – YEAR – Month – Weekday), the AI-generated subject line, and the full HTML body. It lands in Mailchimp as a draft – nothing is sent automatically.
Human in the loop
I still open the draft, read through it, and make small corrections. I will also create a new subject line for A/B testing, and manually enable and fill in the social cards (for now). Then I set the send time manually and schedule it.
That review takes around five to ten minutes. The alternative was building the whole thing from scratch every week. Not bad.
A few things I learned along the way
Sometimes less is more. A capable but non-reasoning model like gpt-4o-mini is faster, cheaper, and produces better output here.
The image expiration issue would have silently broken everything. Airtable's URLs look like normal image links until you open an email 48 hours later and find broken images. The FTP re-upload step adds two modules but solves it completely.
The database is the actual foundation. Years of structured data in Airtable is what makes it possible for an AI to generate accurate, consistent newsletters without guessing. The automation is straightforward – the hard work was building the database in the first place.
