SharePoint List Attachment URL in Email using SharePoint Designer Workflow and Rest API

SharePoint, since it’s introduction from MOSS2007 ( I started with MOSS2007 🙂 ) the SharePoint list has been a very handy tool for all the users to record data and information and blend with automated features provided. Attachment is an extra out of the box capability for every items (list or library) that lets you add some related documents or artifacts to the data information.  Whether a document with the draft proposal in word or some financial statement in excel or a PowerPoint presentation or any image files or any non-Microsoft files. SharePoint lists take them all as long as they are allowed by your farm administrator.

At my work i often get one question that is  if we can get the links to the attachments of a list item without clicking the item itself?  When you can send all the item values in the workflow why can’t the list attachment?. Every time my clients have to click on the item and then open it and then click on the attachments. oh!!! that’s a bit annoying.The attachment field provided by Microsoft when read from workflow provides two values true(if there is an attachment) and false (if there is no attachment).

In this article i will deescribe a solution that i have used at my work to accomplish this.

Note:

  1. This requires SharePoint Designer 2013 or late
  2. Works with SharePoint 2013 or Office 365 ( I am using office 365)

First let’s create a simple custom list in our SharePoint.

  • Create a custom list, give it a name EmailAttachments( feel free to pick your name)
Create New List
Create New List
  • I created a custom list named EmailAttachments as show in image below.
New Custom list named EmailAttachment is created
New Custom list named EmailAttachment
  • Now let’s add couple of items with attachments to understand how the attachments stored inside a list.
New item with attachments added
New item with three attachments added
  • Now to understand how are the attachments stored in the list put your cursor on one of the attachments and read the URL at the bottom of the page.
