OpenKM 6.3.1 & Outlook 2013

[ofstab]

I have been testing OpenKM Community Edition for about a month now, and have been able to work my way through most of the early learning curve difficulties.

My current problem relates to integration with Outlook. In particular emails imported from Outlook using the Community addin. I am operating in an Exchange server environment.

Before I installed the add in, I was able to import emails in a two stage process:

1. Save the email from outlook
2. Import the .msg file created in the previous step.

This works fine, but is cumbersome.

Having installed the add in, I can now import in one step, which is a good thing. However, the emails imported with the add in are handled differently by OpenKM.

The name formats of the sender and recipient are different. An email imported in the two stage process has this format:

Code: Select all

Firstname Lastname <username@domain.com>

Its mime type is text/html.

The same email imported through the add in has this format for external email addresses (i.e. not accounts on our own exchange server):

Code: Select all

username@domain.com

and this format for internal accounts (i.e. those on our exchange server):

Code: Select all

/O=OUR_NAME/OU=Location/cn=Recipients/cn=Exchange User Name

My understanding is that this last format is in the X.500 format (although I do not hold myself out as an expert!). The resulting mime type is text/plain.

Both types of imported emails are readable in OpenKM. They can be moved around the system. Their metadata can be edited.

However, the big problem is that I cannot export, or download, the second type of email. If I try I get these errors:

Code: Select all

2016-01-05 09:49:03,959 [http-bio-0.0.0.0-8080-exec-8] ERROR org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/OpenKM].[FrontendDownloadServlet]- Servlet.service() for servlet [FrontendDownloadServlet] in context with path [/OpenKM] threw exception [com.openkm.frontend.client.OKMException: Local address contains control or whitespace] with root cause
com.openkm.frontend.client.OKMException: Local address contains control or whitespace

Code: Select all

2016-01-05 09:51:31,128 ERROR javax.mail.internet.AddressException: Local address contains control or whitespace in string ``/O=OUR_NAME/OU=EXCHANGE ADMINISTRATIVE GROUP'' 'Local address contains control or whitespace'

My immediate problem is that I cannot migrate to MySQL from the inbuilt database, because the Admin->Export function cannot handle emails imported to OpenKM through the add in. They are simply not exported. Manually imported emails are fine.

However, there are obviously more problems than simply migrating. I cannot download these emails, so I cannot share them with non-OpenKM users.

My guess is that the version of the add in that I am using does not properly support Outlook 2013?

Outside of recoding the add in, which is beyond me, I wonder if I might be able to edit the sender/recipient details, perhaps as part of a regular cronjob to remove the offending strings.

Despite some vigorous searching I have been unable to find other users with a similar problem, and I do wonder if there is some obvious setting or configuration that I am overlooking.

Many thanks,

[jllort]

About the point you are seeing "username@domain.com" rather than 2Firstname Lastname <username@domain.com>" it's probably the way we have for previewing mail. Anyway we are changing the way Outlook addin is going and we will import directly the eml file. Also take in consideration there are some differences between community and professional version. The most significative one is that in professional version you are able to store the raw, on community this feature has still not present.

The problem is that we are not taking on consideration the internal exchange user account "/O=OUR_NAME/OU=Location/cn=Recipients/cn=Exchange User Name" this is a surrealistic mail account and sure is not been able to being parsed from the internal mail address validator. Could you share with us some of these problematic mails and we will take a look on them.

[ofstab]

Thank you for your reply.

If I drag an email from outlook to an explorer folder it appears as a .msg file. I can then import that file without any difficulty. The preview shows the name and email address in the "Firstname Lastname <emailaddress>" format. This looks fine.

If I import an email using the outlook addin then for all internal email addresses outlook supplies an x.500 or x.400 email address to the OpenKM addin, which is transferred to OpenKM. This is not pretty and then causes the Java crash I repeated above if you try to extract the email from OpenKM.

This was NOT the case if the x.400 email address did NOT have a whitespace in it. For example, part of the x.400 address of a colleague was "SMITHJ", and his emails to and from an external address loaded into OpenKM OK. They still showed the x.400 email address, but as there was no whitespace, there was no error.

Part of my x.400 address, on the other hand, is "JOHN SMITH". That prompts the error. It may be that our system is using illegal x.400/x.500 email addresses with white spaces which is the route of the problem. I do not know enough about x.400 addresses to know what is legal and what is illegal.

