Solved

Save Outlook 2010 Attachment to Excel file

Posted on 2013-01-03
14
646 Views
Last Modified: 2013-02-02
I want to save an email sent to me weekly in a shared folder.

The email contains an excel table and it is also zipped.
0
Comment
Question by:scotch1989
  • 6
  • 6
14 Comments
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 38741403
Sub SaveAttachments()
    Dim myOlapp As Outlook.Application
    Dim myNameSpace As Outlook.Namespace
    Dim myFolder As Outlook.MAPIFolder
    Dim myItem As Outlook.MailItem
    Dim myAttachment As Outlook.Attachment
    Dim I As Long
     
    Set myOlapp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlapp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myFolder = myFolder.Folders("Broker Pay")
     
    For Each myItem In myFolder.Items
        If myItem.Attachments.Count <> 0 Then
            For Each myAttachment In myItem.Attachments
                I = I + 1
                myAttachment.SaveAsFile "C:\Users\user\Desktop\Sample\" & myAttachment.DisplayName & ".zip"
            Next
        End If
         
    Next
End Sub

Open in new window


Change these portions:

Create an outlook folder and name it. Replace the name in the line below (among the code above)

Set myFolder = myFolder.Folders("Broker Pay")

Change where you want to save your files. Replace this portion in the code above:

                myAttachment.SaveAsFile "C:\Users\user\Desktop\Sample\" & myAttachment.DisplayName & ".zip"

Also you need to enable Microsoft Outlook objects from Reference Library
0
 

Author Comment

by:scotch1989
ID: 38744565
Thanks for the solution.

I'm getting an error though. ' An object couldn't be found'.

Why do you think the error is about?
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38744608
Ah, you havent done the last step.

To enable library

on your VBA editor, click Tools -> References
Check
'Microsoft Office ## Object Library' (## denotes number)
'Microsoft Excel ## Object Library' (## denotes number)
'Microsoft Outlook ## Object Library' (## denotes number)
0
 

Author Comment

by:scotch1989
ID: 38744658
I'm still getting the error.

Set myFolder = myFolder.Folders("Inbox")  ------ Is this right?
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38744665
Looks right. But you see the problem with this code is that it saves all files as 'zip' files.

So its highly recommended that you create a different folder for these weekly emails (use a rule) and then set that folder in the above code.

Try sending yourself an email with a zip file. Then put that email in the folder you created and run the code.
0
 

Author Comment

by:scotch1989
ID: 38744685
How do I run the codes as a script?
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.

 
LVL 13

Expert Comment

by:Shanan212
ID: 38744694
I dont get what you mean. You mean from a button? Since this is going to be inside excel, it has to be run from Excel though.

Is it working?
0
 

Author Comment

by:scotch1989
ID: 38744702
Sorry, now I'm confused.

I have to run through excel and not from Outlook?
0
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 500 total points
ID: 38744731
Ah! My original understanding was that you are going to run this from Excel. if you are running from Outlook, then go down in this article. You can see how to assign a macro to your tool bar

Figure 3. Assigning a macro to the Quick Access Toolbar

http://msdn.microsoft.com/en-us/library/office/ee814736(v=office.14).aspx
0
 

Author Comment

by:scotch1989
ID: 38744739
I think I'm confuing you too.

This is my question.

The code that you provided, how do I run it? The code is for Outlook 2010 right?

----
This is what I'm doing.
I'm running the code in Outlook by clicking the macro button.
0
 

Author Comment

by:scotch1989
ID: 38744757
oh ok. Thanks :)
0
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 500 total points
ID: 38744763
- Well you can simply press F5 from VBA window
- Assign the macro it to your quick access tool bar of outlook so that it acts as a button

This is from above link I provided

To create a button for a macro on the Quick Access Toolbar

1.
Click the File tab.

2.
Click Options to open the Outlook Options dialog box, and then click Quick Access Toolbar.

3.
In the list under Choose commands from:, choose Macros. Find the text that is similar to Project1.Module1.Test in the list that appears and select that text.

4.
Click the Add >> button to add the macro to the list on the right side, and then click the Modify… button to select a button image to associate with the macro.

5.
Click OK. You should see your new button on the Quick Access Toolbar above the File tab.


Now you can quickly run your macro at any time without using the Developer tab—give it a try.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
On some Windows 7 (SP1) computers, Windows Update becomes super slow even the computer is reasonably fast.  There's one solution that seemed to have worked well for me (after trying a few other suggested solutions).
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

895 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

16 Experts available now in Live!

Get 1:1 Help Now