hendrkle
asked on
Outlook VBA to open attachment
Dear Experts,
My company has a dedicated PC hooked up to a TV Screen. I have set up outlook on this machine with a email address to which our BI system will automatically send an Excel attachment every day.
What I would like is for some sort of VBA code in either Excel or Outlook that will check once a day at a certain time if outlook has received a mail from a specific recipient and with a specific subject (which is my automated email and hence there will be multiple instances of the "same" email - just from different dates).
When it has found this email, it should open the Excel document and switch to Excel and turn Excel to Fullscreen (Excel 2007).
The next time the macro runs, it will close the current open excel file and do the same thing again with the latest received email (which adheres to the sender/subject conditions).
Your assistance is much appreciated, as always!
My company has a dedicated PC hooked up to a TV Screen. I have set up outlook on this machine with a email address to which our BI system will automatically send an Excel attachment every day.
What I would like is for some sort of VBA code in either Excel or Outlook that will check once a day at a certain time if outlook has received a mail from a specific recipient and with a specific subject (which is my automated email and hence there will be multiple instances of the "same" email - just from different dates).
When it has found this email, it should open the Excel document and switch to Excel and turn Excel to Fullscreen (Excel 2007).
The next time the macro runs, it will close the current open excel file and do the same thing again with the latest received email (which adheres to the sender/subject conditions).
Your assistance is much appreciated, as always!
Can you not get your BI system to save the excel report in a shared directory?
ASKER
I wish, for a number of vague reasons my company has disabled this feature, and to be honest, it's not going to happen...
Perhaps, a possible solution to that would be another macro for Outlook to save the incoming attachment locally, then another in Excel to fetch the latest version stored in that folder,by creation date?
I'm just making assumptions here, I wouldn't know how to achieve this....
Perhaps, a possible solution to that would be another macro for Outlook to save the incoming attachment locally, then another in Excel to fetch the latest version stored in that folder,by creation date?
I'm just making assumptions here, I wouldn't know how to achieve this....
Ok, just asking the question as that would eliminate one extra step and hence point of failure.
I have attached code which will save attachments to a specified directory, you'll need to copy\paste this into 'this outlooksession' - do you know where to do this?
Next, create a rule specifying the the sender\subject you want the macro to fire and then on the next step where you say what to happen select run a script (near the bottom) and you'll see this macro name - select it and test, you should see the file saved to your directory..
give this a go and see if it works for you, if it does then all we'll need to do is tell the macro to open the attachment - not sure about full screen but shouldn't be too difficult...
I have attached code which will save attachments to a specified directory, you'll need to copy\paste this into 'this outlooksession' - do you know where to do this?
Next, create a rule specifying the the sender\subject you want the macro to fire and then on the next step where you say what to happen select run a script (near the bottom) and you'll see this macro name - select it and test, you should see the file saved to your directory..
give this a go and see if it works for you, if it does then all we'll need to do is tell the macro to open the attachment - not sure about full screen but shouldn't be too difficult...
Sub RunAScriptRuleRoutine(MyMail As MailItem)
Dim strID As String
Dim olNS As Outlook.NameSpace
Dim olMail As Outlook.MailItem
strID = MyMail.EntryID
Set olNS = Application.GetNamespace("MAPI")
Set olMail = olNS.GetItemFromID(strID)
'change this to your directory and include the \ at the end
sPathName = "c:\attachments\"
With olMail.Attachments
iAttachCnt = .Count
If iAttachCnt > 0 Then
For iCtr = 1 To iAttachCnt
.Item(iCtr).SaveAsFile sPathName & .Item(iCtr).FileName
Next iCtr
End If
End With
Set olMail = Nothing
Set olNS = Nothing
End Sub
ASKER
Thank your for your quick reply! I'm afraid I'll have to come back to you a bit later before I can test it, but definitely will soon, thank tyou again!
Use the code below to test, it should also open the excel file
you will need to add a reference to excel - open vba editor, tools, reference...
you will need to add a reference to excel - open vba editor, tools, reference...
Sub RunAScriptRuleRoutine(MyMail As MailItem)
Dim strID As String
Dim olNS As Outlook.NameSpace
Dim olMail As Outlook.MailItem
Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
strID = MyMail.EntryID
Set olNS = Application.GetNamespace("MAPI")
Set olMail = olNS.GetItemFromID(strID)
'change to your path and keep the \ at the end
sPathName = "c:\attachments\"
With olMail.Attachments
iAttachCnt = .Count
If iAttachCnt > 0 Then
For iCtr = 1 To iAttachCnt
.Item(iCtr).SaveAsFile sPathName & .Item(iCtr).FileName
MsgBox sPathName & .Item(iCtr).FileName
Set oWB = oExcel.Workbooks.Open(sPathName & .Item(iCtr).FileName)
oExcel.Windows(.Item(iCtr).FileName).Activate
oExcel.Visible = True
Next iCtr
End If
End With
Set olMail = Nothing
Set olNS = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
At the speed of light!
I will test as soon as I can!
I will test as soon as I can!