As an experiment, I started a new repository (I wanted to migrate to MySQL anyway) and imported the partial export. This was not good. Even those emails which had x.400 addresses but no whitespaces had problems. They stopped the system showing any more emails in the OpenKM folder. For example, if there was an email with an x.400 address in a folder, no matter how many other emails you try to add to the folder you could not see them. They would appear in the zip file if you downloaded the whole folder as a zip, but OpenKM would not be persuaded to show them. I tried to the load that zip file back into OpenKM, and it got stranger still.

Any email with an x.400 email address lost that address entirely and simply claimed to have been sent by the internal OpenKM email account. The recipient was blank.

At this point, I gave up. OpenKM does not play nicely with x.400 email addresses. The outlook addin needs to block these from being passed. My solution was to go back to my original repository and to delete all emails. I then exported the whole repository, and imported it into a fresh install. I then extracted all the necessary emails from outlook by dragging to explorer, and imported them that way.

The only problem with that is that the "Update Date" of all outgoing emails is now today, not when I sent them over the last month of testing. Inbound emails date stamps are fine.

I am unable to extract problematic emails from OpenKM, so I cannot supply them. Hopefully this narrative is useful to you as you continue to modify the addin.

I do bear in mind that the community and professional versions will differ. I wonder if it is worth me testing a trial version of the professional to see if I can replicate the bug?

[jllort]

If I understood, the same mail inserted as msg into OpenKM goes right but imported from Outlook addin fails, is that ?

[ofstab]

Thanks again for your reply. I have been testing a number of different approaches to this problem. Here is the present position:

Outlook addin:
Email address: users on our exchange server are rendered in x.400 or x.500 format, which breaks OpenKM, meaning I cannot extract them from the repository in ANY format, although I can see their contents in preview.
Attachments: Attachments are perfect, and can be downloaded from the preview window.

Import from Folder:
Email addresses: These work fine for all users.
Attachments: Most attachments are fine. However, if the attachment is another email message (not embedded in the email body but actually an attachment) then the import strips these attachments away from the email message. I can download the email from OpenKM, but the attachment is gone. If I mix attachments, so a PDF and a .msg attached to the same email, only the PDF remains after import from folder to OpenKM.

Import from this Script:

Code: Select all

import com.openkm.core.*;
import com.openkm.api.*;
import java.io.*;
import com.openkm.module.db.stuff.DbSessionManager;
 
String token = DbSessionManager.getInstance().getSystemToken();
OKMDocument document = OKMDocument.getInstance();
OKMFolder folder = OKMFolder.getInstance();
 
public void autoImport(String okmPath, File fldpath){
  try {
    print("Scanning " + fldpath.getName() + "<br>");
    for (File file : fldpath.listFiles()) {
      print("Importing " + file.getName() + "<br>");
	try {
	  if (file.isDirectory()) {
	    try {
	      folder.createSimple(token, okmPath + file.getName());
	    } catch (ItemExistsException ie) {
	      print("folder already exists<br>");
	      // Folder already exists - just ignore exception
	    }
	    autoImport( okmPath + file.getName() + "/", file);
	  } else {
	    // Check if file is still being written to
	    long length = file.length();
	    Thread.sleep(1000);
	    if (file.length() > length) continue;  // Skip file this time
	    document.createSimple(token, okmPath + file.getName(), new FileInputStream(file));
	  }
	  print("Created " + okmPath + file.getName() + "<br>");
	} catch (Exception e) {
	  print ("Exception:" + e + "<br>");
	  // Something bad happened to prevent import. Skip to next file.
	 continue;
	}
       file.delete();
     }
  } catch (Exception e) {
    print("Exception: " + e + "<br>");
  }
}
 
autoImport("/okm:root/ImportedDocs/", new File("C:/DMS_Import/"));

The email shows up in OpenKM as a file with mime type application/vnd.ms-outlook. Preview does not work, but the full email with attachments is available if downloaded from OpenKM.

So:
Outlook AddIn: Attachments: Yes, Email Addresses:No, Preview: Yes
Folder Import: Attachments: No, Email Addresses: Yes, Preview: Yes
Script Import: Attachments: Yes, Email Addresses: Yes, Preview: No

[ofstab]

Update!

I think I have found the problem with the Outlook Addin.

In the source (OpenKMAddIn.cs, line 99) the relevant section which grabs the sender email address is:

Code: Select all

newMail.from = mailItem.GetType().InvokeMember("SenderEmailAddress", System.Reflection.BindingFlags.GetProperty, null, mailItem, null).ToString();

However, according to this article:
https://anoriginalidea.wordpress.com/20 ... bnet-vsto/

