Document Workflow in MOSS 2007: How to extract an email address from an email sent to your Document Library for Workflow

  • Article

In my last posting, Document Workflow in MOSS 2007: How to enable the portal to receive external emails found here, I showed you how to enable a document library to receive external/internal emails with attachments. 

 

So, if you wanted to get the email address from the person who sent the email to the document library, in order to send them a notification (response email) automatically (using workflow, which I will describe in a later posting), you will notice an odd thing about SharePoint.  The "odd thing" is that the email address, isn't really an email address.  It is actually a html "SendTo:..." command (see the picture below).  This means that whenever you try to send an email to the address in a workflow, you will find that it NEVER WORKS! 

How can you see this for yourself?  Just hover your mouse over the email address in the document library and then notice down at the bottom left of the browser.  You will notice that the candidate1@OutsideOfCompany.com email address resolves to "mailto:candidate1@OutsideOfCompany.com."  Again look at the bottom left of the browser screen.  SharePoint converts any text that looks like an email into a "mail to..." link by default and as far as I know, there is NO WAY around this, other than tricking SharePoint by splitting the email address.

This is how you overcome this problem:

Although we could overcome this by doing something in Visual Studio, assuming that you are a developer.  Another option is using the Calculated Fields within the document Library. 

What we need to do is to split the email address into multiple fields and then later in a workflow we can recombine the pieces.  WARNING, this is going to seem complicated, heck it is complicated, but if you follow the steps exactly, you will end up with two columns which separate the email address into the part before the @ symbol and the part after the@ symbol, which you can reconnect in your workflow.  Just cut and Past the formulas into your document library.

Here are the steps:

1. Figure out where the Email actually starts.  The text after the html code within the "mailto..." code.

Create a calculated column in the SharePoint Document Library.  We'll call it "Email Calculation Variable - Email Start Location" and then add the string calculation, like this: The formula is: =FIND("<",[E-Mail From])+1

 

2. Next we need another calculated column in the SharePoint Document Library to find where the @ symbol is located, which we'll call "Email Calculation Variable - At Symbol Location".  The formula is: =FIND("@",[E-Mail From])

3. Now that we found out where the html code ends and the @ symbol starts, we need to extract the email address in between the two, which we'll call "Email Calculation Variable - Email Domain Location Second Part," which is the text after the @ symbol in your email address.  The formula is: =LEFT(RIGHT([E-Mail From],5),4)

4.  Now we will use the the previously created field to find the last part of the email before we join them together.  The formula is: =FIND([Email Calculation Variable - Domain Second Part],[E-Mail From])

 

Now that we have the parts all broken out, we can recombine them into two simple Columns: The part of the email address before the @ symbol and the part of the email address from the @ symbol on (including the @ symbol).

Step 1: I am not going to try to explain all of what the calculation does, because this blog enter is long enough.  The column will be called: "WFV Email Address Part before @ Symbol."

 So here's the formula that you can cut and paste: =MID([E-Mail From],[Email Calculation Variable - Email Start Location],[Email Calculation Variable - At Symbol Location]-[Email Calculation Variable - Email Start Location])

Step 2: Here's the second column and it will be called: "WFV Email Address Part from the @ Symbol on" and here's the formula: =MID([E-Mail From],[Email Calculation Variable - At Symbol Location],[Email Calculation Variable - Email Domain Location]-[Email Calculation Variable - At Symbol Location])&[Email Calculation Variable - Domain Second Part]

 

Now that you have the two fields for the part of the email before the @ symbol and the part of the email from the @ symbol on, you will be able to reconnect or concatenate the two pieces inside of your workflow, using the String Builder Action and you can then send an email to the address.

Unfortunately, I cannot answer the obvious questions of: Why is it so hard.. or Why doesn't SharePoint leave the field as a text... Hopefully, this work around will be useful to you, especially when you can just create the columns that I've shown above and just cut-and-paste the formulas. :)

 

~ Robert Shelton