Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Help with Outlook VBA

  1. #1
    Board Regular
    Join Date
    Jul 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with Outlook VBA

    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

  2. #2
    Board Regular
    Join Date
    Jul 2013
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Outlook VBA

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •