Solved

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

Posted on 2014-01-21
8
414 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

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.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now