Record opened emails for reopening later

[Alan Grant]

I'd like to have a macro button on the ribbon that will export the Subject name and Received date/time of all opened Inbox email messages to a list (Excel or CSV), and a second macro button on the ribbon that will refer to that list and reopen those emails. If this is possible, please give me an idea of how I could code this. Thanks.

 

[Diane Poremsky]

The first one is definitely easy - i have macro samples that come close and would need tweaking. The message id needs saved so it can find the correct message later.

Macro to Export Outlook Fields to Excel

Use a macro to write Outlook values to an Excel spreadsheet.

www.slipstick.com

The second one might work best as an excel macro - i don't think i have any samples for that.

 

[Alan Grant]

Hi Diane. If I can get away with even the first part only I would be happy, as I could then have a log of previously opened emails that I can open each manually. That's the most important part for me. From your code sample it looks like it is based on selected emails (Selection property) vs opened emails. So if I read that right, how can I enumerate opened emails? Is there a property, function, or method for that? Regards.

 

[Diane Poremsky]

that would be the read state. do you want it to run automatically when you mark a message read or just run daily or on a schedule?

 

[Alan Grant]

The scenario I wish to cover is when I inadvertently close Outlook or my pc crashes while I have a number of emails open as reminder to respond to, so by having this macro I'm asking about I could run it which will record the subject names of the opened emails into a file or spreadsheet. It's really just a safety precaution so I could more easily know what I had open.

 

[Alan Grant]

I'm really not experienced with VBA but after some more reading I thought of another option I think might accomplish my goal more easily: for each Inbox email that I open a copy is automatically (not explicitly executed) inserted into a new Outlook folder called "Opened Emails". When I close the Inbox email, its copy is deleted from the "Opened Emails" folder. Think this is doable?

 

[Diane Poremsky]

No, i don't think its workable but i will sleep on it. Flagging would be easier - at last if you aren't flagging messages for reminders. it would be easy to do too - hit the flag when you open it and click again when you close.

Categories would work too, but with the quick click gone in the compact view, itsan extra click or you need o use a keyboard shortcut.

 

[Alan Grant]

Sure, categories would be fine I think, if it could be automatically made to be (eg) Yellow when open but uncategorized upon being explicitly closed by me.

*I just need to way to know what emails I had open if Outlook was inadvertently closed, or the computer did a scheduled reboot, crashed, or Outlook crashed.* That's really my main goal. Thanks.

 

[Alan Grant]

By the way, is the difficulty in trying to do this due to Outlook's inability to process such VBA code in the background (vs via a macro button)?

 

[Diane Poremsky]

Outlook can handle background processing, but it can get complicated, especially with multiple messages open.

While I put on my thinking cap try always opening outlook using the /restore switch. This will reopen windows and messages, but I'm not sure if it always remembers the last open messages.

 

[Alan Grant]

Ok thanks.

By the way, I have tried the /restore switch a few times but find it often doesn't work in these situations for some reason.

For my learning, does background VBA still go under ThisOutlookSession as an event procedure?

 

[Diane Poremsky]

By the way, I have tried the /restore switch a few times but find it often doesn't work in these situations for some reason.

I'm guessing it doesnt record all of the open messages, except under specific circumstances, such as windows rebooting.

VBA that runs automatically needs to be in ThisOutlookSession. We can look for open events and close events, but it can get messy - and trying ot do a lookup at the same time can be difficult. I think categories (or flags) might be more efficient and less prone to error.

 

[Diane Poremsky]

baby steps... this sets the category. i'm still working on removing it on close.

Private WithEvents m_Inspectors As Outlook.Inspectors
Private WithEvents m_Inspector As Outlook.Inspector

Private Sub Application_Startup()
Set m_Inspectors = Application.Inspectors
End Sub
Private Sub Initialize_Handler()
Set myItem = Application.ActiveInspector.CurrentItem
End Sub


