Introduction
Recently, we were asked to create a solution for moving a specific email from a specific team to a SharePoint 2007 list in order to track the status. The challenge was that we should use only SharePoint designer. This tip will show you exactly how to read an email automatically and add the email to a custom SharePoint list.
Background
I created a new folder in Outlook and added rules so that email from a specific team would be moved automatically to the folder, and then we added a macro so that once an email moved to the folder, a webpage from Sharepoint will get opened. Using Outlook activex in JavaScript, we will not access the Outlook and read the unread email in the folder and get email contents, now using spservices we moved the email contents to the SharePoint list.
Steps Overview
There are three steps that are explained in the tip which will help you to read emails and add it into SharePoint list.
Step 1: Create a new folder in your Outlook and add rule so that emails would be moved to that folder automatically.
Step 2: Write a macro in Outlook to open a webpage.
Step 3: Webpage will read the unread emails automatically and add it into SharePoint list.
Step 1: Create a new folder in your Outlook and add rule so that emails would be moved to that folder automatically.
As this is a simple step, beginners can refer to the below links to create folder and to add rules in Outlook.
- To create new folder
- https://askdrexel.drexel.edu/app/answers/detail/a_id/2283/~/how-to%3A-create-a-folder-in-outlook-2007
- To create rules to move emails to a specific folder
- http://www.codetwo.com/kb/how-to-create-in-outlook-a-rule-moving-mail-to-a-specified-folder/
I have created 'Sample' folder in my Outlook.
Step 2: Write a macro in Outlook to open a webpage.
In Outlook, click ALT+F11 to open Visual Basic Editor and add the below VB code. The below code will access the Internet Explorer browser and navigate to the URL which is specified in oApp.
Sub MyRule(Item As Outlook.MailItem)
Dim strURL As String
Dim oApp As Object
Set oApp = CreateObject("InternetExplorer.Application")
strURL = "http://server/page/sample.aspx"
oApp.navigate (strURL)
oApp.Visible = True
End Sub
Step 3: Webpage will read the unread emails automatically and add it into SharePoint list.
We can use Activex object and access the Outlook application in our machine.
var objOutlook = new ActiveXObject("Outlook.Application");
By getting hold of the 'Session' of Outlook application, we can access all the folders and contents in Outlook. Refer to the below screen shot of developer tool, you could see the variables for all the Outlook objects.
Develop a simple HTML page with a table containing the following fields:
- Sent By
- Sent on
- Recived By
- Recived on
- Subject Content
Create a JavaScript function and call the function through button click or on page load in case you want to load the data while opening the page.
In the below function, I used ActiveXobject of Outlook and iterated to the sub folder "Sample" in Outlook and checked for unread messages. In available of unread messages, the properties like SenderName, SentOn, ReceivedBy, etc. of the messages are retrieved and made displayed in a grid. Then by setting UnRead = false the emails will be Marked as read in Outlook.
<script>
function PopulateTable()
{
var objOutlook = new ActiveXObject("Outlook.Application");
var session = objOutlook.Session;
for(var folderCount = 1;folderCount <= session.Folders.Count; folderCount++)
{
var folder = session.Folders.Item(folderCount);
if(folder.Name.indexOf("Mailbox - ")>=0)
{
for(var subFolCount = 1; subFolCount <= folder.Folders.Count; subFolCount++)
{
var sampleFolder = folder.Folders.Item(subFolCount);
if(sampleFolder.Name.indexOf("Sample")>=0)
{
for(var itmCount = 1; itmCount <= sampleFolder.Items.Count; itmCount++)
{
var itm = sampleFolder.Items.Item(itmCount);
if(!itm.UnRead)
continue;
var sentBy = itm.SenderName;
var sentDate = itm.SentOn;
var receivedBy = itm.ReceivedByName;
var receivedDate = itm.ReceivedTime;
var subject = itm.ConversationTopic;
var contents = itm.Body;
var tbl = document.getElementById('tblContents');
if(tbl)
{
var tr = tbl.insertRow(tbl.rows.length);
if(tbl.rows.length%2 != 0)
tr.className = "alt";
var tdsentBy = tr.insertCell(0);
var tdsentDate = tr.insertCell(1);
var tdreceivedBy = tr.insertCell(2);
var tdreceivedDate = tr.insertCell(3);
var tdsubject = tr.insertCell(4);
var tdcontents = tr.insertCell(5);
tdsentBy.innerHTML = sentBy;
tdsentDate.innerHTML = sentDate;
tdreceivedBy.innerHTML = receivedBy;
tdreceivedDate.innerHTML = receivedDate;
tdsubject.innerHTML = subject;
tdcontents.innerHTML = contents;
}
itm.UnRead = false;
}
break;
}
}
break;
}
}
return;
}
</script>
<div id="divResult">
<input type="button" value="Read" onclick="PopulateTable();" /><br />
<table cellpadding="3" cellspacing="3" border="1" id="tblContents" >
<tr>
<th style="width:100px;">Sent By</th>
<th style="width:80px;">Sent on</th>
<th style="width:80px;">Recived By</th>
<th style="width:75px;">Recived on</th>
<th style="width:75px;">Subject</th>
<th style="width:200px;">Content</th>
</tr>
</table>
</div>
Points of Interest
By accessing the table elements, we moved the data to SharePoint list and SQL Server.
