Writes a personalised welcome email from each new client intake row, asks you to approve it, then sends it or drafts it.
Uses
Claude
Gmail
Google Sheets
Welcome new clients from their intake form
Run every hour.Run every hour.
Set intakeSheetUrl to blank.Set intakeSheetUrl to blank.Set intakeTabName to blank.Set intakeTabName to blank.Set welcomedTabName to blank.Set welcomedTabName to blank.Set firmName to blank.Set firmName to blank.Set firmContext to blank.Set firmContext to blank.Set maxClientsPerRun to 10.Set maxClientsPerRun to 10.Google SheetsRead column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedRead.Read column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedRead.Set welcomedKeys to an empty list.Set welcomedKeys to an empty list.
For each loggedAddress in welcomedRead.values:For each loggedAddress in welcomedRead.values:
If a step below fails, save the error as loggedErrorIf a step below fails, save the error as loggedErrorTry
Add loggedAddress in lowercase to the end of welcomedKeys and save it as welcomedKeys.Add loggedAddress in lowercase to the end of welcomedKeys and save it as welcomedKeys.On error
Show in the output: Ignored an entry in the '@welcomedTabName' tab that could not be read as an email address.Show in the output: Ignored an entry in the '@welcomedTabName' tab that could not be read as an email address.Google SheetsRead the rows of intakeSheetUrl. Save it as intake.Read the rows of intakeSheetUrl. Save it as intake.
If intake.headers is emptyIf intake.headers is emptyYes
Stop and report a problem: The '@intakeTabName' tab is empty. Its first row must be a header row with columns named Name, Email and Notes.Stop and report a problem: The '@intakeTabName' tab is empty. Its first row must be a header row with columns named Name, Email and Notes.Else if @intake.headers doesn't contain "Name" or @intake.h…
Join intake.headers with , and save it as foundHeaders.Join intake.headers with , and save it as foundHeaders.Stop and report a problem: The '@intakeTabName' tab needs columns named exactly Name, Email and Notes in its first row, but it has: foundHeaders. Rename those header cells (for a Google Form, rename the questions) to Name, Email and Notes, then run it again.Stop and report a problem: The '@intakeTabName' tab needs columns named exactly Name, Email and Notes in its first row, but it has: foundHeaders. Rename those header cells (for a Google Form, rename the questions) to Name, Email and Notes, then run it again.Otherwise
No steps
Set welcomedThisRun to 0.Set welcomedThisRun to 0.Set stoppedAtLimit to false.Set stoppedAtLimit to false.Set failures to an empty list.Set failures to an empty list.For each row in intake.rows:For each row in intake.rows:If welcomedThisRun is greater than or equal to maxClientsPerRunIf welcomedThisRun is greater than or equal to maxClientsPerRunYes
Change stoppedAtLimit to true.Change stoppedAtLimit to true.Stop repeating.Stop repeating.No
No steps
Set rowLabel to row row.row_number as text.Set rowLabel to row row.row_number as text.Set clientName to blank.Set clientName to blank.Set clientEmail to blank.Set clientEmail to blank.Set clientNotes to blank.Set clientNotes to blank.Set rowIsComplete to false.Set rowIsComplete to false.If a step below fails, save the error as missingColumnIf a step below fails, save the error as missingColumnTry
Trim row.Name and save it as clientName.Trim row.Name and save it as clientName.Trim row.Email and save it as clientEmail.Trim row.Email and save it as clientEmail.Trim row.Notes and save it as clientNotes.Trim row.Notes and save it as clientNotes.Change rowIsComplete to the length of clientName is greater than 0 and the length of clientEmail is greater than 0 and the length of clientNotes is greater than 0.Change rowIsComplete to the length of clientName is greater than 0 and the length of clientEmail is greater than 0 and the length of clientNotes is greater than 0.On error
Show in the output: Skipping rowLabel - the intake tab needs a Name, an Email and a Notes column.Show in the output: Skipping rowLabel - the intake tab needs a Name, an Email and a Notes column.Skip to the next item.Skip to the next item.If rowIsComplete is noIf rowIsComplete is noYes
Show in the output: Skipping rowLabel - Name, Email and Notes must all be filled in before a welcome email can be written.Show in the output: Skipping rowLabel - Name, Email and Notes must all be filled in before a welcome email can be written.Skip to the next item.Skip to the next item.No
No steps
If not clientEmail is a valid email addressIf not clientEmail is a valid email addressYes
Show in the output: Skipping rowLabel - '@clientEmail' is not a valid email address.Show in the output: Skipping rowLabel - '@clientEmail' is not a valid email address.Skip to the next item.Skip to the next item.No
No steps
Make clientEmail lowercase and save it as emailKey.Make clientEmail lowercase and save it as emailKey.If welcomedKeys contains emailKeyIf welcomedKeys contains emailKeyYes
Show in the output: clientEmail has already been welcomed - leaving rowLabel alone.Show in the output: clientEmail has already been welcomed - leaving rowLabel alone.Skip to the next item.Skip to the next item.No
No steps
If a step below fails, save the error as welcomeErrorIf a step below fails, save the error as welcomeErrorTry
Set prompt to You are writing the body of a welcome email to a brand new client of a professional services firm. Write only the body of the email: no subject line, no placeholder brackets such as [Name], and nothing you cannot support from the details below. Greet the client by name, refer to what they asked for in their own terms, say what happens next, and sign off from the firm. Keep it under 200 words and do not invent dates, prices, or people.Set prompt to You are writing the body of a welcome email to a brand new client of a professional services firm. Write only the body of the email: no subject line, no placeholder brackets such as [Name], and nothing you cannot support from the details below. Greet the client by name, refer to what they asked for in their own terms, say what happens next, and sign off from the firm. Keep it under 200 words and do not invent dates, prices, or people.
About the firm:
firmName
firmContext
The new client:
Name: clientName
What they told us:
clientNotes.ClaudeAsk Claude: prompt. Save it as written.Ask Claude: prompt. Save it as written.
Maximum response length: 700Trim written.response_text and save it as welcomeBody.Trim written.response_text and save it as welcomeBody.If welcomeBody isn't emptyIf welcomeBody isn't emptyYes
Set subject to Welcome to firmName.Set subject to Welcome to firmName.Google SheetsRead column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedNow.Read column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedNow.If welcomedNow.values contains emailKeyIf welcomedNow.values contains emailKeyYes
Show in the output: clientEmail is already waiting on another run's approval - leaving rowLabel to it.Show in the output: clientEmail is already waiting on another run's approval - leaving rowLabel to it.Skip to the next item.Skip to the next item.No
No steps
Google SheetsAdd a row to welcomedTabName in intakeSheetUrl.Add a row to welcomedTabName in intakeSheetUrl.Add emailKey to the end of welcomedKeys and save it as welcomedKeys.Add emailKey to the end of welcomedKeys and save it as welcomedKeys.Add 1 to welcomedThisRun.Add 1 to welcomedThisRun.
Request approval: Send this welcome email to clientName <clientEmail>? Save it as review.Request approval: Send this welcome email to clientName <clientEmail>? Save it as review.Approve and it goes to the client now, under your name. Reject it, or leave it, and it is saved as a Gmail draft for you to edit.
Subject: subject
welcomeBodySet outcome to blank.Set outcome to blank.If review.approved is yesIf review.approved is yesYes
If a step below fails, save the error as sendErrorIf a step below fails, save the error as sendErrorTry
GmailSend an email to clientEmail.Send an email to clientEmail.
Subject: subjectwelcomeBodyChange outcome to Sent.Change outcome to Sent.On error
Change outcome to Send failed.Change outcome to Send failed.If a step below fails, save the error as draftAfterSendErrorIf a step below fails, save the error as draftAfterSendErrorTry
GmailDraft an email to clientEmail.Draft an email to clientEmail.
Subject: subjectwelcomeBodyChange outcome to Send failed, saved as a draft.Change outcome to Send failed, saved as a draft.On error
No steps
Add clientName <clientEmail>: you approved the welcome email but it could not be sent (sendError.message). Recorded as '@outcome' on the '@welcomedTabName' tab. to the end of failures and save it as failures.Add clientName <clientEmail>: you approved the welcome email but it could not be sent (sendError.message). Recorded as '@outcome' on the '@welcomedTabName' tab. to the end of failures and save it as failures.No
If a step below fails, save the error as draftErrorIf a step below fails, save the error as draftErrorTry
GmailDraft an email to clientEmail.Draft an email to clientEmail.
Subject: subjectwelcomeBodyChange outcome to Drafted.Change outcome to Drafted.On error
Change outcome to Draft failed.Change outcome to Draft failed.Add clientName <clientEmail>: the welcome email could not be saved as a draft (draftError.message). Recorded as 'Draft failed' on the '@welcomedTabName' tab. to the end of failures and save it as failures.Add clientName <clientEmail>: the welcome email could not be saved as a draft (draftError.message). Recorded as 'Draft failed' on the '@welcomedTabName' tab. to the end of failures and save it as failures.If a step below fails, save the error as recordErrorIf a step below fails, save the error as recordErrorTry
Google SheetsAdd a row to welcomedTabName in intakeSheetUrl.Add a row to welcomedTabName in intakeSheetUrl.On error
Add clientName <clientEmail>: outcome, but that could not be written to the '@welcomedTabName' tab (recordError.message). to the end of failures and save it as failures.Add clientName <clientEmail>: outcome, but that could not be written to the '@welcomedTabName' tab (recordError.message). to the end of failures and save it as failures.No
Show in the output: No welcome email was written for clientName, so rowLabel is left for the next run.Show in the output: No welcome email was written for clientName, so rowLabel is left for the next run.On error
Add Could not welcome clientEmail: welcomeError.message. If they show as 'Awaiting approval' on the '@welcomedTabName' tab, delete that row to have them offered again. to the end of failures and save it as failures.Add Could not welcome clientEmail: welcomeError.message. If they show as 'Awaiting approval' on the '@welcomedTabName' tab, delete that row to have them offered again. to the end of failures and save it as failures.Set limitNote to blank.Set limitNote to blank.If stoppedAtLimit is yesIf stoppedAtLimit is yesYes
Build the text This run asked about @maxClientsPerRun clients, the most 'maxClientsPerRun' allows; anyone else is offered on the next run. and save it as limitNote.Build the text This run asked about @maxClientsPerRun clients, the most 'maxClientsPerRun' allows; anyone else is offered on the next run. and save it as limitNote.No
No steps
If failures isn't emptyIf failures isn't emptyYes
Join failures with a new line and save it as failureList.Join failures with a new line and save it as failureList.Stop and report a problem: Some welcome emails did not go out as planned:Stop and report a problem: Some welcome emails did not go out as planned:
failureList@limitNote.No
No steps
Show in the output: Welcome emails handled this run: welcomedThisRun@limitNote.Show in the output: Welcome emails handled this run: welcomedThisRun@limitNote.