Bid List Automations - KW Smartsheet Utility

Bid List Automation System – Knowledge Base

Overview

The Bid List Automation system is a set of scheduled scripts and Smartsheet sheets used by the Estimating team to:

• Send Bid List emails to vendors automatically
• Control which vendors receive which Bid Lists
• Track email delivery success or failure
• Keep the vendor contact list synchronized from external systems

The system removes the need for manual email distribution and ensures consistent communication with vendors.

Components of the System

Main Script

bid_list_automation.py

This script is responsible for:

• Reading queued Bid List requests from Smartsheet
• Determining which vendors should receive the Bid List
• Downloading attached Bid List PDFs
• Sending emails via SMTP2GO
• Marking the request as sent

This script runs on a scheduled basis.

Auxiliary Script — Recipient Sync

smtp_recipient_to_ss.py

This script keeps the vendor contact list updated in Smartsheet by processing CSV exports.

Auxiliary Script — Email Status Poller

smtp_recipient_status_poller.py

This script checks SMTP2GO for delivery activity and updates vendor delivery status in Smartsheet.

Smartsheet Sheets Used

Automated Bid List (Main Driver)

This sheet is where Bid List email requests are created.

Users submit a form which populates this sheet.

Required fields include:

• Delivery Date
• Delivery Time
• Bid List Recipient Categories
• Email Body text (optional)
• Bid List PDF attachment

The automation script will send the email once the scheduled time is reached.

Bid List Recipients

This sheet controls:

• Which vendors are eligible to receive Bid Lists
• Whether the vendor is Active
• Which categories each vendor belongs to

It also displays the latest email delivery status returned by SMTP2GO.

Bid List Pipedrive Download (Recipient Source Sheet)

This sheet is populated automatically from CSV exports and serves as the source for vendor contact data.

Bid List Recipient Categories

This sheet defines valid categories vendors can belong to.

Examples:

• Default
• Charlotte
• Raleigh
• SD Narrative

These categories determine which vendors receive specific Bid Lists.

Operational Usage

Sending a Bid List (Estimator Workflow)

1. Estimator fills out the Bid List Request Form
2. Uploads the Bid List PDF
3. Selects recipient categories
4. Chooses delivery date and time

Once submitted:

• The row appears in the Automated Bid List sheet
• The automation script checks this sheet regularly
• When the scheduled time arrives:
  • Recipients are determined
  • Email is sent
  • The Date Sent column is populated

No further manual action is required.

Syncing Vendor Recipients from Contact List CSV

To update the vendor list:

1. A member of the Estimating Team exports the vendor contact list CSV
2. The file must be named similar to:

Contact List.csv

3. The file is placed into the monitored directory:

\\mccorvey.com\dfs\KIRLIN\Estimating\Bids\Bids 2025\Bid List\Contact List

The script will:

• Detect the newest Contact List CSV
• Delete all existing rows in the recipient source sheet
• Upload new Name + Email records into Smartsheet
• Move the processed CSV into an Archive folder

No manual Smartsheet editing is required.

This ensures the system always uses the most recent vendor data.

Managing Vendor Eligibility

Vendor eligibility is controlled in the Bid List Recipients sheet.

Key controls:

• Active column
  • Checked = vendor can receive emails
  • Unchecked = vendor will not receive emails
• Bid List Recipient Categories column
  • Determines which Bid Lists the vendor will receive

Multiple categories can be assigned.

Monitoring Email Delivery Status

The SMTP Status Poller script runs regularly and updates:

• SMTP2GO Response
• Latest Response Time
• Error Information (for bounced or failed emails)

This allows users to:

• Identify invalid vendor emails
• Confirm successful delivery
• Investigate failures

Important Notes

• Bid List rows without Delivery Date AND Delivery Time will not send
• Bid Lists must have at least one PDF attachment
• Vendors must be marked Active to receive emails
• Vendors must have matching Recipient Categories
• Only the newest Contact List CSV will be processed
• Older CSV files are automatically archived

Troubleshooting Tips

If a Bid List does not send, check:

• Delivery date/time has passed
• Recipient categories are selected
• Vendors are Active
• PDF attachment exists
• Automation script logs

If vendors are missing:

• Confirm Contact List CSV was placed in the correct directory
• Confirm script ran successfully
• Confirm vendor categories are assigned