Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-15
15
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 501 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 12

Assisted Solution

by:Jamie Garroch
Jamie Garroch earned 498 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 71

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 501 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 71

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 71

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 71

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 71

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
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: …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

722 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