Solved

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

Posted on 2015-01-15
15
279 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 10

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
If you don't know how to downgrade, my instructions below should be helpful.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
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…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now