Solved

Save Outlook 2010 Attachment to Excel file

Posted on 2013-01-03
14
638 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

705 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

13 Experts available now in Live!

Get 1:1 Help Now