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

x
?
Solved

Outlook VBA to open attachment

Posted on 2011-02-21
7
Medium Priority
?
1,238 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:hendrkle
[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
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:shahzadbux
ID: 34944133
Can you not get your BI system to save the excel report in a shared directory?
0
 

Author Comment

by:hendrkle
ID: 34944217
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....
0
 
LVL 10

Expert Comment

by:shahzadbux
ID: 34944446
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...
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

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:hendrkle
ID: 34944558
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!
0
 
LVL 10

Expert Comment

by:shahzadbux
ID: 34944614
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...
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

Open in new window

0
 
LVL 10

Accepted Solution

by:
shahzadbux earned 2000 total points
ID: 34944642
This will now open the workbook in full screen mode!

Let me know if you get stuck with any of the steps...
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
        Set oWB = oExcel.Workbooks.Open(sPathName & .Item(iCtr).FileName)
        oExcel.Windows(.Item(iCtr).FileName).Activate
        oExcel.DisplayFullScreen = True
        oExcel.Visible = True
        Next iCtr
    End If

End With
    
    Set olMail = Nothing
    Set olNS = Nothing
End Sub

Open in new window

0
 

Author Comment

by:hendrkle
ID: 34944703
At the speed of light!

I will test as soon as I can!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 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