The “Address” property of the MailItem object is supposed to return the email address. And it does if your email comes from an internet sender[...]. If the email sender a user of Microsoft Exchange, you get a weird X400 formatted email address. Not very handy if you want the SMTP email address.

That is exactly the problem I am seeing. Helpfully, the rest of the article contains the vb.net code that is needed to extract the SMTP email address from Exchange by way of Extended MAPI. Unfortunately it is beyond me to a) incorporate that code into the Outlook Addin source code, and b) compile that source into a usable binary. Hopefully this info might allow someone more code literate than I to achieve that.

[jllort]

We are doing now some changes on outlook addin, well will be released we will notice to you for checking that case.

[ofstab]

Thanks for your update. I wanted to use emails with OpenKM so I wrote my own Outlook Add In. I had to learn (a little bit) of visual basic to do it, but it seems to work quite well. I looked up your source code for the add in as I said earlier in the thread, but once I learned to read it it appears it is quite out of date and does not reflect the current functionality of the add in.

My plugin does the main job I needed quite well, in that it detects when a sender is an exchange user and gets the email address in a different way. My method for that is:

Code: Select all

    Private Function GetSenderSMTPAddress(inputMessage As Outlook.MailItem) As String

        Dim senderOfInterest As Outlook.AddressEntry, sendersExchangeRecords As Outlook.ExchangeUser
        senderOfInterest = inputMessage.Sender
        If senderOfInterest IsNot Nothing Then
            If senderOfInterest.AddressEntryUserType = 0 Then 'Note 0 replaces olExchangeUserAddressEntry from _
            										 https://msdn.microsoft.com/EN-US/library/office/ff868214.aspx
                sendersExchangeRecords = senderOfInterest.GetExchangeUser
                GetSenderSMTPAddress = sendersExchangeRecords.PrimarySmtpAddress
            Else
                GetSenderSMTPAddress = inputMessage.SenderEmailAddress
            End If
        Else
            GetSenderSMTPAddress = "nobody@nowhere.tld"
        End If

        senderOfInterest = Nothing
        sendersExchangeRecords = Nothing

    End Function

(I am all for interesting object names, I just cannot remember any of the naming scheme systems so I apologise in advance)

Because the dates are not stored in the current model I use a system where outlook prints the email through word to a pdf, and then the pdf is uploaded to OpenKM with some custom properties as the sender/date/subject etc. I also cannot turn an outlook.mailitem object into a mailbean object, because I am fairly useless.

It also works with selections of emails allowing for bulk uploading. It stores any attachments in folders named the same as the underlying email.

I have also programmed a custom task pane that is usable on outgoing emails that lets you select a folder to save the email to after it is sent. The add in just monitors any additions to sent items and checks them for a subject line code which the taskpane adds. At the moment as long as the code is in a format which matches a regular expression it triggers saving the same to the last chosen path. I would like to expand this to have a code generated for each folder being used to store emails, so the add in would automatically know where to store them. Then it would be a simple step to have it check the inbox for incoming emails as well.

I also have a custom ribbon in compose windows which gives you the ability to grab documents from OpenKM as attachments, using the ExplorerForm in the SDK for .net. The method for that looks like this:

