Help with Outlook VBA

[lmoseley7]

I know this is not directly Excel related, but I don't know if MR OUTLOOK exists, and the issue is related to trying to open a worksheet as a result of a certain email being delivered.

I previously had code in ThisOutlookSession that would mark deleted items read when they were added to the deleted items folder. Part of that code included a declaration at the top of the code window:

'Public WithEvents g_OlkFolder As Outlook.Items'

Then there was an Application_Startup macro that set that variable equal to the olFolderDeletedItems.Items. I was then working on piecing together some code that would open a specific Excel workbook when an email arrived in the inbox that matched certain criteria. The code that I found also included a Public WithEvents declaration:

'Public WithEvents inboxitems As Outlook.Items'

Next there were several declaration and set statements that were added to the Application_Startup macro. Finally, a macro that somewhat mirrored the deleted folder ItemAdd macro, but this one tracked additions to the inbox and the result was a message box that displayed information about the email that was received.When I added this code to ThisOutlookSession, it would not work; no errors, it just never displayed the message box. After some debugging and testing, I discovered that the issue seemed to involve the double declaration of 'Outlook.Items' at the top of the code because when I commented out the code related to the deleted items macros, the new inbox item macro worked perfectly. Is there a way to fix the code below so that both macros will work?

Public WithEvents g_OlkFolder As Outlook.ItemsPublic WithEvents inboxitems As Outlook.Items


Private Sub Application_Quit()
Set g_OlkFolder = Nothing
Set inboxitems = Nothing
End Sub
Private Sub Application_Startup()


Dim outlookApp As Outlook.Application
Dim objectNS As Outlook.NameSpace


Set g_OlkFolder = Session.GetDefaultFolder(olFolderDeletedItems).Items
Set outlookApp = Outlook.Application
Set objectNS = outlookApp.GetNamespace("MAPI")
Set inboxitems = objectNS.GetDefaultFolder(olFolderInbox).Items


End Sub
Private Sub g_OlkFolder_ItemAdd(ByVal Item As Object)
Item.UnRead = False
Item.Save
End Sub
Private Sub inboxItems_ItemAdd(ByVal Item As Object)


'https://www.tachytelic.net/2017/10/how-to-run-a-vba-macro-when-new-mail-is-received-in-outlook/


On Error GoTo ErrorHandler


Dim Msg As Outlook.MailItem
Dim MessageInfo
Dim Result
If TypeName(Item) = "MailItem" Then
MessageInfo = "" & _
    "Sender : " & Item.SenderEmailAddress & vbCrLf & _
    "Subject : " & Item.Subject
    Result = MsgBox(MessageInfo, vbOKOnly, "New Message Received")
End If


ExitNewItem:
    Exit Sub


ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitNewItem
End Sub

 

[lmoseley7]

In the code above, the two public declaration statements were actually on separate rows, although I had tried using a "," after the first declaration and then adding "inboxitems As Outlook.Items" but with no luck. I just tried replacing the 'Public' declarations with 'Private' and it seems to be working so maybe that was the answer? I'd love to have someone that knows more about this weigh in on the issue if they could explain why one presentation worked and the other did not.