Code to download attachment from Outlook, save it on desktop and open it

Question

This is the code in Outlook that sets the rules in Outlook automatically to save the attachment (Excel) with date stamp:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim dateFormat

    dateFormat = Format(Now, "yyyy-mm-dd H-mm")
    saveFolder = "c:\Users\abc1\Desktop\"
   For Each objAtt In itm.Attachments
      objAtt.SaveAsFile saveFolder & "\" & dateFormat & objAtt.DisplayName
      Set objAtt = Nothing
 Next

End Sub

Next step I want is to open the attachment once it's saved. Is that possible?

How about this one?

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
  Dim objAtt As Outlook.Attachment
  Dim saveFolder As String
  Dim dateFormat

      dateFormat = Format(Now, "yyyy-mm-dd H-mm")
      saveFolder = "c:\Users\abc1\Desktop\"
     For Each objAtt In itm.Attachments
        objAtt.SaveAsFile saveFolder & "\" & dateFormat & objAtt.DisplayName
        Set objAtt = Nothing
   Next


Dim Shex As Object
   Set Shex = CreateObject("Shell.Application")
   tgtfile = "objatt"
   Shex.Open (tgtfile)

End Sub

It saves to my desktop and has been working fine. I need to open it on my desktop. Is it possible? — D K, Apr 28 '15 at 17:36
You can use code from here to open any document in its default application: http://stackoverflow.com/questions/18921168/how-can-excel-vba-open-file-using-default-application — Tim Williams, Apr 28 '15 at 18:06
Which one? Create object and then looking for tgtfile? How would I define the doc name - it would be different every time it gets downloaded. — D K, Apr 28 '15 at 19:03
Yes - the `runit` answer with 5 votes. Save the full file path to a variable and pass it to `runit` — Tim Williams, Apr 28 '15 at 19:05
If you need to post code, *edit your question* and add it: it's unreadable in a comment. — Tim Williams, Apr 28 '15 at 19:09

score 1 · Answer 1 · answered Apr 28 '15 at 17:44

1

Yes, it is possible. If you know exactly that you need to open Excel files you may use the Excel object model to get the job done. See How to automate Microsoft Excel from Visual Basic for more information. The Open method of the Workbooks class opens a workbook.

answered Apr 28 '15 at 17:44

Eugene Astafiev

47,483
3
24
45

Create application like excel.application? – D K Apr 28 '15 at 18:05
Yes, exactly. You need to create a new Excel Application instance and then load the file there. – Eugene Astafiev Apr 28 '15 at 18:19
My file would be saved as different named file since it's date/time stamp would be different everytime. – D K Apr 28 '15 at 18:58
But you know the filename at runtime, so you can use it for opening the file in Excel. – Eugene Astafiev Apr 29 '15 at 06:29
Due to the change in date everytime the file will be saved in different name. How would I call it for different file everytime? – D K Apr 29 '15 at 19:14

Tim Williams · Answer 2 · 2015-04-29T22:30:21.473

0

Try this:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)

    Dim objAtt As Outlook.Attachment
    Dim saveFolder As String
    Dim dateFormat, FilePath As String

    dateFormat = Format(Now, "yyyy-mm-dd H-mm")
    saveFolder = "c:\Users\abc1\Desktop" '<<EDIT removed trailing \
    For Each objAtt In itm.Attachments
      FilePath = saveFolder & "\" & dateFormat & _
                  " " & objAtt.DisplayName
      objAtt.SaveAsFile FilePath
      runit FilePath
    Next

End Sub

Sub runit(FilePath as String)
   Dim Shex As Object
   Set Shex = CreateObject("Shell.Application")
   Shex.Open (FilePath)
End Sub

'Edit: I used this to test the code, since I'm not running
'      it from a rule
Sub Tester()

    Dim Msg As MailItem

    Set Msg = Application.ActiveInspector.CurrentItem

    saveAttachtoDisk Msg

End Sub

edited Apr 29 '15 at 22:30

answered Apr 29 '15 at 19:31

Tim Williams

154,628
8
97
125

first time I tried it kicked me out and operation failed..Next trial, it worked.although doesn't run the "runit" code. Since I can set up 1 rule for the incoming emails.. I don't think there's a way to create multiple rules. is there? – D K Apr 29 '15 at 20:19
There was an error in your original code which I hadn't spotted (adding an additional backslash onto `saveFolder`, which already had a trailing backslash). Try it with the fix. – Tim Williams Apr 29 '15 at 20:43
So the code does work with the additional backslash but unfortunately it opens the wrong attachment :) For example I've attached an excel file but it opens an image on the desktop. Haven't seen that before. – D K Apr 29 '15 at 21:22
If your mail item has embedded images then those will also be saved and opened. If you don't want that then you'll need to add some code to filter them out, perhaps by looking at the filename and checking the extension. – Tim Williams Apr 29 '15 at 22:30

Code to download attachment from Outlook, save it on desktop and open it

2 Answers2

Linked