• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

How do I get this code to fire when email comes in?

This code works, but only on start up. I need it to work when email comes to the specified inbox.  I pasted this code together, and I'm pretty sure there's junk in there at the top I don't need. Suggestions?

Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim oMail As Outlook.MailItem
    Dim myNameSpace As Outlook.NameSpace
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")
    Set myNameSpace = Application.GetNamespace("MAPI")

On Error GoTo notfoundFolder
    Set myItems = myNameSpace.Folders("XE_IPP").Folders("Inbox").Items
    Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
    For Each oMail In myItems
        If TypeName(oMail) = "MailItem" Then
            If oMail.Subject = "IPP Share Request" And LCase(Right(oMail.Attachments.item(1).FileName, 5)) = ".xlsm" Then
                   oMail.Attachments.item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\New Requests\" & oMail.Attachments.item(1).FileName
                   Set moveFolder = myNameSpace.Folders("XE_IPP").Folders("Inbox").Folders("Requests")
                   oMail.Move moveFolder
                   
                    Dim accApp As Access.Application
                    Set accApp = New Access.Application
                    'This is where I want Access to run invisible
                    accApp.openCurrentDatabase ("G:\XE_ECMs\IPP Sharing Development\Processing.accdb")
                    accApp.Run ("RequestsTurnaround")
                    accApp.Quit
            End If
        End If
    Next
        
    On Error GoTo 0

    Exit Sub

notfoundFolder:
    End
End Sub

Open in new window


Also, inside the loop, Access runs, but would prefer that it be invisible. Suggestions?

Thanks for the help!
0
Jay Williams
Asked:
Jay Williams
3 Solutions
 
QlemoDeveloperCommented:
This the cleaned up code, doing exactly the same (going thru the folder on Outlook start), and whenever an item is put into the default Inbox.
Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()

    Set Items = Outlook.Session.GetDefaultFolder(olFolderInbox).Items

    For Each oMail In Outlook.Session.Folders("XE_IPP").Folders("Inbox").Items
	Items_ItemAdd(oMail)
    Next

End Sub

Private Sub Items_ItemAdd(ByVal Item as object)
On Error GoTo notfoundFolder
    Dim accApp As Access.Application

    If TypeName(Item) = "MailItem" Then
        If Item.Subject = "IPP Share Request" And LCase(Right(Item.Attachments.item(1).FileName, 5)) = ".xlsm" Then
            Item.Attachments.item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\New Requests\" & Item.Attachments.item(1).FileName
            oMail.Move Outlook.Session.Folders("XE_IPP").Folders("Inbox").Folders("Requests")

            Set accApp = New Access.Application
            'This is where I want Access to run invisible
            accApp.openCurrentDatabase ("G:\XE_ECMs\IPP Sharing Development\Processing.accdb")
            accApp.Run ("RequestsTurnaround")
            accApp.Quit
        End If
    End If

    On Error GoTo 0
notfoundFolder:
End Sub

Open in new window

The sub name Items_ItemAdd is fixed: Items is your WithEvents variable, and ItemAdd is the event name to handle - whenever an item is added to that folder.
0
 
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
I run a VBA script in Outlook whenever an email arrives in the inbox and I use a rule to do that.

Add a rule with the conditions you need and set the action to Run the Project1.ThisOutlookSession.VBAProject script.
0
 
Jay WilliamsOwnerAuthor Commented:
Qlemo and Jamie, Thanks for your help. I pasted in the code but it does not fire on event.

I will try again setting a rule to run it, but this is a shared folder that I don't own, so we'll see if I can.  That's the reason I need it to run on event.
0
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
QlemoDeveloperCommented:
You need to either execute the Startup macro manually, or restart Outlook, to initiate the event trigger definition,
0
 
David LeeCommented:
Have you checked your security settings and verified that macros are enabled?
0
 
Jay WilliamsOwnerAuthor Commented:
This was definitely service with a smile! Thanks to Jamie, too for pointing me toward security.  Hats off!
0
 
QlemoDeveloperCommented:
If the code I provided does not work, it is most likely because I chose the wrong mailbox folder. Please check that.
0
 
Jay WilliamsOwnerAuthor Commented:
Qlemo, I'm holding both sets of code up side by side, and they are very different, but I'm too green to understand what they say or how to reconcile them.  Here they are (attached) side by side.
CompareCode.jpg
0
 
QlemoDeveloperCommented:
My code listens to additions to your default Inbox (line 5), and processes "XE_IPP\Inbox" on startup (line 7).
The original code does the same (see lines 13 and 12).
But maybe my code contains an error I cannot spot, and hence the error catching stuff snaps in, so nothing is done. Remove my line 14 for test.
0
 
Jay WilliamsOwnerAuthor Commented:
I know I'm being difficult.  Sorry.  Line 14 on your code is a blank line.  I removed the error handler with no effect.
0
 
QlemoDeveloperCommented:
Are there mails to process on startup?
Does it work if you copy (!) a mail into your inbox?
0
 
Jay WilliamsOwnerAuthor Commented:
It does not work on startup with mail in the inbox.  By "copy" do you mean "paste in" or an email cc to that inbox?

I also checked and tested to see that If Item.Subject does in fact = "IPP Share Request" And LCase(Right(Item.Attachments.Item(1).FileName, 5)) = ".xlsm"--and it does.
0
 
QlemoDeveloperCommented:
Dropping a mail (like a file) into the monitored folder is sufficient to trigger that event code.
For debugging, please set a break point in the event trigger code, best in line 17 of http:#a40551396 code.
0
 
Jay WilliamsOwnerAuthor Commented:
This isn't likely to be resolved here, yet.  We have internal security/permissions issues that are keeping me from a solution.  I'll keep it on the back burner until we get a break.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now