Solved

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

Posted on 2015-01-15
15
281 Views
Last Modified: 2015-01-29
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
Comment
Question by:Jay Williams
15 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 167 total points
ID: 40551396
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
 
LVL 11

Assisted Solution

by:Jamie Garroch
Jamie Garroch earned 166 total points
ID: 40552990
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
 

Author Comment

by:Jay Williams
ID: 40553183
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

Expert Comment

by:Qlemo
ID: 40553210
You need to either execute the Startup macro manually, or restart Outlook, to initiate the event trigger definition,
0
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 167 total points
ID: 40553331
Have you checked your security settings and verified that macros are enabled?
0
 

Author Comment

by:Jay Williams
ID: 40553668
This was definitely service with a smile! Thanks to Jamie, too for pointing me toward security.  Hats off!
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40564198
If the code I provided does not work, it is most likely because I chose the wrong mailbox folder. Please check that.
0
 

Author Comment

by:Jay Williams
ID: 40564466
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40564589
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
 

Author Comment

by:Jay Williams
ID: 40564646
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40564666
Are there mails to process on startup?
Does it work if you copy (!) a mail into your inbox?
0
 

Author Comment

by:Jay Williams
ID: 40564769
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40566049
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
 

Author Comment

by:Jay Williams
ID: 40577559
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question