Do you need to send thousands of personalized emails to your clients or customers – but you don't want to share your contacts with a third-party Saas service – like MailChimp or HubSpot, or… maybe you don't want to spend an arm-and-a-leg? We created a spreadsheet template with a Python script to send out customized emails automatically from your personal computer. Unlike other email services online, this tool is free of charge and runs directly on your computer -- ensuring your email list, content, attachment, and credentials remain private and secure. Plus, you can choose from various email styles and options to fit your message to each recipient.
In this article, you'll learn how to:
With our template, you get a compressed file with the spreadsheet, the Python script, and auxiliary files. Once you unzip or extract our template, make sure to keep all the files in the extracted folder. Remember, that the spreadsheet template is crucial for the Python script to work correctly. Make sure not to change any column names, fields, or the structure in general. Only edit the fields in blue.
Our spreadsheet template comes with a tab that contains all the general settings and configurations you'll need to send emails. Under the 'Fields' tab, set the connection type, whether or not to include an attachment, the email row that the sender should start from, and the option to add a signature at the end of all your emails.
The template has two connection options. One is SMTP, suitable for email servers like Hotmail, Outlook, and Yahoo, and the other is a Gmail API connection specifically for Google users – either Gmail or Google Worksuite. Select the connection type via the dropdown menu. For SMTP connections, you need to provide your email provider's server and port details. This information can be found by searching online – simply search online "[your email provider's name] SMTP server and port settings" – for example – "Yahoo SMTP server and port settings."
When using SMTP, you'll also need to input your email credentials into the template, such as your email address and password. Rest assured, these details remain entirely local on your computer and aren't shared with anyone. If you want added privacy, you can even hide these fields within the template. That said, once the spreadsheet has your credentials, do not share the spreadsheet with others.
On the other hand, if you opt for the Google API connection, no extra SMTP details are required. The Google API manages everything, establishing the connection automatically when the Python script gets executed. The only requirement is to sign in to your Gmail or Google Worksuite account as usual. Login details aren't stored or saved, so you'll need to log in each time you use the script. This ensures that your information remains private and secure.
Other configurable options include adding an attachment and signature to your outgoing emails. To enable attachments, select "Yes" from the corresponding dropdown. When the script is executed, you'll be prompted to pick the one file that will be attached to your emails. Likewise, to enable the signature, choose "Yes" and design your signature as desired. A formatting preview is available on the side to give you a glimpse of how your signature will appear in emails.
Lastly, you can select the row from which your emails should start sending. For instance, if there are 100 email addresses in your list, but you only wish to send the last 50, it's possible to adjust the starting point. This feature is especially handy if you encounter any issues while the emails are being sent and need to restart from the middle of your list to avoid sending duplicates.
Our template allows you to send personalized emails to all your contacts by merging custom fields into the email you write. Personalizing emails is important because it ensures your emails land in the inbox of your recipients. Additionally, it allows you to pass custom URLs, dates, facts, and figures to each of your contacts separately.
Instead of creating separate emails for each recipient, our template will merge the custom fields you have defined for contact into your email body and subject – by replacing [Column1] through [Column10]. The Python script will auto-replace these variables with the actual data as the emails are sent. This eliminates the need to design individualized emails for everyone. All you need to do is get creative with these fields.
Within this template, up to six email content options can be created. Five of these tabs are standard emails, while the sixth can be customized in an HTML-based layout. But don't worry, you don't need any HTML knowledge! Let's start with the standard email tabs. The layout of these emails is not customizable. However, you can still edit the email content you want to send.
In the email subject and body fields, enter your message. Remember, when you enter your message in the subject line or the email body – you can use [Column1] through [Column10] to merge the email content with the custom fields of your recipients.
For emails that need a more personalized look, the 'HTML email' tab is ideal. On this tab, users can adjust text styles, add links, and select heading types. These customizations can be chosen using the drop-down menus next to each.
After each is selected, the email will automatically update, creating a well-organized appearance. And, remember, any settings applied in the 'Fields' tab are carried over. So if signatures or attachments are enabled, they are incorporated into every email.
Before you can run our Python script, you need to install Python on your computer. Python is a simple programming language that runs on Windows, Mac, and Linux computers. Without Python, our script won't run, and your emails won't be sent – even with the correct template setup. It's worth noting that our Python-based solution runs locally on your device. This means it doesn't rely on external servers to store or process your data, ensuring your information remains private and secure. Also, the script is transparently shared, allowing anyone to inspect its code. Upon reviewing the source code of our Python script you'll be able to see that your data remains private and is only shared with your email provider.
To set up Python, follow these steps:
Note: Windows users should check the "Add Python to PATH" option during installation. If you miss this step, uninstall Python and reinstall it ensuring the path-option is selected. This step is vital for future script execution using the Windows "Command Prompt".
Python libraries improve a script's capabilities, enabling functionalities such as Gmail API integration or Excel file reading. All essential libraries for this tool are detailed in the provided "requirements.txt" file, to facilitate the download. Without them, the script won't work. You only have to set this up once.
Extract or unzip our template into a preferred folder, in both Windows and Mac our template will be extracted to a folder called "email_sender". Now we're going to explain how to install the necessary libraries and run the email sender on Windows, and later we will explain the same for Macs.
Below is a step-by-step guide to installing these Python libraries:
As the emails are being sent, you'll be able to see all the logs and details in the Command Prompt. Going forward you don't need to re-install the libraries again and again. To start sending emails again, go back to your folder via the "Command Prompt" and type the last "python3" command.
And again like the Windows instructions, you don't need to re-install the libraries again and again. To start sending emails, you just need to go back to your folder via the "Terminal" and type the last "python3" command.
The Python script works in a direct manner. It first checks the Excel file to get all the configuration details that you entered in the Fields tab. If you've enabled file attachments, for example, it prompts you to select which file you want to attach to all your emails.
Using the SMTP connection option, the script will create a connection with your specified email provider. If you're using the Gmail API option, the script will open a web page for you to log into your Gmail – or Google Worksuite – account.
We've designed this API feature specifically for this task. To function, make sure you give Python permission to use your login data. As Python highlights, the only reason for this access is to send emails. Your login info and any other details will not be shared with us at You Exec or stored by Python. Once you grant all necessary permissions, you'll get a confirmation message indicating access has been authorized.
Once credentials are confirmed, the script initiates the email-sending process. For each sent email, the Command Prompt, or Terminal, will print out a confirmation message showing the email type and its recipient. This continues until all emails have been sent. At the end, a summary report provides the total number of emails sent and the time it took. If an error occurs when you're sending the emails, the script will notify you, and will indicate the last email that was sent. This allows you to resume from where you stopped the next time you start sending emails. This can be done by changing the "Start sending emails from row" field in the Excel file.
The script is also designed with protections. It can recognize situations that will keep the script from running smoothly, for example, when you're not connected to the internet, when the template hasn't been filled out correctly, or if there's been a change to the structure of the original files. Lastly, for those using Google Sheets, the script operates similarly. Just download the Excel version by selecting "File > Download > Microsoft Excel" and place that file in the same folder as the other documents. You can always overwrite this with the most recent version as needed.
We hope this tool helps to optimize your email-sending process. We'd love to hear about your experiences or if you have any questions. As you use this template, if you have any feature requests – do share that feedback with us so we can keep improving this email sender. Thank you so much for watching.
