Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Save Outlook 2010 Attachment to Excel file

Posted on 2013-01-03
14
Medium Priority
?
658 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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
 
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 2000 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

885 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