Does your organization receive payments (donations) via bank transfer (wire-transfer)? Do you want to see the transactions inside Salesforce?
Last time we looked at how to integrate salesforce with the popular Darujme.sk portal. Today we will look at another Salesforce feature that can be very useful for (not only) non profit organizations. We will stay with the topic of donations and we will explore how we can import donations made via wire-transfer (bank deposit, sepa payment) into Salesforce.
There are two ways how we can programmatically interact with a bank account.
In Europe the Payment_Services_Directive requires all banks to provide API acesss.
However getting access via api is a major undertaking. Banks usually don’t provide a simple “I need readonly access to my account” access. Instead a company needs to have a license issued for universal bank access. Then get a qualified certificate. After all of this the company can ask permissions to access a particular bank account. With another round of checks.
Just process the bank notification emails! Yay.
For this blog we shall go the easy route #2
What is Salesforce Email to Apex? It is an extremely useful feature, emails sent to an address can be automatically processed by an apex class.
In a nutshell,
First of all our apex class has to implement a specific interface. Make sure we return the right result.
public with sharing class InukoBankEmail implements Messaging.InboundEmailHandler {
public Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope) {
Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
result.success = true;
// TODO: actually parse the email and create objects
return result;
}
}
Next we will configure salesforce to generate a unique email address for our apex class.
We will do this in the Salesforce Setup > Custom Code > Email Services section. (Or just search for Email Services :).
.
Click on New Email Service then choose the Apex we've made in the previous step.
Click on New Email Address and copy the email address Salesforce generated for us.
Make sure you lock down the email sender. Since the incomming email will trigger code with database access, we must be careful about who can send us email.
You can also find more information in the great Salesforce documentation.
That’s it, let’s dance, erm I mean let’s parse.
Each bank’s notification email will for sure have a different structure. Thus this code is not trying to be universal, rather it should give you a toolkit that you can use to parse your bank’s format.
In the end we will need a different method for every bank. We can switch between them based on the envelope.fromAddress
value. It gives us the sender - the bank who sent the notification.
Emails used to be plain-text. These days the cool kids like richly formatted emails, thus html emails are very common.
A bank email can therefore have these parts:
In the next sections we will look at some APEX code. We shall be using the InuTransaction object we defined in the previous blog post
Plain text parsing is usually very straightforward. The most common format is one data value per text line. Where each line has two parts the field name and value.
Payer Account name: Acme corp
Payer IBAN: FC48 0000 4839 2995 9592
Amount: 34,20 Euro
Date: 24.9.2024
Information text: invoice-39382
To parse an email like this, we shall split the text info lines, then each line by the :
separator.
We then look for specific field-names and parse the field-value.
For example if we know the field-name is ‘Date’ we will parse the text into a Date object. For the iban field, we will remove all whitespace even inside the iban string. As a bare minimum we might want to trim trailing whitespace from values.
public static void parsePlainTextEmail(String body, String rawBody) {
Contact defaultContact = getDefaultContact();
InuTransaction__c t = new InuTransaction__c(contact__c = defaultContact.id);
String[] parts = body.split('\\n');
for (String p : parts) {
String[] line = p.split(':');
if (line.size()<2)
continue;
String name = line[0].trim(); // remove starting and trailing whitespace
String value = line[1].trim();
if (name == 'Date') {
String[] dt_parts = value.split('\\.');
t.Transaction_Date__c = Date.newInstance(integer.valueOf(dt_parts[2]),integer.valueOf(dt_parts[1]),integer.valueOf(dt_parts[0]));
} else if (name == 'Amount') {
// remove currency, any whitespace, convert decimal comma to decimal point.
String num = value.replaceAll('EUR', '').replaceAll('\\s+', '').replaceAll(',','.');
t.Transaction_Amount__c = Decimal.valueOf(num);
} else if (name == 'Payer IBAN') {
String iban = value.replaceAll('\\s+', ''); // remove any whitespace within the IBAN
t.Payer_iban__c = iban;
String contactId = findContactByIban(iban);
if (contactId != null) {
t.contact__c = contactId;
}
String accountId = findAccountByIban(iban);
if (accountId != null) {
t.account__c = accountId;
t.Donor_kind__c = 'company';
}
} else if (name == 'Information text') {
t.Info_text__c = value;
} else if (name == 'Payer Account name') {
t.Bank_Account_Name__c = value;
} else if (name == 'Variabilný symbol') {
t.Variable_number__c = value;
} else if (name == 'Špecifický symbol') {
t.Specific_number__c = value;
}
t.Transaction_Status__c = 'successful';
t.Zdroj_daru__c = 'Účet';
}
// Parse failed: keep the whole email, so we can debug.
if(t.Payer_IBAN__c == null || t.Transaction_Amount__c == null || t.Transaction_Date__c == null) {
// save the email so we can investigate.
t.bank_email_body__c = rawBody;
}
insert t;
return true;
}
If your bank send a nicely formatted xhtml emails (test it, unfortunately we stumbled on false promises before), we can use the apex xml parser. We load the html body of the email, and then look for specific nodes.
In our experience the information fields will be put inside a table. Our approach is to look for all TR nodes and the TD elements within. We expect values in TD to be primitive or only lightly formatted (spans with colors that kind of thing).
We will recursively look at each xml node. If it is a TR (table row) node, the for each child we will get the text and add it to the parsed list.
The getNodeText helper will deal with the case where a TD contains more than text. If it has children we will extract and concatenate the
public class Row {
public List<string> cells;
}
private static String convertXmlToText(String xmlText) {
Dom.Document doc = new Dom.Document();
doc.load(xmlText);
Dom.XMLNode root = doc.getRootElement();
List<Row> rows = new List<Row>();
parseXml(root, rows, '');
// We convert the table rows into lines and cells into label:value pairs.
// You can also just use the values directly of course.
String result = '';
for(Row r : rows) {
if(r.cells.size() >= 2) {
result += r.cells[0] +':' + r.cells[1] +'\n';
}
}
return result;
}
private static void parseXml(Dom.XMLNode node, List<Row> rows, String pad) {
List<Dom.XMLNode> children = node.getChildElements();
if(children.isEmpty()){
return;
}
Boolean isRow = node.getName() == 'tr';
Row r = null;
if (isRow) {
r = new Row();
r.cells = new List<string>();
rows.add(r);
}
for(Dom.XMLNode child : children){
String nodeName = child.getName();
if (nodeName == 'td') {
String tdText = getXmlText(child);
r.cells.add(tdText);
} else {
parseXml(child, rows, pad+' ');
}
}
}
private static String getXmlText(Dom.XMLNode node) {
List<Dom.XMLNode> children = node.getChildElements();
if(children.isEmpty()){
return node.getText();
}
String r = '';
for(Dom.XMLNode child : children){
r += getXmlText(child);
}
return r;
}
If your bank only sends you HTML notification emails and they are not valid XML, then this section is for you.
To parse HTML we need an HTML parser. Duh. However there is no HTML parses in APEX:( .
If the format of your bank email is to strange, we recommend a Azure function or some other service where you can run Python
and use the
BeautifulSoup library for HTML parsing.
But, chances are high, the email can be parsed with Regex. Generic HTML parsing with Regex is a big no-no.
Thankfully, we are not parsing just any HTML, instead we are looking for specific things inside the HTML.
Since our bank email has all the information we need inside a table, we will look for tr
and td
elements and extract the text within them. Keep in mind that your bank email might look different, therefore you might be looking for some other elements.
The code below, will take all td
elements and produce a list of label:value pairs. With the assumption, that there are exactly two td
elements.
If the text within the td
looks like another element (starts with <
), we assume these are span
elements and we will take the text within them and simply concatenate.
private static String convertHtmlToText(String htmlText)
Pattern tdPattern = Pattern.compile('<td[^>]*?>(.*?)</td>');
Matcher tdMatcher = tdPattern.matcher(htmlText);
Pattern spanPattern = Pattern.compile('<span[^>]*?>(.*?)</span>');
String sb = '';
Boolean isLabelCell = true;
while(tdMatcher.find()) {
Integer groupCount = tdMatcher.groupCount() + 1; // 0 is the full match
for(Integer index = 1; index < groupCount; index++) {
String z = tdMatcher.group(index);
if (z.subString(0, 1) == '<') { // text is <span ... parse again
String sp = '';
Matcher m2 = spanPattern.matcher(z);
while (m2.find()) {
String spanText = m2.group(1);
sp += spanText.replaceAll(' ','');
}
z = sp;
}
sb += z;
if (!isLabelCell) { // == isValueCell
sb += '\n';
}
isLabelCell = !isLabelCell; // first td is label, second is value. Alternate
}
}
return sb;
Whether you are a non-profit organization or a for-profit one, integrating payment notifications is very useful. It closes the loop of workflows, providing a way to clearly mark an activity as finished. It also enables automation, allowing us to first segment our donors/customers based on the amount and frequency of payments. And then launch subsequent activities based on that.
Email Services or Email to Apex, is a great way to implement such a feature. We get instant feedback. And there is no need to pay, setup and secure an additional service running elsewhere.
Happy hacking!