There’s been a bit of buzz lately about the uncertain future of public folders. This got me thinking about how I use public folders sometimes to achieve certain things and how I might go about replacing this. One of things I use public folders for sometimes is to act as a shared mailbox like Mark has described in http://www.msexchange.org/articles/MF021.html . This is a quick easy way to achieve a shared mailbox and works fairly well.
There are a few ways you could go about replacing something like this because I have a database server and a few IIS servers already in my network with spare capacity this seemed like it could offer an alternative. So I started with a basic framework of a mailbox that would receive email for my shared mailbox’s email address this would then fire an Event sink attached to the mailbox which would then parse the message and the attachments out and store these is a database. I’ve then got an ASP.NET web application that can then read and display a list of messages and attachments. When I started this I was focused more on attachments more then the content of the messages because I have a shared mailbox that receives a lot of attachments. So I wanted something that would allow me to download the attachments on the messages from the “inbox view” without having to look at the content of each message. This is the type of View I came up with http://bluetack.aspxconnection.com/images/listview.jpg
After I had the attachment download going it seemed a shame just to leave it there so I included some more forms to show the message content and then lastly to respond to the message.
The Database
Storing email in a database creates a lot of fairly unique challenges essentially email is bunch of blobs of data which you then need to store in structured relational tables. I’ve had one go at this before which had mixed results the main problem is when you start to stored a lot of data in flat tables the table and database can start to become a little unweilding. So for my second attempt I’ve gone for three tables and one view this time.
The inbox table: the first table stores the major email header fields and the html message body which is stored in a text field in the database.
The Attachments table: This stores all the attachments from a message it’s related to the inbox table based on the PR_Entry_ID from the Exchange Store. To store the attachments what I decided to do is store them in the RFC 2045 MIME encoded body part format that can accessed using CDOEX via the event sink. Within the ASP.NET app that allows the download of the attachment I’ve used CDOEX as well (you could also use CDOSYS) it basically uploads the attachments you want to download into a temporary message object and then decodes the MIME and sends it back to the user via the browser response stream. The message Attachment’s MIME body part is stored in a text field in the attachments table.
The Sent table stores sent mail I’ve used the System.web.Mail class to send mail in my web application via SMTP this helps if the mailbox the sink is on has a different primary address to that you want to use for replies this way you bypass the Exchange submission URI the bad part is this is that mail doesn’t get stored in the sent items of the mailbox so this is the reason for the database insert.
The vwViewMessages view is a database view that links both the inbox and attachments table together via an Outer join to display all the attachments of a message in a single database view.
I’ve included a SQL script in the download for this article with all the table definitions I used
The Event sink
Currently I’ve used a script based async onsave event sink the sink itself is relatively straight forward. It basically takes the header fields and message body and then parses out any single quotes (‘) and escapes them with another quote to ensure that they can be inserted into a database successfully. It also loops though all the attachments in the message and then populates the attachment table with any attachments from the message. For embedded messages which are those attachments with a content type of RFC/822 the whole embedded message is stored in the attachments table. There some code to decode the PR_EntryID into a hex value which is then used to uniquly referance each mail
The Web Application
The Web application itself consists of four different forms which all perform distinct functions
Listmsg.aspx This form displays a list of messages and attachments using the vwViewMessages view. As I said previously to allow for attachments to be downloaded directly from this view I’ve come up with some custom code to create a custom view of the inbox messages to make it more readable I’m using the ItemDataBound event of the Datagrid to delete duplicated field data that isn’t required this gives a more hierarchal type view of the messages. The ItemDataBound event is also used to add ahref and build querystrings to pass in the entryid and database names to the other forms.
Attachdownld.aspx This form handles retrieving the required attachment from the attachments table decodes it and then returns it to the user via the response stream of the browser. To decode the attachment it use CDOEX, because CDOEX is usually only supported on a Exchange server you can use CDOSYS if you using this on a server that isn’t a Exchange box the only parts of CDOEX that is used is the GetDecodedContentStream which is common within both libraries. If the attachment that the user is trying to download is an embedded message the code needs to handle this differently. What it does is loads the whole embedded message via the streams interface and this provides the whole message to the user via the browser stream. This does bring up one issue if the embedded message has attachments of its own. In this case if you try to download the attachment to the file system it will fail but if you try to open it up in an email application directly it will succeed okay. I’m not 100% sure why this is happening I think its something to do with having multiple content dispositions in the response stream. The workaround that I’ve used is just to tell people to open it up using Outlook or Outlook Express.
Showmsg.aspx This form is a basic message display form pretty straight forward using some query strings to work out what mail you want to open and which database to use. It offers a link to the reply form and a link to go back
Replymsg.aspx: This is the Reply form which gives the user a chance to respond to the message. As a text interface for the reply I’ve used the freetextbox control from www.freetextbox.com. This is one seriously cool .Net control and provides very rich user input functionality that would have take weeks to write if you had to do it you’re self. There's some code the puts the body of the message your responding to in the body of the response and it also creates a header and separator very similar to OWA. Once the send button is clicked on this form the System.Web.Mail class is used to send the mail via SMTP and also some ADO.NET code is used to insert the sent mail into the sent table in the database.
Installation Hard-coded bits and pieces
This code is very much a work in progress but I thought there was enough cool bits and pieces that might be of interest to some people and the reality is that a final complete version may not ever surface because of my time constraints. This is a list of hard-coded bits you need to change. You also need to create your own interops for CDOEX and ADODB (just use the PIA). I’ve create a download that contains all the relative Visual studio files and the event sink code which can be download here . To use the reply form you also need to download and install the freetextbox control from www.freetextbox.com
Event Sink
Database connection string Eg (Data Source=server;Initial Catalog=SharedMailbox;User Id=username;Password=pass)
DatabaseNames I’ve used a generic database name at the moment you’ll need to set it to the name of the database you create.
Aspx files
Database connection strings in all aspx and event sink. Eg (Data Source=server;Initial Catalog=SharedMailbox;User Id=username;Password=pass)
DatabaseNames I’ve used a generic database name at the moment you’ll need to set it to the name of the database you create.
Listmsg.aspx SQL select string needs to be set with the table name for the inbox
daSqlDataAdapter = new SqlDataAdapter("Select EntryID,DateSent,FromName,Subject,filename,attachnum from [vwViewMessages@address@domain.com]", scSQLConnection);
Itemdataboundevent database name needs to be set
string dnDBName = " address@domain.com";
Replymsg.aspx
From email address in the following lines
msMessage.From = "address@yourdomain.com";
and
string hdReplyheader = "<BR><BR><hr width=\"100%\" size=\"1\" color=\"#808080\" align=\"left\" noshade>" +
"<B>From:</B>" + drDatareader["FromName"].ToString() + "[" + drDatareader["FromEmail"].ToString() +"]" + "<BR>" +
"<B>Sent:</B>" + drDatareader["DateSent"].ToString() + "<BR>" +
"<B>To:</B>address@yourdomain.com" + "<BR>" +
"<B>Subject:</B>" + drDatareader["Subject"].ToString();
The event sink code looks like
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
call dbinsert(bstrURLItem)
End Sub
sub dbinsert(murl)
on error resume next
dbDatabaseName = "address@domain.com"
dbAttachmentsName = "Attachments@" & dbDatabaseName
dbDatabaseName = "inbox@" & dbDatabaseName
Set Cnxn1 = CreateObject("ADODB.Connection")
strCnxn1 = "Data Source=server;Initial Catalog=SharedMailbox;User Id=username;Password=pass;"
Cnxn1.Open strCnxn1
set msg = createobject("cdo.message")
msg.datasource.open murl
eiEntryID = Octenttohex(msg.fields("http://schemas.microsoft.com/mapi/proptag/0x0FFF0102").value)
miMessageID = msg.fields("urn:schemas:mailheader:message-id").value
dhDavhref = msg.fields("DAV:Href").value
stSenttime = msg.fields("urn:schemas:httpmail:datereceived").value
fnFromName = msg.fields("urn:schemas:httpmail:fromname").value
feFromEmail = replace(replace(msg.fields("urn:schemas:httpmail:fromemail").value,"<",""),">","")
toToEmail = msg.fields("urn:schemas:mailheader:to").value
sjSubject = msg.Subject
tbTextBody = msg.fields("urn:schemas:httpmail:htmldescription")
haHasAttach = msg.fields("urn:schemas:httpmail:hasattachment").value
line_to_insert = ("'" & eiEntryID & "','" & replace(miMessageID,"'","''") &
"','" & dhDavhref & "','" & stSenttime & "','" & replace(fnFromName,"'","''") &
_
"','" & replace(feFromEmail,"'","''") & "','" & replace(toToEmail,"'","''") &
"','" & replace(sjSubject,"'","''") & _
"','" & left(replace(tbTextBody,"'","''"),255) & "','" &
replace(tbTextBody,"'","''") & "','" & haHasAttach & "'")
sqlstate1 = "insert into [" & dbDatabaseName & "] values(" & line_to_insert &
")"
Cnxn1.Execute(sqlstate1)
i = 1
set objattachments = msg.attachments
for each objattachment in objattachments
if objAttachment.ContentMediaType = "message/rfc822" then
set msg1 = createobject("cdo.message")
msg1.datasource.OpenObject objattachment, "ibodypart"
fnFileName = msg1.subject & "(" & i & ")" & ".eml"
ctContentType = "message/rfc822"
ceContentTransferEncoding = "7bit"
cdContentDisposition =
msg1.Fields("urn:schemas:mailheader:content-disposition").value
set stm = msg1.getstream
mbMessageBody = stm.readtext
line_to_insert = ("'" & eiEntryID & "','" & i & "','" &
replace(fnFileName,"'","''") & "','" & replace(ctContentType,"'","''") & _
"','" & replace(ceContentTransferEncoding,"'","''") & "','" &
replace(cdContentDisposition,"'","''") & "','" & replace(mbMessageBody,"'","''")
& "'")
sqlstate1 = "insert into [" & dbAttachmentsName & "] values(" & line_to_insert &
")"
Cnxn1.Execute(sqlstate1)
else
fnFileName = objattachment.filename
ctContentType = objattachment.ContentMediaType
ceContentTransferEncoding = objattachment.ContentTransferEncoding
cdContentDisposition =
objattachment.Fields("urn:schemas:mailheader:content-disposition").value
set stm = objAttachment.getstream
mbMessageBody = stm.readtext
line_to_insert = ("'" & eiEntryID & "','" & i & "','" &
replace(fnFileName,"'","''") & "','" & replace(ctContentType,"'","''") & _
"','" & replace(ceContentTransferEncoding,"'","''") & "','" &
replace(cdContentDisposition,"'","''") & "','" & replace(mbMessageBody,"'","''")
& "'")
sqlstate1 = "insert into [" & dbAttachmentsName & "] values(" & line_to_insert &
")"
Cnxn1.Execute(sqlstate1)
end if
i = i + 1
next
set msg = nothing
end sub
Function Octenttohex(OctenArry)
ReDim aOut(UBound(OctenArry))
For i = 1 to UBound(OctenArry) + 1
if len(hex(ascb(midb(OctenArry,i,1)))) = 1 then
aOut(i-1) = "0" & hex(ascb(midb(OctenArry,i,1)))
else
aOut(i-1) = hex(ascb(midb(OctenArry,i,1)))
end if
Next
Octenttohex = join(aOUt,"")
End Function
</SCRIPT>
There are a few ways you could go about replacing something like this because I have a database server and a few IIS servers already in my network with spare capacity this seemed like it could offer an alternative. So I started with a basic framework of a mailbox that would receive email for my shared mailbox’s email address this would then fire an Event sink attached to the mailbox which would then parse the message and the attachments out and store these is a database. I’ve then got an ASP.NET web application that can then read and display a list of messages and attachments. When I started this I was focused more on attachments more then the content of the messages because I have a shared mailbox that receives a lot of attachments. So I wanted something that would allow me to download the attachments on the messages from the “inbox view” without having to look at the content of each message. This is the type of View I came up with http://bluetack.aspxconnection.com/images/listview.jpg
After I had the attachment download going it seemed a shame just to leave it there so I included some more forms to show the message content and then lastly to respond to the message.
The Database
Storing email in a database creates a lot of fairly unique challenges essentially email is bunch of blobs of data which you then need to store in structured relational tables. I’ve had one go at this before which had mixed results the main problem is when you start to stored a lot of data in flat tables the table and database can start to become a little unweilding. So for my second attempt I’ve gone for three tables and one view this time.
The inbox table: the first table stores the major email header fields and the html message body which is stored in a text field in the database.
The Attachments table: This stores all the attachments from a message it’s related to the inbox table based on the PR_Entry_ID from the Exchange Store. To store the attachments what I decided to do is store them in the RFC 2045 MIME encoded body part format that can accessed using CDOEX via the event sink. Within the ASP.NET app that allows the download of the attachment I’ve used CDOEX as well (you could also use CDOSYS) it basically uploads the attachments you want to download into a temporary message object and then decodes the MIME and sends it back to the user via the browser response stream. The message Attachment’s MIME body part is stored in a text field in the attachments table.
The Sent table stores sent mail I’ve used the System.web.Mail class to send mail in my web application via SMTP this helps if the mailbox the sink is on has a different primary address to that you want to use for replies this way you bypass the Exchange submission URI the bad part is this is that mail doesn’t get stored in the sent items of the mailbox so this is the reason for the database insert.
The vwViewMessages view is a database view that links both the inbox and attachments table together via an Outer join to display all the attachments of a message in a single database view.
I’ve included a SQL script in the download for this article with all the table definitions I used
The Event sink
Currently I’ve used a script based async onsave event sink the sink itself is relatively straight forward. It basically takes the header fields and message body and then parses out any single quotes (‘) and escapes them with another quote to ensure that they can be inserted into a database successfully. It also loops though all the attachments in the message and then populates the attachment table with any attachments from the message. For embedded messages which are those attachments with a content type of RFC/822 the whole embedded message is stored in the attachments table. There some code to decode the PR_EntryID into a hex value which is then used to uniquly referance each mail
The Web Application
The Web application itself consists of four different forms which all perform distinct functions
Listmsg.aspx This form displays a list of messages and attachments using the vwViewMessages view. As I said previously to allow for attachments to be downloaded directly from this view I’ve come up with some custom code to create a custom view of the inbox messages to make it more readable I’m using the ItemDataBound event of the Datagrid to delete duplicated field data that isn’t required this gives a more hierarchal type view of the messages. The ItemDataBound event is also used to add ahref and build querystrings to pass in the entryid and database names to the other forms.
Attachdownld.aspx This form handles retrieving the required attachment from the attachments table decodes it and then returns it to the user via the response stream of the browser. To decode the attachment it use CDOEX, because CDOEX is usually only supported on a Exchange server you can use CDOSYS if you using this on a server that isn’t a Exchange box the only parts of CDOEX that is used is the GetDecodedContentStream which is common within both libraries. If the attachment that the user is trying to download is an embedded message the code needs to handle this differently. What it does is loads the whole embedded message via the streams interface and this provides the whole message to the user via the browser stream. This does bring up one issue if the embedded message has attachments of its own. In this case if you try to download the attachment to the file system it will fail but if you try to open it up in an email application directly it will succeed okay. I’m not 100% sure why this is happening I think its something to do with having multiple content dispositions in the response stream. The workaround that I’ve used is just to tell people to open it up using Outlook or Outlook Express.
Showmsg.aspx This form is a basic message display form pretty straight forward using some query strings to work out what mail you want to open and which database to use. It offers a link to the reply form and a link to go back
Replymsg.aspx: This is the Reply form which gives the user a chance to respond to the message. As a text interface for the reply I’ve used the freetextbox control from www.freetextbox.com. This is one seriously cool .Net control and provides very rich user input functionality that would have take weeks to write if you had to do it you’re self. There's some code the puts the body of the message your responding to in the body of the response and it also creates a header and separator very similar to OWA. Once the send button is clicked on this form the System.Web.Mail class is used to send the mail via SMTP and also some ADO.NET code is used to insert the sent mail into the sent table in the database.
Installation Hard-coded bits and pieces
This code is very much a work in progress but I thought there was enough cool bits and pieces that might be of interest to some people and the reality is that a final complete version may not ever surface because of my time constraints. This is a list of hard-coded bits you need to change. You also need to create your own interops for CDOEX and ADODB (just use the PIA). I’ve create a download that contains all the relative Visual studio files and the event sink code which can be download here . To use the reply form you also need to download and install the freetextbox control from www.freetextbox.com
Event Sink
Database connection string Eg (Data Source=server;Initial Catalog=SharedMailbox;User Id=username;Password=pass)
DatabaseNames I’ve used a generic database name at the moment you’ll need to set it to the name of the database you create.
Aspx files
Database connection strings in all aspx and event sink. Eg (Data Source=server;Initial Catalog=SharedMailbox;User Id=username;Password=pass)
DatabaseNames I’ve used a generic database name at the moment you’ll need to set it to the name of the database you create.
Listmsg.aspx SQL select string needs to be set with the table name for the inbox
daSqlDataAdapter = new SqlDataAdapter("Select EntryID,DateSent,FromName,Subject,filename,attachnum from [vwViewMessages@address@domain.com]", scSQLConnection);
Itemdataboundevent database name needs to be set
string dnDBName = " address@domain.com";
Replymsg.aspx
From email address in the following lines
msMessage.From = "address@yourdomain.com";
and
string hdReplyheader = "<BR><BR><hr width=\"100%\" size=\"1\" color=\"#808080\" align=\"left\" noshade>" +
"<B>From:</B>" + drDatareader["FromName"].ToString() + "[" + drDatareader["FromEmail"].ToString() +"]" + "<BR>" +
"<B>Sent:</B>" + drDatareader["DateSent"].ToString() + "<BR>" +
"<B>To:</B>address@yourdomain.com" + "<BR>" +
"<B>Subject:</B>" + drDatareader["Subject"].ToString();
The event sink code looks like
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
call dbinsert(bstrURLItem)
End Sub
sub dbinsert(murl)
on error resume next
dbDatabaseName = "address@domain.com"
dbAttachmentsName = "Attachments@" & dbDatabaseName
dbDatabaseName = "inbox@" & dbDatabaseName
Set Cnxn1 = CreateObject("ADODB.Connection")
strCnxn1 = "Data Source=server;Initial Catalog=SharedMailbox;User Id=username;Password=pass;"
Cnxn1.Open strCnxn1
set msg = createobject("cdo.message")
msg.datasource.open murl
eiEntryID = Octenttohex(msg.fields("http://schemas.microsoft.com/mapi/proptag/0x0FFF0102").value)
miMessageID = msg.fields("urn:schemas:mailheader:message-id").value
dhDavhref = msg.fields("DAV:Href").value
stSenttime = msg.fields("urn:schemas:httpmail:datereceived").value
fnFromName = msg.fields("urn:schemas:httpmail:fromname").value
feFromEmail = replace(replace(msg.fields("urn:schemas:httpmail:fromemail").value,"<",""),">","")
toToEmail = msg.fields("urn:schemas:mailheader:to").value
sjSubject = msg.Subject
tbTextBody = msg.fields("urn:schemas:httpmail:htmldescription")
haHasAttach = msg.fields("urn:schemas:httpmail:hasattachment").value
line_to_insert = ("'" & eiEntryID & "','" & replace(miMessageID,"'","''") &
"','" & dhDavhref & "','" & stSenttime & "','" & replace(fnFromName,"'","''") &
_
"','" & replace(feFromEmail,"'","''") & "','" & replace(toToEmail,"'","''") &
"','" & replace(sjSubject,"'","''") & _
"','" & left(replace(tbTextBody,"'","''"),255) & "','" &
replace(tbTextBody,"'","''") & "','" & haHasAttach & "'")
sqlstate1 = "insert into [" & dbDatabaseName & "] values(" & line_to_insert &
")"
Cnxn1.Execute(sqlstate1)
i = 1
set objattachments = msg.attachments
for each objattachment in objattachments
if objAttachment.ContentMediaType = "message/rfc822" then
set msg1 = createobject("cdo.message")
msg1.datasource.OpenObject objattachment, "ibodypart"
fnFileName = msg1.subject & "(" & i & ")" & ".eml"
ctContentType = "message/rfc822"
ceContentTransferEncoding = "7bit"
cdContentDisposition =
msg1.Fields("urn:schemas:mailheader:content-disposition").value
set stm = msg1.getstream
mbMessageBody = stm.readtext
line_to_insert = ("'" & eiEntryID & "','" & i & "','" &
replace(fnFileName,"'","''") & "','" & replace(ctContentType,"'","''") & _
"','" & replace(ceContentTransferEncoding,"'","''") & "','" &
replace(cdContentDisposition,"'","''") & "','" & replace(mbMessageBody,"'","''")
& "'")
sqlstate1 = "insert into [" & dbAttachmentsName & "] values(" & line_to_insert &
")"
Cnxn1.Execute(sqlstate1)
else
fnFileName = objattachment.filename
ctContentType = objattachment.ContentMediaType
ceContentTransferEncoding = objattachment.ContentTransferEncoding
cdContentDisposition =
objattachment.Fields("urn:schemas:mailheader:content-disposition").value
set stm = objAttachment.getstream
mbMessageBody = stm.readtext
line_to_insert = ("'" & eiEntryID & "','" & i & "','" &
replace(fnFileName,"'","''") & "','" & replace(ctContentType,"'","''") & _
"','" & replace(ceContentTransferEncoding,"'","''") & "','" &
replace(cdContentDisposition,"'","''") & "','" & replace(mbMessageBody,"'","''")
& "'")
sqlstate1 = "insert into [" & dbAttachmentsName & "] values(" & line_to_insert &
")"
Cnxn1.Execute(sqlstate1)
end if
i = i + 1
next
set msg = nothing
end sub
Function Octenttohex(OctenArry)
ReDim aOut(UBound(OctenArry))
For i = 1 to UBound(OctenArry) + 1
if len(hex(ascb(midb(OctenArry,i,1)))) = 1 then
aOut(i-1) = "0" & hex(ascb(midb(OctenArry,i,1)))
else
aOut(i-1) = hex(ascb(midb(OctenArry,i,1)))
end if
Next
Octenttohex = join(aOUt,"")
End Function
</SCRIPT>