General Email
Email To Database
At its simplest, ThinkAutomation can be used to update a database from received emails. For this sample to work, create a table in your database called Emails, with the following columns:
| Column Name |
Type |
| Id |
varchar(250) |
| FromAddress |
varchar(500) |
| ToAddress |
varchar(500) |
| Subject |
varchar(2000) |
| Date |
date |
| Size |
integer |
| AttachmentNames |
varchar(1000) |
| BodyText |
text |
| BodyHtml |
text |
You create Extract Field actions to extract each field from the incoming message. On each Extracted Field click the Database Update tab and enter the Update Table Name to 'Emails'. When you assign a database table name to an extracted field, ThinkAutomation automatically creates the SQL commands to update that table.
Add an Update A Database action to update your database.
Sample: Email To Database
// Simple 'Email To Database' automation.
// Create a table called 'Emails'
// With columns: Id,FromAddress,ToAddress,Subject,Date,Size,AttachmentNames,BodyText,BodyHtml
Id=Extract FieldBuilt In Field%Msg_MessageId%(Database Emails.Id)
FromAddress=Extract FieldBuilt In Field%Msg_From%(Database Emails.FromAddress)
ToAddress=Extract FieldBuilt In Field%Msg_To%(Database Emails.ToAddress)
Subject=Extract FieldBuilt In Field%Msg_Subject%(Database Emails.Subject)
Date=Extract FieldBuilt In Field%Msg_Date%(Database Emails.Date)
Size=Extract FieldBuilt In Field%Msg_Size%(Database Emails.Size)
AttachmentNames=Extract FieldBuilt In Field%Msg_Attachments%(Database Emails.AttachmentNames)
BodyText=Extract FieldBuilt In Field%Msg_Body%(Database Emails.BodyText)
BodyHtml=Extract FieldBuilt In Field%Msg_Html%(Database Emails.BodyHtml)
// Save the email to the database
Update A DatabaseSQL ServerOnEmailBackup
Save Attachments
This sample shows how to automatically save all attachments on incoming messages to a local folder structure. Attachments will be saved to subfolders based on the from address, year and month.
The AttachmentRoot variable is set to the local root folder where we want to save attachments.
The AttachmentFolderName variable is set to the full path. The File Operation create folder action creates the folder if it does not exist.
Sample: Save Attachments
// Saves attachments to folders for each sender.
// Attachments saved to C:\Attachments\{fromaddress}\{year}\{month}\
// Also saves results to a log %Root%AttachmentsLog.log
If%Msg_AttachmentCount%Greater Than0Then
// Set the root folder here. Defaults to C:\Attachments
AttachmentRoot=C:\Attachments
AttachmentFolderName=%AttachmentRoot%\%Msg_From%\%Year%\%MonthName%
AttachmentFolderCreated=File OperationCreate Folder%AttachmentFolderName%\
If%AttachmentFolderCreated%Is Not BlankThen
// Save all attachments. Append a unique key to the filename if required to ensure the file name is unique.
Process Attachments*.*Save To%AttachmentFolderName%(Make Unique)
AttachmentSaveResult=%Msg_AttachmentListWithSizes% Saved To %AttachmentFolderName%
AttachmentSaveResult=Could Not Create Folder: %AttachmentFolderName% error was: %LastErrorText%
Read/Write Text File%Root%AttachmentsLog.log Write(Append)
// %AttachmentSaveResult%(Show Notification)(Log)
Save Attachments To A Database
Files and attachments can be saved to a SQL database by assigning a file path to a Blob column.
For this sample to work, create a table called Attachments, with the following columns:
| Column Name |
Type |
| FileName |
varchar(250) |
| FileData |
blob or varbinary(max) |
| FileType |
varchar(10) |
| FileSize |
int |
| SenderEmail |
varchar(250) |
We use a For..Each action to loop on Attachment. For each attachment the loop sets the FileName variable to the current attachment filename and the Location variable to the file path where ThinkAutomation saves the attachment during message processing.
The Extension variable is set to just the file extension using the Set action with the Extract File Extension operation. The Size variable is set to the file size using the File Operation action.
Save Attachments To A Database
// Automation Actions Follow
// For each attachment. If there are no attachments then the for each block will be skipped
For EachAttachment[Assign To: FileName, Location]
// Use the Set action to get the file extension from the filename
Extension=Extract File Extension(%FileName%)
// Use the File Operations action to get the file size
Size=File OperationGet File Size%Location%
// Save the attachment to the database
Update A Database Using Custom SQLSQL ServerOnTest
ReturnSaved %Msg_AttachmentCount% to database
The Update A Database Using Custom SQL then inserts a record into the Attachments table.
The Update A Database action is shown below:
The @FileData parameter type is set to Blob and the Value set to the %Location% variable. If the parameter type is Blob and its value is set to a file path then the actual file contents are read and assigned.
Process Order
This sample shows how to process an order email. The incoming email body is in the following format:
We have received an order for your product "Widget".
The credit card charge has been authorized.
We sent out the license key of the program to the user:
Order No. = 986
Program = 123456
Number of licenses = 1
Ref.No. = 56789
Reseller =
Promotion =
Net sales = USD 120.00
Net Discount = USD 0.00
collected VAT = USD 0.00
Shipping = USD 0.00
VAT on Shipping = USD 0.00
Our service fee = USD -8.95
VAT on service fee = USD 0.00
Total = USD 111.05
User data:
Salutation =
Title =
Last Name = Miller
First Name = Dan
Company = Ace Clothing
Street = 24a Stanley Street
ZIP = ST6 6BW
City = Stoke on Trent
FullCity =
Country = United Kingdom
State / Province = Staffordshire
Phone = 01782 822577
E-Mail = dan@mydomain.com
VAT ID =
Payment = Credit Card: Visa
Registration name = Dan Miller
Language = English
Extract Field actions are created to extract each item that we need. A database table/column name is assigned to each field allowing for an automatic Update A Database action.
The GeoIP Lookup action is used to check that the country for the customers email address is the same as the country specified on the order.
The Create Document action is used to create a PDF receipt. The Send Email action is used to send an email back to the customer with the PDF receipt as an attachment.
The Execute A Database Command action is used to call a stored procedure to check if the customer is new. The IsNew variable is set to True if the customer is new and an email is sent to the sales team to indicate a new customer.
Finally, if the Phone field is not blank then the Normalize Phone Number action is used to correctly format the phone number based on the customer's country. The Twilio Send SMS Message action is used to send an SMS message to the customer.
Sample: Process Order
// Extract data from incoming email
// We assign database table and column names to each extracted field that we want to use with the Update Database action to do an automatic database update.
Product=Extract FieldFrom%msg_body%Look For"Product"(Database Orders.Product)
OrderNo=Extract FieldFrom%msg_body%Look For"Order No."Then"="(Database Orders.OrderNo)
Program=Extract FieldFrom%msg_body%Look For"Program"Then"="(Database Orders.Program)
Qty=Extract FieldFrom%msg_body%Look For"Number of licenses"Then"="(Database Orders.Qty)
Reference=Extract FieldFrom%msg_body%Look For"Ref.No."Then"="(Database Orders.Reference)
Reseller=Extract FieldFrom%msg_body%Look For"Reseller"Then"="(Database Orders.Reseller)
Currency=Extract FieldFrom%msg_body%Look For"Net Sales"Then"="(Database Orders.Currency)
Value=Extract FieldFrom%msg_body%Look For""(Database Orders.Value)
Discount=Extract FieldFrom%msg_body%Look For"Discount"Then"= ..."(Database Orders.Discount)
LastName=Extract FieldFrom%msg_body%Look For"Last Name"Then"="(Database Orders.LastName)
FirstName=Extract FieldFrom%msg_body%Look For"First Name"Then"="(Database Orders.FirstName)
Company=Extract FieldFrom%msg_body%Look For"Company"Then"="(Database Orders.Company)
Street=Extract FieldFrom%msg_body%Look For"Street"Then"="(Database Orders.Street)
PostCode=Extract FieldFrom%msg_body%Look For"Zip"Then"="(Database Orders.PostCode)
City=Extract FieldFrom%msg_body%Look For"City"Then"="(Database Orders.City)
Country=Extract FieldFrom%msg_body%Look For"Country"Then"="(Database Orders.Country)
State=Extract FieldFrom%msg_body%Look For"State"Then"="(Database Orders.State)
Phone=Extract FieldFrom%msg_body%Look For"Phone"Then"="(Database Orders.Phone)
Email=Extract FieldFrom%msg_body%Look For"E-mail"Then"="(Database Orders.Email)
PaymentType=Extract FieldFrom%msg_body%Look For"Payment"Then"="(Database Orders.PaymentType)
CreditCard=Extract FieldFrom%msg_body%Look For"Credit Card:"(Database Orders.CreditCard)
RegName=Extract FieldFrom%msg_body%Look For"Registration Name"Then"="(Database Orders.RegName)
Dated=Extract FieldBuilt In Field%Msg_Date%(Database Orders.Dated)
// Check the email is valid
GeoIP Lookup%Email%Set%GeoIPCountry% = Country
If%GeoIPCountry%Does Not Contain%Country%Then
// Country mismatch. The geoip lookup of the email domain does not match the country specified on the order. Possible fraud.(Show Notification)(Log)
Send EmailToorderprocessing@mysite.com"Please Check Order: %OrderNo% - Country Mismatch"
If%Email%Is Not A Valid Email AddressThen
// The email address %Email% given is not valid(Log)
// A new order has been received - update database (& CSV file for backup)
Update A DatabaseSQL ServerOn%YourConnectionString%
Update A CSV File%Root%Orders.csv
// Create PDF receipt which we will send to the customer
PDFReceipt(Assign Saved Path)=Create DocumentReceiptSave To%Root%\Order_%Reference%.pdfAsPDF(Delete After)
Send EmailTo%Email%"Thank you for your order"
Send EmailToorderprocessing@mysite.com"New Order For: %Product%"
Send EmailTo%Email%"Follow up for order %OrderNo%"(Scheduled For 7 Days Time)
// Check if the customer is new
Execute A Database CommandSQL ServerOn%YourConnectionString%IsNewCustomer
If%IsNew%Equal ToTrueThen
// Let sales team know we have a new customer
Send EmailTosalesteam@mysite.com"New Customer: %Company%"
If%Phone%Is Not BlankThen
// Send text message to customer if phone number given
SMSNumber=Normalize Phone Number%Phone%(%Country%)(Make International)
Twilio Send SMS MessageTo%SMSNumber%From%MyTwilioNumber%"Hi %FirstName%. We have received your order for %Product%. We are now processing."