Ever been here?

[Bird]

I am looking to open one of my workbooks (WB1) when an e-mail with “JJJ”
in the subject is received. WB1 will use a second workbook (WB2),
attached to the e-mail, as input. So basically what I need to know how
to do is:
1) Automatically open an .xls file received in an e-mail.
2) Open a different .xls file in a specific folder when the e-mail has
“JJJ” in the subject.

Anyone ever been here?
Thank you!

 

[Guest]

Greetings Bird.

This question actually should go to Outlook programming, since the job has
to be done there, but here goes:

You have to create macro to Outlook to automatically open the attached excel
file when the mail arrives. Add the following code to the "ThisOutlookSession"
Note that the public withevents definition has to be first line (above code).

NOTE!! This macro is extremely dangerous for your security, since it will
open up the excel sheet attachement immediately when received. I suggest you
change this system around (run excel macro to save all "JJJ" mail
attachments) and run the macro yourself when the updating is necessary.

What this macro does:
1. We define Outlook Items object with events to trap some outlook events to
our macro.
2. We define "Autoexec" using the Outlooks MAPILogonComplete event.
3. We define Outlooks ItemAdd event handler for the defined Outlook Items
object (inbox)
4. We check each mail for subject "JJJ" and save the first attachment to
C:\Temp
5. We fire Excel and open the saved file in excel.

That's all folks
---8<---
Public WithEvents inbox As Outlook.Items
Private Sub Application_MAPILogonComplete()
Set inbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
Set inbox = GetNamespace("MAPI").Folders("Personal
Folders").Folders("Inbox").Items
End Sub
Private Sub inbox_ItemAdd(ByVal Item As Object)
Dim mItem As MailItem
Dim sItem As Object
Dim objExcel As Object
If TypeName(Item) = "MailItem" Then
Set mItem = Item
If mItem.Subject = "JJJ" Then
mItem.Attachments.Item(1).SaveAsFile ("C:\Temp\" &
mItem.Attachments.Item(1).DisplayName)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.WorkBooks.Open ("C:\Temp\" &
mItem.Attachments.Item(1).DisplayName)
End If
End If
End Sub
---8<---
P.S.
This code is missing the second workbook "WB2", but opening the second
workbook is simply second objExcelWorkbooks.Open("WB2") in the macro.
- Mr_Mani

 

[Bird]

Mr_Mani

On its face, this seems to be exactly what I am looking for. I
appreciate your time, consideration, and know-how.

As to the question of security, this may be the workaround, I think?

The situation is that I receive a file from London between 3am 7am
(while I am still sleeping). I would like to open WB2, which will then
reference and open the WB1 received. The only modification to my
original intent is that with your code, I will save WB1, and then run
WB2.

If you still see security concerns with the revised proves, please let
me know!

----------------------------------------------------------------------------------------------------

Public WithEvents inbox As Outlook.Items

Private Sub Application_MAPILogonComplete()
Set inbox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbo
x).Items
Set inbox =
GetNamespace("MAPI").Folders("PersonalFolders").Folders("Inbox").Items
End Sub

Private Sub inbox_ItemAdd(ByVal Item As Object)
Dim mItem As MailItem
Dim sItem As Object
Dim objExcel As Object
If TypeName(Item) = "MailItem" Then
Set mItem = Item
If mItem.Subject = "JJJ" Then
mItem.Attachments.Item(1).SaveAsFile ("C:\Temp\"
&mItem.Attachments.Item(1).DisplayName)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.WorkBooks.Open ("C:\Temp\"
&mItem.Attachments.Item(1).DisplayName)
objExcelWorkbooks.Open("C:\Workbooks\WB2.xls")
End If
End If
End Sub

 

[Bird]

Mr_Mani,

I have encountered some problems during the implementation you
original code, please see below. First problem was with th
“ThisOutlookSession” statement, and then just for laughs
commented-out that code and had a problem with “WithEvents”. I woul
appreciate any assistance you could give me on this. Again thank you,

Bird

Option Explicit
ThisOutlookSession ‘ *-Compile error: Invalid outsid
procedure-*

Public WithEvents inbox As Outlook.Items ‘ *-Compile error
Only valid in object module-*
Private Sub Application_MAPILogonComplete()
Set inbox
GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
Set inbox
GetNamespace("MAPI").Folders("PersonalFolders").Folders("Inbox").Items
End Sub

Private Sub inbox_ItemAdd(ByVal Item As Object)
Dim mItem As MailItem
Dim sItem As Object
Dim objExcel As Object
If TypeName(Item) = "MailItem" Then
Set mItem = Item
If mItem.Subject = "JJJ" Then
mItem.Attachments.Item(1).SaveAsFile ("C:\Temp\" _
& mItem.Attachments.Item(1).DisplayName)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.WorkBooks.Open ("C:\Temp\"
mItem.Attachments.Item(1).DisplayName)
End If
End If
End Su

 

[Guest]

Greetings Bird,

Public WithEvents inbox As Outlook.Items ‘ *-Compile error:
Only valid in object module-*

Where did you put the code.
I put the code to the Outlooks "ThisOutlookSession" class.
From outlook Tools/Macro/Visual Basic Editor
From the visual basic editor project tree view I select the ThisOutlookSession

Hope this helps some.

BR, Mani