Solved

How to save an Outlook 2010 email with an excel attachment to a folder?

Posted on 2014-01-21
8
419 Views
Last Modified: 2014-02-04
When I get an email in Outlook 2010 with the subject contains the word "TEC" I need to save to a folder.
The VBA code to save an excel file to a folder will be set off by a outlook rule.
The path for the folder is...\\psf\Home\TEC Reports
Thanks,
Jimi
0
Comment
Question by:Jimi Sherman
  • 4
  • 4
8 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39799150
Something like this?

Sub SaveAttachment()
Dim sPath As String, sPathC As String
Dim lPos As Long
Dim MyFile As String
Dim sAmount As String, dAmount As Double
Dim colAttachments As Outlook.Attachments
Dim objAttachment As Outlook.attachment
Dim FolderPath As String

    Dim curmail As Outlook.MailItem, sCat As String
    Set curmail = GetCurrentItem()
      
    Set colAttachments = curmail .Attachments
    
    FolderPath = "\\psf\Home\TEC Reports"
    
For Each objAttachment In colAttachments
    
        MyFile = objAttachment.FileName
        sPath = FolderPath & "\" & MyFile
        objAttachment.SaveAsFile sPath

    Next
   
   curMail.Delete
   
    'Cleanup
    Set objAttachment = Nothing
    Set colAttachments = Nothing
    Set MyOlApplication = Nothing
    Set myOlSelection = Nothing
    Set mySelectedItem = Nothing

End Sub

' From http://www.outlookcode.com/codedetail.aspx?id=50
Function GetCurrentItem() As Object
    Dim objApp As Outlook.Application
         
    Set objApp = CreateObject("Outlook.Application")
    On Error Resume Next
    Select Case TypeName(objApp.ActiveWindow)
        Case "Explorer"
            Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
        Case "Inspector"
            Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
        Case Else
            ' anything else will result in an error, which is
            ' why we have the error handler above
    End Select
     
    Set objApp = Nothing
End Function

Open in new window


Thomas
0
 

Author Comment

by:Jimi Sherman
ID: 39799186
The code stops at...
objAttachment.SaveAsFile sPath

Jimi
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39799910
Does the following path exist?  
\\psf\Home\TEC Reports
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Jimi Sherman
ID: 39800424
Yes
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39800474
Any error message you're getting?
0
 

Author Comment

by:Jimi Sherman
ID: 39800531
No error message it just stops with the debug box opens.
I got it to work when I changed the path to...."C:\Users\Jimi\Documents"
So if you want i can close the question and issues you the points of courses?

Jimi
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39800694
Can you map a drive to your destination? Does it work then?
0
 

Author Comment

by:Jimi Sherman
ID: 39800730
Yes it works then.
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

Find out what you should include to make the best professional email signature for your organization.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 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