Code: Select all

    Private Sub button_OKM_addattachment_Click(sender As Object, e As RibbonControlEventArgs) Handles button_OKM_addattachment.Click
        Dim ThisOutlookSession As Outlook.Application = New Outlook.Application
        Dim currentoutlookobject As Object = ThisOutlookSession.ActiveInspector.CurrentItem

        If (TypeOf currentoutlookobject Is Outlook.MailItem) Then
            Dim emailinneedofanattachment As Outlook.MailItem = currentoutlookobject

            Dim instanceofDictionaryhelper As New DictionaryHelper()
            Dim instanceofEncodingType As New UTF8Encoding
            Dim instanceoffileInputStream As IO.FileStream = New IO.FileStream(instanceofOKMdatahandler.getdictionary_filename, IO.FileMode.Open)

            instanceofDictionaryhelper.loadDictionary(instanceoffileInputStream, instanceofEncodingType)

            'We can now pull a version of the ExplorerForm, passing the dictionary and webservice as normal.
            Dim instanceofExplorerForm As New OKM_SDK_Form_Files_Class(instanceofDictionaryhelper, instanceofWebservice) ', thismailitemwantinganattachment) 
            instanceofExplorerForm.showSearch()
            instanceoffileInputStream.Close()

            'WHat we are left with is a raw file sitting in a temp folder.  We now need rename if from the XML data file saved by the form.
            If My.Computer.FileSystem.FileExists(instanceofOKMdatahandler.getOpenKMtempdirectory() & "\rawoutputfromopenkm") Then
                My.Computer.FileSystem.RenameFile(instanceofOKMdatahandler.getOpenKMtempdirectory() & "\rawoutputfromopenkm", instanceofOKMdatahandler.getattachmentfilename())
                'We can now attach it to the current email, if it is small enough:
                Try
                    emailinneedofanattachment.Attachments.Add(instanceofOKMdatahandler.getOpenKMtempdirectory() & "\" & instanceofOKMdatahandler.getattachmentfilename()) 'this can cause java memory heap errors on the server
                Catch ex As Exception
                    MessageBox.Show("Outlook refused to accept the attachment.  Perhaps it was too large?", "OpenKM Plugin Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                End Try
                'And finally we can delete the temp file, whether or not it loaded.
                My.Computer.FileSystem.DeleteFile(instanceofOKMdatahandler.getOpenKMtempdirectory() & "\" & instanceofOKMdatahandler.getattachmentfilename())
            Else
                MessageBox.Show("No Attachment Found.", "OpenKM Plugin Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            End If
        Else
            MessageBox.Show("You can only add attachments to emails", "OpenKM Plugin Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If

    End Sub

(I couldn't get the dictionary helper working with a dictionary file built into the assembly so I just wrote a method that creates a dictionary file in the user's mydocs/OpenKM folder and uses that everytime it creates a form. Again, sorry.)

The Form to make that work class looks like this:

Code: Select all

Public Class OKM_SDK_Form_Files_Class
    Implements ExplorerHandler
    Private instanceofExplorerForm As ExplorerForm
    Private instanceofWebservice_internal As OKMWebservice
    Private instanceofOKMdatahandler As New OKM_Data_Handler_Class()

    Public Sub New(dictionary As DictionaryHelper, instanceofwebservice_external As OKMWebservice)
        instanceofExplorerForm = New ExplorerForm(Me, dictionary, instanceofwebservice_external)
        instanceofWebservice_internal = instanceofwebservice_external
    End Sub

    ' showTree
    Public Sub showSearch()
        instanceofExplorerForm.setFirstTime(True)
        instanceofExplorerForm.ShowDialog()
    End Sub

    ' explorerButtonCancelled
    Public Sub explorerButtonCancelled() Implements ExplorerHandler.explorerButtonCancelled
        ' something to do here?
    End Sub

    'explorerButtonAccepted
    Public Sub explorerButtonCancelled(documentfilenameandopenkmpathselectedonform As [String], docUuid As [String]) Implements ExplorerHandler.explorerButtonAccepted
        Dim filecontent As Byte() = instanceofWebservice_internal.getContent(documentfilenameandopenkmpathselectedonform, False)
        System.IO.File.WriteAllBytes(instanceofOKMdatahandler.getOpenKMtempdirectory() & "\rawoutputfromopenkm", filecontent)
        instanceofOKMdatahandler.setattachmentfilename(documentfilenameandopenkmpathselectedonform)
    End Sub
End Class

(I have just noticed that I appear to be trying to overload the explorerButtonCancelled method in that class. That was just an accident and I have misnamed the method.)

I had to code in visual basic as it was the easiest to learn, and I see most of the underlying code is in java. Still if the plugin or VB code I have so far would be of use to anyone I would be happy to post it somewhere - if you can give me a clue where. I am programming in Visual Studio Community edition using the VSTO plugins. I am not using the addin-express which I think is currently used in the plugin so I can only guarantee that my code works on Outlook 2013 & 2016 as that is what I have tested it on.

The whole thing is about 1,300 lines of code at the moment, so I don't want to just paste it into a forum post.

[jllort]

Sorry for our late answering, but seems your post was locked under non approved status and until now we had not seeing it.

WE will take a look at your code if can be integrated with ours.

[pherrera]

Hello,
Please could you give me one of your emails in eml or msg format within a zip in the forum, so we can try.
Thank you.

[ofstab]

You mean one of the ones I was having trouble with?

[pherrera]

An email with which you have problems with the format of email address sender.

[jllort]

Hi

If you want to share the code with us, we will investigate it and if it's possible integrate with our actual code. If you are not able for uploading the project here ( because contains company data etc... I can provide to you my mail address ).

My colleague pherrera, ask to you is you can share with us a .eml or msg problematic mail for testing in our side ( I'm talking about the mails which are not compatible with usual mail format because it's exchange mail format "Email address: users on our exchange server are rendered in x.400 or x.500 format, which breaks OpenKM" ).