Private Sub m_Inspectors_NewInspector(ByVal Inspector As Outlook.Inspector)
If TypeOf Inspector.CurrentItem Is Outlook.MailItem Then
'Handle emails only
Set m_Inspector = Inspector
End If
End Sub

Private Sub m_Inspector_Activate()
Dim Mail As Outlook.MailItem
Set Mail = m_Inspector.CurrentItem
If Len(Mail.EntryID) > 0 Then
'Edit the subject only for new emails
Mail.Categories = "Opened"
Mail.Save
End If
Set m_Inspector = Nothing
End Sub

 

[Alan Grant]

This looks very promising. I will try out what you have so far.

FYI, if this works out, there's a lot of people who have asked the question "Can Outlook record/remember opened emails on demand?" or "Are there 3rd party utilities that do similar?" (Stardock came close with their Groupy product but they said they cannot read opened emails yet for this specific purpose).

Maybe you could make some beer $$ with this lol.

 

[Diane Poremsky]

i think I got it working, using Michael's code - Inspector Wrapper: Receive Events of Multiple Emails - VBOffice

This Outlook Session:

Private WithEvents m_Inspectors As Outlook.Inspectors
Private m_MyInspectors As VBA.Collection
Private m_lNextKey As Long

Private Sub Application_Startup()
Set m_Inspectors = Application.Inspectors
Set m_MyInspectors = New VBA.Collection
End Sub

Private Sub m_Inspectors_NewInspector(ByVal Inspector As Outlook.Inspector)
On Error Resume Next
Dim oInspector As cInspector

Set oInspector = New cInspector

If TypeOf Inspector.CurrentItem Is Outlook.MailItem Then
'Handle emails only
Set m_Inspector = Inspector

Dim Mail As Outlook.MailItem
Set Mail = m_Inspector.CurrentItem
If Len(Mail.EntryID) > 0 Then
Mail.Categories = "Opened"
Mail.Save
End If

If oInspector.Init(Inspector, CStr(m_lNextKey)) Then
m_MyInspectors.Add oInspector, CStr(m_lNextKey)
m_lNextKey = m_lNextKey + 1
End If
End If

End Sub

Friend Property Get MyInspectors() As VBA.Collection
Set MyInspectors = m_MyInspectors
End Property
Private Sub m_Mail_Close(Cancel As Boolean)
CloseInspector
End Sub

A class module named cInspector

Private WithEvents m_Inspector As Outlook.Inspector
Private WithEvents m_Mail As Outlook.MailItem
Private m_IsClosed As Boolean
Private m_sKey As String

Friend Function Init(oInspector As Outlook.Inspector, _
sKey As String _
) As Boolean
Dim obj As Object

If Not oInspector Is Nothing Then
Set obj = oInspector.CurrentItem
If TypeOf obj Is Outlook.MailItem Then
Set m_Mail = obj
Set m_Inspector = oInspector
m_sKey = sKey
Init = True
End If
End If
End Function

Private Sub m_Inspector_Close()
CloseInspector
End Sub

Private Sub Class_Terminate()
CloseInspector
End Sub

Friend Sub CloseInspector()
On Error Resume Next
If m_IsClosed = False Then
m_IsClosed = True
ThisOutlookSession.MyInspectors.Remove m_sKey
m_Mail.Categories = ""
m_Mail.Save
Set m_Mail = Nothing
Set m_Inspector = Nothing
End If
End Sub

 

[Alan Grant]

Hi Diane.

I pasted the new code into my ThisOutlookSession and upon reopening Outlook it threw an error. Reopened it again and this time hangs on opening, doesn't give me a chance to remove any code etc. Doesn't seem to be a bypass key to suppress macros on opening. Tried Run Outlook /Safe and that goes a little further then hangs. Do you know of any way to bypass the macros on opening or otherwise reset Outlook?

By the way it may have nothing to do with the above macro and I assume full responsibility of course. I'm still very optimistic about it.

Thanks!

 

[Diane Poremsky]

go to %appdata%\microsoft\outlook and rename the otm file - outlook will create a clean new one.