Help with Outlook VBA

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
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:

Code:
'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:

Code:
'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?

Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top