Extract data from outlook messages.

[danswan]

I doubt if this is possible, but I thought it might be worth an ask. I'll give you the background to understand what I'm trying to do, might make more sense.

I work in an office where we handle incoming calls and make outgoing calls. Ordinarily there would be software to do what I want, but for whatever reason there hasn't been any investment into such things.

We use Microsoft Lync as our phone system and after every call it creates a message in a 'Conversation History' folder in Outlook. I can quite easily copy all the message headers from this folder and paste them into excel and find out how many calls per week/month etc which is quite useful. The message contents contain one further bit of information which I would like to get out into excel as well, call duration (see screenshot below with personal data removed).

Ideally, what I would like would be for this information to be available as a column in Outlook, which I could then just paste into Excel and organise accordingly. I have no idea if this is possible though. I've been messing around with exporting from the PST into Access but not getting anywhere. Any help gratefully received.

 

[AHarvey]

who is running the Lync Server?

I'm pretty certain that the server can auto generate reports that cover all the info you want.

EDIT: I thought Lync was being phased out for Skype?

 

[danswan]

Yeah, sorry it is now branded 'Skype for Business'

The reason I've been doing this is that it's proving extremely difficult to get any of this sort of information from our hosting company, we've been asking for 18 months now. Obviously that's not good enough but that's a separate issue!

 

[danswan]

Managed to get this info into MSAccess, is there some sort of expression I could use that would extract the data after the word 'Duration' as in the screenshot above?

 

[dazzerd]

Presuming you all the data is in one string and always prefixed with "duration: " then just use something like Select blah, blah2, mid([access field name],11, 255) AS DurationString [* you cold be clever here and get the exact length of the string should you wish bit no need I think]
FROM yourAccessTable

or even better:

Select Blah, Blah1, replace([access field name],"duration: ","") as DurationString FROM your AccessTable

will work

 

[danswan]

Many thanks, this sounds exactly what I need. I'll have a play