Sleeper: Rules Wizard Question

I have a rule that invokes a script that opens an excel file and starts another script that does lots of other stuff. The rule is set to run when mails come in meeting specific parameters. My problem is, if I get 2 mails that invoke the script, it attempts to open the same excel file twice, which casues problems.

Is there a way to have the rule wait until the first one finishes processing. I know you can check stop processing rules, but in actuality, the outlook rule is done once the excel file is opened.

Thanks in advance.

Ross

Quote:

---

Originally Posted by rsilberfarb

I have a rule that invokes a script that opens an excel file and starts another script that does lots of other stuff. The rule is set to run when mails come in meeting specific parameters. My problem is, if I get 2 mails that invoke the script, it attempts to open the same excel file twice, which casues problems.

---

Couldn't the script check whether the Excel file is already open?

If I knew how to do that, I wouldn't have asked for help.

Any Ideas?

Quote:

---

Originally Posted by rsilberfarb

If I knew how to do that, I wouldn't have asked for help.

Any Ideas?

---

Hi, :yes

http://www.vbaexpress.com/kb/getarticle.php?kb_id=443
http://www.vbaexpress.com/kb/getarticle.php?kb_id=207
http://www.vbaexpress.com/kb/getarticle.php?kb_id=186

Later..

Thanks! Now how do I tell outlook to wait until the workbook is closed before runing the rule again.

Here is the code:

Code:

---

Const TextFilePath As String = "C:\Documents and Settings\Owner\My Documents\Service Call Logs\FMI Inputs\Text Mail\"
Sub Test(Item As Outlook.MailItem)
Item.SaveAs TextFilePath & Item.Subject & ".txt", olTXT
Shell "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE ""c:\Documents and Settings\Owner\My Documents\Service Call Logs\FMI Inputs\service call data gathering.xls""", 1
End Sub

---

Quote:

---

Originally Posted by rsilberfarb

Thanks! Now how do I tell outlook to wait until the workbook is closed before runing the rule again.

---

Well there's no command for that but perhaps there's a work arround if I can figure out what you're doing!

Your code saves the active mail as txt file and then opens a Excel file with a shell command.

So Tell me..what is this XL file doing? Does it have AutoMacro's doing anything?

If so does that Excel book closes itself after performing that action?

I'll think about your querie meanwhile...:whistle:

Hi there, Ross :hi:

How's this working out?
I had a look at a way around this and it's quite problematic!
The issue here is the use of the SHELL command to open the XL file, since you can't determine it's success.
I was thinking that what you need in your script is a function to open the Excel file and returns a value appropriately. That way you could do loop the code (holding up Outlook from doing anything else, like firing the rule again) until you get the file open. That would mean referencing the Excel object and using WorkBooks.Open

This is fine, but then control won't return to Outlook until your WorkBook_Open code has finished running (which kind of negates the loop/function call thing).
There are also other issues, like control of the Excel object and catering for circumstances, like the WorkBook being left open after the code is run, there being an error while the excel code runs or Excel crashing or being locked by another process.
All a bit messy and there are only two solutions as I see it:

1. run ALL the code currently in Excel from Outlook, so you hold up Outlook from processing anything else and have complete control over the Excel object so you can deal with all situations.

