Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-15
15
Medium Priority
?
287 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 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
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 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

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!

Question has a verified solution.

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

This article lists the top 5 trialware OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their Exchange server is no longer available or other critical issues with Exchange server or impo…
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

916 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