Download outlook attachments with vba according to subject does not work on other computers

Question

The code reads the lines from Excel in column "A", looks for them in Outlook and downloads the attached file to the folder that is in range("E3"), which changes depending on the computer.

But, on my computer it works perfectly, on my colleagues' computer it doesn't download the files.

Sub Descarga()
    Dim it, at      As Variant, t As Long
    Dim olApp       As Outlook.Application
    Dim olNS        As Outlook.Namespace
    Dim olFolder    As Outlook.MAPIFolder
    Dim olItem      As Object
    Dim mailitem    As Outlook.mailitem
    Dim olAtt       As Outlook.Attachment
    
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderInbox)

    For Each it In CreateObject("outlook.application").GetNamespace("MAPI").GetDefaultFolder(6).Items
        For t = 1 To Range("A" & Rows.Count).End(xlUp).Row
            If InStr(it.Subject, Cells(t, 1)) Then
                For Each at In it.Attachments
                    at.SaveAsFile (Range("E3")) & "\" & at.DisplayName        'Range("E3") is the path
                Next
            End If
        Next
    Next

End Sub

Do you mean the code runs without raising any error, but does not save the attachments? Do you test **the same workbook** on both computers in discussion? — FaneDuru, Feb 23 '22 at 14:05
In general, it isn't recommended to iterate over the Folder's Items collection. Using GetFirst(), GetNext() etc or Find() is preferred AFAIK. The difference between you & your colleague may be the number of messages in the Inbox. — DS_London, Feb 23 '22 at 14:08
@DS_London Exactly, there is a difference between the number of files in the inbox. How could I use your method? — Angel Carvajal, Feb 23 '22 at 14:21
This is a partial clarification answer... Does the code run on the other computer without any error but not saving any attachment? If so, try running the code line by line (pressing `F8`, being in VBE) and see if the code reach the iterations. Then, move the cursor over the variables and see if the values you can see is the one you expect... — FaneDuru, Feb 23 '22 at 14:23

Eugene Astafiev · Answer 1 · 2022-02-23T14:43:40.560

First of all, there is no need to create a new Outlook Application class for each item in a folder (even if the new instance is not created, it makes sense to break the long chain of property and method calls for your convenience and clean code):

For Each it In CreateObject("outlook.application").GetNamespace("MAPI").GetDefaultFolder(6).Items

Instead, use the Find/FindNext or Restrict methods to find items that correspond to your conditions with the subject line. Read more about them in the following articles:

If you need to process items from different folder you may also consider using the AdvancedSearch method of the Application class. Read more about this method in the Advanced search in Outlook programmatically: C#, VB.NET article. The key benefits of using the AdvancedSearch method in Outlook are:

The search is performed in another thread. You don’t need to run another thread manually since the AdvancedSearch method runs it automatically in the background.
Possibility to search for any item types: mail, appointment, calendar, notes etc. in any location, i.e. beyond the scope of a certain folder. The Restrict and Find/FindNext methods can be applied to a particular Items collection (see the Items property of the Folder class in Outlook).
Full support for DASL queries (custom properties can be used for searching too). You can read more about this in the Filtering article in MSDN. To improve the search performance, Instant Search keywords can be used if Instant Search is enabled for the store (see the IsInstantSearchEnabled property of the Store class).
You can stop the search process at any moment using the Stop method of the Search class.

Finally, I'd recommend making sure the file path passed to the SaveAsFile method is valid and doesn't contain forbidden symbols:

at.SaveAsFile (Range("E3")) & "\" & at.DisplayName    'Range("E3") is the path

The OP is not creating a new Outlook Application class for each item in a folder - the loop is initialized and the enumerator is retrieved only once before the loop starts. — Dmitry Streblechenko, Feb 23 '22 at 14:36
I didn't check how VBA evaluates such statements, but it makes sense to break it. And check the file path. — Eugene Astafiev, Feb 23 '22 at 14:38
`CreateObject` For Each is creating a new instance if one is not already running — 0m3r, Feb 23 '22 at 15:13
I think everybody knows what `CreateObject ` function is doing. — Eugene Astafiev, Feb 23 '22 at 15:29
Both VBA and .Net would retrieve IEnumVariant interface once before starting the loop. — Dmitry Streblechenko, Feb 23 '22 at 17:09
Yeah, I was thinking about that, but wasn't sure how VBA handles that (unmanaged code). Anyway, it is better to keep a long line broken into separate statements for a better troubleshooting in case of any issues. — Eugene Astafiev, Feb 23 '22 at 17:15

score 0 · Answer 2 · answered Feb 23 '22 at 14:35

0

Firstly, do not use at.DisplayName - use at.FileName.

Secondly, check the attachment type before saving - OOM won't let you save attachments that are, for example, embedded OLE objects, you need to check the attachment type first.

For Each at In it.Attachments
  if at.Type = 1 Then 'olByValue
    at.SaveAsFile (Range("E3")) & "\" & at.FileName 'Range("E3") is the path
  End If
Next

answered Feb 23 '22 at 14:35

Dmitry Streblechenko

62,942
4
53
78

Yeah, I've recommended checking the file path passed to the `SaveAsFile` method. – Eugene Astafiev Feb 23 '22 at 14:39

Download outlook attachments with vba according to subject does not work on other computers

2 Answers2