2. Find a way of stacking or polling the new mail events. I'm not aware of a way of setting Outlook to do this but i think you could force the issue creating a Task with a reminder each time you process one of your Mail items (say at 15 minute intervals) and using the BeforeReminderShow event, in a similar way to how you're using the Mail_Item event, to trigger the process code then delete the reminder. (you could use the task's subject or body property to hold the text file path)
This way, you regain control of the timing of your Excel code, seperating it from the Mail item event. This would also keep a handy log of the activity in your tasks list and see how things are progressing by manipulating the task's status property.

Does that help?

Killian,

Thanks for the reply. As you know from the last time you helped me, I need more coaching than that. Either option would be fine, but I do not how to take your suggestions and make them do what you say they can.

Also, I need to be able to save the attachments for certain mails to my computer, but there is no pre-packaged rule to this, any ideas? I have found some solutions, but they seemed more complex then they needed to be.

Thanks in advance!

Ross

Well I think running the code from Outlook shouldn't be too tricky.
You could just try referencing the Excel object model (in Tools>References) replacing the SHELL command with

Code:

---

Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    Dim fFullPath As String
fFullPath = "C:\Documents and Settings\Owner\My Documents\" & _
                "Service Call Logs\FMI Inputs\service call data gathering.xls"
'in the background, create an instance of xl then open the file
    Set xlApp = New Excel.Application
    Set wb = xlApp.Workbooks.Open(fFullPath)
    'the code in the excel file's open event will run
'if not done in the Excel code, you can close
    'the workbook and quit Excel here
    wb.Close
    xlApp.Quit
'tidy up
    Set wb = Nothing
    Set xlApp = Nothing

---

This code is adapted from a KB article which I incidentally just did that deals with your other question about saving attachments.

Its here

Enjoy :thumb

Killian,

Thanks!!

I successfully implemented having outlook run the code to open excell, but I can't get it to save the attachments as referenced in the KB.

Below is the code I have in the ThisOutlookSession, I removed the printing part because I do not need that, but I still can't get it to work.

Please Help,

Ross

Code:

---

Dim WithEvents TargetFolderItems As Items
'set the string constant for the path to save attachments
Const FILE_PATH As String = "C:\Documents and Settings\FMi\My Documents\Service Call Logs\FMI Inputs\Extract"
Const TextFilePath As String = "C:\Documents and Settings\FMi\My Documents\Service Call Logs\FMI Inputs\Text Mail\"
Sub Auto(Item As Outlook.MailItem)
Item.SaveAs TextFilePath & Item.Subject & ".txt", olTXT
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim fFullPath As String
fFullPath = "C:\Documents and Settings\FMi\My Documents\" & _
"Service Call Logs\FMI Inputs\service call data gathering.xls"
'in the background, create an instance of xl then open the file
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(fFullPath)
 'the code in the excel file's open event will run
'if not done in the Excel code, you can close
 'the workbook and quit Excel here
 'tidy up
Set wb = Nothing
Set xlApp = Nothing
End Sub
 
Private Sub Application_Startup()
    'some startup code to set our "event-sensitive" items collection
    Dim ns As Outlook.NameSpace
    Dim TargetFolder As MAPIFolder
    Dim fldr As MAPIFolder, subfldr As MAPIFolder
Set ns = Application.GetNamespace("MAPI")
    For Each fldr In ns.Folders
        For Each subfldr In fldr.Folders
            If subfldr.Name = "Completed Forms" Then
                Set TargetFolderItems = subfldr.Items
                Exit For
            End If
        Next
    Next
    Set ns = Nothing
End Sub
 
'this is the ItemAdd event code

Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
    'when a new item is added to our "watched folder" we can process it
    Dim olAtt As Attachment
    Dim i As Integer
If Item.Attachments.Count > 0 Then
        For i = 1 To Item.Attachments.Count
            Set olAtt = Item.Attachments(i)
            'save the attachment
            olAtt.SaveAsFile FILE_PATH & olAtt.FileName
    End If
Set olAtt = Nothing
End Sub
 
'this is the Application_Quit event code in the ThisOutlookSession module

Private Sub Application_Quit()
Dim ns As Outlook.NameSpace
    Set TargetFolderItems = Nothing
    Set ns = Nothing
   
End Sub

---

Hi Ross, :hi:
The first important question... Is the Item_Add event firing?
You should check this by adding a breakpoint on the first line, running the Application_Start code then dropping a mailitem into your target folder.
Are you getting errors? I noticed there's a missing Next to close the For Each loop - that won't help :whistle:

Code:

---

' this is the ItemAdd event code
Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
'when a new item is added to our "watched folder" we can process it
Dim olAtt As Attachment
Dim i As Integer
If Item.Attachments.Count > 0 Then
        For i = 1 To Item.Attachments.Count
            Set olAtt = Item.Attachments(i)
    'save the attachment
            olAtt.SaveAsFile FILE_PATH & olAtt.FileName
        Next '<---- MISSING NEXT !!!
    End If
Set olAtt = Nothing
End Sub

---

Killian,

I put the breakpoint in, and when a mail comes in to the target folder, the VBA editor opens in debug mode.

I assume this is what we wanted?

Before I put the missing next in, I would get an error. Now it just processes as if it is working, but does not save the file.

Also, now that I have the code that was formerly in excel, in outlook, I am having an additional problem. I have three profiles on the computer that this is running on, before I was able to be working on another profile (winows logon), and still have the rule fire. Now I need to be actively on the logon. Any Ideas?

Thanks Again,

Ross

Hi Ross,
Well the Item_Add event is firing - when it fires with your breakpoint added, the code stops at the breakpoint. You can then step through the code line by line (with F8) and take a look at how things are happening.
I just noticed there's no "\" at the end of the FILE_PATH constant - is it possible that your files are saving, just not where you expected?

Regarding the Outlook code - the module "ThisOutlookSession" is specific to each user, saved in their profile as an OTM file. So the code will have to be reproduced for each user login (it may be possible just to copy the OTM file). This is very much the poor man's version of software distribution but the only other option is to write the code in a IDE such as Visual Basic (5 or 6) or .Net to compile it into an add-in, then install that where required. There's some more info in this thread about the kind of excitement involved in that process.