FirstDoc Attachment URL
FirstDoc Attachment URL
  • let’s look at the attachment URL we have as displayed in the above image.
  • https://theinventor.sharepoint.com/sites/sharepoint/Lists/EmailAttachments/Attachments/1/onepdf.pdf
    • Let’s break the URL
      • Current Site Name (https://theinventor.sharepoint.com/sites/sharepoint)
      • Lists (/Lists)
      • ListName(/EmailAttachments)
      • Attachments (/Attachments)
      • Item ID(/1)
      • FileName(/onepdf.pdf)

So there is a structral storage that stores all the attachments for each list items in SharePoint.

Each List has a folder named attachment and inside attachment there is a sub-folder for each item named with it’s id which stores all the attachments of that particular item.

  • So the target is to be able to construct the URL (https://theinventor.sharepoint.com/sites/sharepoint/Lists/EmailAttachments/Attachments/1/onepdf.pdf) using SharePoint designer workflow  for the attachments for each tiem.
  • SharePoint provides apis through Rest web services which has lot of  useful functions that let users to access the list information from SharePoint very easily.
  • For our scenario we will use the _api/lists/getByTitle(‘ListName’)/items(itemId)
  • Lets browse the SharePoint Rest api with our current lists and item and see what it has to  offer.
    • Paste following URL in the browser and let’s examine the content.
      • https://theinventor.sharepoint.com/sites/sharepoint/_api/lists/getByTitle(‘EmailAttachments’)/items(1)$select=AttachmentFiles,Title&$expand=AttachmentFiles
      • So full rest api after the Site URL is “/_api/lists/getByTitle(‘ListName’)/items(itemid)$select=AttachmentFilesTitle&$expand=AttachmentFiles “
      • Here is a part of the content received from the Rest Api call made in the browser. I am picking one full item result that starts from <entry> and ends with </entry>
        • <entry><id>https://theinventor.sharepoint.com/sites/sharepoint/_api/Web/Lists(guid’fafa1425-29a4-404d-ba2b-9b3452eec218′)/Items(1)/AttachmentFiles(‘FirstDoc.docx’)<updated>2017-09-18T14:50:59Z</updated><author><name /></author><content type=”application/xml”><m:properties><d:FileName>FirstDoc.docx</d:FileName><d:FileNameAsPath m:type=”SP.ResourcePath”>FirstDoc.docx<d:ServerRelativePath m:type=”SP.ResourcePath”>/sites/sharepoint/Lists/EmailAttachments/Attachments/1/FirstDoc.docx</d:<d:ServerRelativeUrl>/sites/sharepoint/Lists/EmailAttachments/Attachments/1/FirstDoc.docx</d:ServerRelativeUrl></m:properties></content></entry>
        • Out of all the nodes in the result we will focus on the nodes that has information related to attachments.
        • In the result we have two nodes one is FileName (filename of the attachment) and other is ServerRelativeURL( the url of the attachment except the web app URL)
        • This is the xml returned from the browser but later in the article we will examine the actual JSON resulted from the web service call.
  • Now since we know what we need let’s start creating the Workflow and bring this magic to real work. 🙂
  • First create a SharePoint Workflow with 2013 platform it doesn’t work in 2010 or earlier versions. (I am using a SharePoint 2013 designer to create a sharePoint workflow for office 365 or SharePoint online 2016)
Create a SharePoint 2013 Workflow
Create a SharePoint 2013 Workflow on the List
  • Now from the action pick build the dictionary
Build a dictionary which will be used for the headers of the Web service call.
Build a dictionary which will be used for the headers of the Web service call.
  • Click on the {this} and add two strings request header.We use this Dictionary to create a Request header for the response. Add two strings
    • Name:Accept  Type: String Value:application/json;odata=verbose
    • Name:Content-Type  Type: String Value:application/json;odata=verbose
Add a string to the build dictionary
Add a string to the build dictionary
Two strings are added to build the dictionary
Two strings are added to build the dictionary
9
Create a new dictionary variable named JsonRequestHeader to use for header
  • Add new dictionary variable for the header as above image
  • Now in the next step let’s call the web service from the actions pick call HTTP Web service
10
Add call  web service action from the workflow
  • Now we need to assign the header we created earlier to this web service call.
  • Click on the right corner bottom arrow of the call web service action and menu should drop down then click the properties and change the header to the header we just created.
22
Click on the properties from the menu
23
In the request header assign the dictionary we created at the first step
  • Click on the this after call  as shown in picture here we are going to paste the rest api call we have.
  • 12
    Click on the this hyperlink next to Call and paste the Rest Api call you have in the string builder

    Full URL is =Your Site Url/_api/lists/getByTitle(‘ListName’)/items(itemid)?select=AttachmentFiles,Title&$expand=AttachmentFiles 

    11
    Here is the full URL of the rest api call broken in parts (make sure you have your URLs correct  you can hard-code the site URL or list name also)

    Now click on the response next to Response content. It asks us to add a variable let’s add a local workflow variable of type dictionary and name it JsonResults.

13
Add new dictionary Named JsonResults
14
JsonResults added

Note: Following steps up to number 1-11 are for educational purpose to understand the Json data. It isn’t part of the result

  1. After the web service call add  an email action to inspect the Jsonresult content to  a user (the user who created the item )to see the received Json data from the call.

    15
    Email to Current user the JsonResult content
  2. Now let’s save and publish the workflow
  3. 16
    Save and publish the workflow

    Go to the list and click the workflow

  4. 17
    Pick an item and go to workflow

    Run the workflow

    18
    Run the workflow
  5. Now if the workflow ran successfully then you should get an email to the item creator’s email address (i am sending email to item created by) let’s check our email for the content.
  6. 24
    Email Sent by the workflow with the json content

    Let’s Copy the Json and parse it using some online parser.

  7. I  use http://json.parser.online.fr/
  8. 25
    Parsing the json content and finding the fields we need

    Here you go as seen in the picture this is the structure for the json resulted from the Rest Api call for the attachment file.

  9. So we have two  fields Filename or ServerRelativeURL that has information related to the attachment links. I am using Filename for my URL construction, you can use which one is more suitable for you.  This structure is for 1 attachment so if we have multiple attachments then we will have multiple items with same metadata structure  as seen in below picture.
Jsonstructure for attachments in 1 item
Full Json structure for all the attachments in one single SharePoint list item

10. Now we need to determine the path for the attachment field to read the value because we will use the get item by path action from the workflow to read and retrieve the node value we need.

Note: Action from 1-10 you don’t have to do, i did it to understand the Json and determine what can we use to construct the attachment URL.

  • Now after examining the JSON, delete the email action and  add another stage called Get Attachment URL in the workflow and continue.
  • Pick workflow action get item by name or path and add
Add Get item by name or path action from the workflow
Add Get item by name or path action from the workflow
  •  Now in the action replace following values
    • item by name or path= d/AttachmentFiles/results (Look at the json result to determine this)
    • dictionary=JsonResults
    • item = Create new dictionary variable called TotalResults

So we are reading the results items from  JsonResult (the returned Json Content) data  with item path d/AttachmentFiles/results store it in another dictionary variable called TotalResults.

TotalResults will have all the results of the attachments folder for the current list item which is stored and indexed by integer value. so 0 will have the first attachment details and 1 will have the second and so on.

Now after replacing the values lets add another action from the workflow to count the total items in TotalResults and save it to a variable named count

26
Added a count items action to count the total items in TotalResults for looping
  • That means now the variable count will have the count value of the attachments each item has. ( ie. 1 attachment count=1, 0 attachment count=0 and so on)
  • Next step is we will check the value of count and if it is greater than 0 means it has attachments and we will loop through JsonResult and retrieve the attachment fields
  • Let’s add a condition If variable:count is greater than 0 So then  set a workflow variable StartCount (create an integer if not created) and set the value to 0.
27
Add a if condition to check the variable count and if it is greater than 0 we create another integer variable called StartCount 
  • Set the variable StartCount to 0 ( we will use this variable to drive up our loop)
  • After the StartCount variable From the action let’s add a Loop with condition
addloop1
Add loop with condition
  • Now replace following value in the loop as shown in the above image
    • while value= Variable:StartCount
    • equals value= change to less than Variable:count
28
Setting the loop condition
  • Once the loop value and condition is set Add action Get item by name and replace following values
    • item by name or pathd/AttachmentFiles/results/([%Variable:StartCount%])/FileName
      • so if it is first attachment file then it is d/AttachmentFiles/results/0/FileName
    • dictionary= Variable:JsonResult
      • This is the JsonResult which has all the JSON data from the web service call
    • item= Variable:AttachmentFileName (Create a local workflow variable of string type to store the attachment filename)
      • This is the attachment filename
getattachmentresults
Reading Filename from JsonResult 

Add a new string variable named AttachmentFileName to store the attachment file name retrieved   

29

save the Filename to a new  string variable named AttachmentFilename to store the FilenameNote: I am reading Filename from the JsonResult you can read other values as well if you want you can replace Filename with ServerRelativeURL

  •  Now let’s store this value in some variable and construct our Attachment URLs, i am going to append the value to existing (string= string+ new string) so that we don’t loose the value in next loop and we will keep adding to the existing.
30
Adding a local string variable AttachmentUrls to store the Filename  and use for the URL

Once the string is added let’s add the AttachmentFilename into it and make a Url so that every time it loops it adds a new URL if it exists.so open the string builder and let’s build the Urls.

AttachmentUrls=  [%Variable:AttachmentUrls%]<a href=”[%Workflow Context:CurrentSite URL%]Lists/[%WorkflowContext:List Name%]/Attachments/[%CurrentItem:ID%]/[%Variable:AttachmentFilename%]”>[%Variable:AttachmentFilename%] </a> <br/>

Breaking this URL in pieces

  •  [%Variable:AttachmentUrls%] (The string itself so that we don’t loose the earlier value)
  • Building the link (Getting the value from workflow context, click on add or change lookup to get to workflow context)
    • [%Workflow Context:CurrentSite URL%] (Current site Url)
    • Lists/( part of the URL hard-coded)
    • [%WorkflowContext:List Name%] (Current List Name)
    • /Attachments (part of the URL hard-coded)
    • [%CurrentItem:ID%] (Current item id)
    • /[%Variable:AttachmentFilename%] (AttachmentFilename received from the earlier get item by name of or path action)
31
Value of the AttachmentUrls variable set using the string builder
  • Now to increase the loop value next time add calculate action from the workflow.
addcalc
Add  calculate action to increment the loop variable

Replace following value from the above image

  • Calculate value=StartCount
  • plus value= 1
    • We are adding 1 to StartCount and saving it to workflow variable called calc
  • Then add another action below set workflow variable and replace
    • workflow variable = Variable:StartCount
    • value=calc
32
Establishing the loop increasing the variable StartCount  that drives the loop
  • As seen in image above we incremented StartCount with 1 every time and stored it in the variable called calc ( A local workflow variable of type number)  then reset StartCount variable to calc.
    • Now the value of StartCount= StartCount+1 in every loop
    • Next time the loop will start from StartCount+1
  • Now Since we have everything completed lets add an email action out of the loop and get the values inside it
33
Add an email action and in the body add the AttachmentUrls  
  • Go back to the list and run the workflow. If you have followed everything in this article then you should receive an email with the attachment links for each item as below.

34

Finally, we are able to get attachment links and put it in the workflow email along with all the list fields and values.

Let me know if you ran into any issues following this @ your work.

Advertisements

8 thoughts on “SharePoint List Attachment URL in Email using SharePoint Designer Workflow and Rest API

  1. Great that works !!
    I want to know how this gonna work in sharepoint list column because i try adding in hyperlink column but i am getting only normal link and hyperlink column is restricted to 255 characters so workflow through an error. That would be great, name we getting in email if we can get this same in sharepoint list column.

  2. This article is very helpful. The only issue we are facing is if there are spaces in the name of the file that was attached to an SharePoint item, the hyperlink got will be broken after the first space. For example, a file name “ABC DEF GHI.pdf”, the hyperlink turned out will be like http://xxxx.xxx.com/xxx/ABC“. So the file cannot be opened actually. Is there a solution to this? Thanks a lot!

Leave a Reply

Your email address will not be published. Required fields are marked *