dcsimg
Results 1 to 13 of 13

Thread: [RESOLVED] Extract Attachment from Email Crashes

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Resolved [RESOLVED] Extract Attachment from Email Crashes

    Found some code online; saw it working via video but for some reason mine won't work. Not sure if there's a syntax mistake in code or a setting that perhaps is causing it to crash on this line.. ?

    Goal is to extract a copy of attachments from emails within a specific Outlook folder and dump it into a designated file explorer table to compile batches for development.

    Error: Runtime error '-2147221233(800401of)' The attempted operation failed. An object could not be found.

    Pic where crashes below/code further down for copy/paste/editing:
    Name:  vba-extract-email-attachment-fail.jpg
Views: 185
Size:  24.7 KB

    Code:
    Code:
    Option Explicit
    
    Private Sub ExtractEmails_Click()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim OlItems As Object
    Dim OlFolder As Object
    Dim j As Integer
    Dim strFolder As String
    
    'On Error Resume Next
    Set OlApp = GetObject(, "Outlook.Application")
    
    If Err.Number = 429 Then
        Set OlApp = CreateObject("Outlook.Application")
    End If
    
    strFolder = ThisWorkbook.Path & "\Extract"  'change Extract to the folder name where you would like to place attachments
    
    Set OlFolder = OlApp.getnamespace("MAPI").Folders("first.last@companyname.com").Folders("Inbox").Folders("atest")
    'change the above to the name of your main Inbox and any sub folders to drill down to --
    Set OlItems = OlFolder.Items
    
    For Each OlMail In OlItems
        If OlMail.Attachments.Count > 0 Then
            For j = 1 To OlMail.Attachments.Count
                OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).Filename
            Next j
        End If
    Next
    
    Set OlFolder = Nothing
    Set OlItems = Nothing
    Set OlMail = Nothing
    Set OlApp = Nothing
    
    MsgBox "Done", vbInformation
    
    End Sub
    (my first name.lastname is normally in the script; only removed it for this image)

  2. #2
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    790

    Re: Extract Attachment from Email Crashes

    Complete shot in the dark since I've never written any code to do what you are doing, but the Item() array might be 0 based, so it might be worth trying replacing Item(j) with Item(j-1) in the two places where the Item array is referenced in that highlighted line of code.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Question Re: Extract Attachment from Email Crashes

    Quote Originally Posted by OptionBase1 View Post
    Complete shot in the dark since I've never written any code to do what you are doing, but the Item() array might be 0 based, so it might be worth trying replacing Item(j) with Item(j-1) in the two places where the Item array is referenced in that highlighted line of code.
    Thanks for idea, I entered a 'dash 1' in the 2 areas where parens where present.. it automatically added that extra spacing... Was not sure if the other "j" references needed adjusted or not... Tried to do the first one but turned red - so I'll post this so you can see the 2 red areas I adjusted and the others refs not touched.
    Tried running code, got diff error..?? Runtime Error 440

    Name:  vba-extract-email-attachment-fail002.jpg
Views: 89
Size:  27.4 KB
    Last edited by ChrisOK; Aug 9th, 2019 at 11:00 PM. Reason: added specific error code generated

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,442

    Re: Extract Attachment from Email Crashes

    j is fine, no need to alter
    i pasted your code directly into outlook vba

    i had to edit the application object and mail folder, use a fixed existing path rather that the workbook path,
    none of which should make any difference to the result,
    other than that your code worked correctly without error

    if there was any error to any of your objects the code would have not got to the point where the error is happening

    if the target folder does not exist then a different error would occur,
    make sure you have full write access to the target folder and that it is not a windows protected folder

    does the error occur on all emails or just one or a few?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Question Re: Extract Attachment from Email Crashes

    Quote Originally Posted by westconn1 View Post
    j is fine, no need to alter
    i pasted your code directly into outlook vba

    make sure you have full write access to the target folder and that it is not a windows protected folder
    does the error occur on all emails or just one or a few?
    Answers: I do have full read/write access to all folders on this laptop; was testing it on a folder with only a couple emails in it holding attachments.

    I was using an outside method (using Excel file with button to call the code) in the event I needed to send it to a VM virtual machine area that I must do some work from - but now that I think about it, can't use it in that area anyway because the Outlook there is strictly web-browser based where the VBA won't be accessible via the normal Alt+F11 route.

    Anyway, using it here to perform the attachment extraction is the primary goal:
    You intrigued me to try it in my Desktop based Outlook; however, I'm not an expert at VBA and am not sure how to adjust this code to make it work within the Outlook realm.
    When I ran it, it errored out n the "ThisWorkbook.Path" area (for the obvious reason it's no longer within a workbook)

    Could you or someone please assist in updating the below to work within Outlook?

    Code:
    Option Explicit
    Private Sub ExtractEmails_Click()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim OlItems As Object
    Dim OlFolder As Object
    Dim j As Integer
    Dim strFolder As String
    
    'On Error Resume Next
    Set OlApp = GetObject(, "Outlook.Application")
    
    If Err.Number = 429 Then
        Set OlApp = CreateObject("Outlook.Application")
    End If
    
    strFolder = ThisWorkbook.Path & "\Extract"  'change Extract to the folder name where you would like to place attachments
    
    Set OlFolder = OlApp.GetNamespace("MAPI").Folders("firstname.lastname@company.com").Folders("Inbox").Folders("atest")
    'change the above to the name of your main Inbox and any sub folders to drill down to --
    Set OlItems = OlFolder.Items
    
    For Each OlMail In OlItems
        If OlMail.Attachments.Count > 0 Then
            For j = 1 To OlMail.Attachments.Count
                OlMail.Attachments.Item(j - 1).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j - 1).FileName
            Next j
        End If
    Next
    
    Set OlFolder = Nothing
    Set OlItems = Nothing
    Set OlMail = Nothing
    Set OlApp = Nothing
    
    MsgBox "Done", vbInformation
    
    End Sub

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,442

    Re: Extract Attachment from Email Crashes

    just change strfolder to any folder you like on your local hdd
    Code:
    strFolder = "c:\temp\extract"
    you also do not need to create an application object, if you are working within outlook, full code as i used below, you will just have to change the outlook folder, i was just using the inbox, and the path to save to

    there is no reason why the code should not work just as well from excel, but the question is if can save from web based outlook to a local hdd, which i have never tried
    it obviously must have found a folder to create a folder object, an items object and an attachment object, else it could not have gotten to where the error occurred, then could not save the attachment for some reason

    'Set OlFolder = OlApp.GetNamespace("MAPI").Folders("first.last@companyname.com").Folders("Inbox").Folders("atest")
    i am not sure that this folder assignment is correct and can not test, without a similar folder tree, i would think it should be more like
    Code:
    Set OlFolder = OlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)Folders("first.last@companyname.com").Folders("atest")
    the outlook constant (olfolderinbox) would not be valid in excel using latebinding of outlook and should be assigned or use the literal value

    Code:
    Private Sub ExtractEmails_Click()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim OlItems As Object
    Dim OlFolder As Object
    Dim j As Integer
    Dim strFolder As String
    
    'strFolder = ThisWorkbook.Path & "\Extract"  'change Extract to the folder name where you would like to place attachments
    strFolder = "c:\temp\extract"
    'Set OlFolder = OlApp.GetNamespace("MAPI").Folders("first.last@companyname.com").Folders("Inbox").Folders("atest")
    'change the above to the name of your main Inbox and any sub folders to drill down to --
    Set OlFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    
    Set OlItems = OlFolder.Items
    
    For Each OlMail In OlItems
        If OlMail.Attachments.Count > 0 Then
            For j = 1 To OlMail.Attachments.Count
                OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).FileName
            Next j
        End If
    Next
    
    Set OlFolder = Nothing
    Set OlItems = Nothing
    Set OlMail = Nothing
    
    MsgBox "Done", vbInformation
    
    End Sub
    on googling, microsoft seems to think the error you were getting is to do with a corrupt outlook profile, but i am not sure i would believe that is your problem
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Re: Extract Attachment from Email Crashes

    Ok, forget about the VM area and the browser-based, won't be using that -- just the desktop based Outlook.
    I copied/pasted that large batch of code above (where you made edits) and pasted it into the "ThisOutlookSession" window..
    Is that the correct place to put it? Or should it be placed within it's own Module? (I tried both and had probs) -wanted to clarify where it should go?
    Ran it and the 4th line in this section of code was highlighted in yellow:

    Code:
    For Each OlMail In OlItems
        If OlMail.Attachments.Count > 0 Then
            For j = 1 To OlMail.Attachments.Count
                OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).FileName
            Next j
        End If
    Next
    Am I supposed to substitute any of these parts w/ other specifics or should it run as it is with the generic references?
    Based on the error, it appears it's looking for more specifics in either the file path from which to grab the attachments --
    or the filepath for which the attachments should be pasted into after extraction...?
    The error I get when the above 4th line is highlighted is:
    Run time error: -2137024893 (80070003) "Cannot save the attachment. Path does not exist. Verify the path is correct.

    The location of the folder that I would like to extract attachments from email is:
    TOP LEVEL is myfirstname.lastname@companyname.com
    2nd LEVEL is INBOX
    3rd LEVEL is atest

    (there's only 2 emails in that "atest" folder and both have an attachment

    The location of where to place the extracted attachments can be anywhere at this point.. how about:
    DESKTOP > Attachments

    From there I can zip and move them into the VM virtual machine area for developers to combine and run reconciliations on..
    Thanks greatly for your help in trying to get this working...

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Question Re: Extract Attachment from Email Crashes

    Ok, forget about the VM area and the browser-based, won't be using that -- just the desktop based Outlook.
    I copied/pasted that large batch of code above (where you made edits) and pasted it into the "ThisOutlookSession" window..
    Is that the correct place to put it? Or should it be placed within it's own Module? (I tried both and had probs) -wanted to clarify where it should go?
    Ran it and the 4th line in this section of code was highlighted in yellow:

    Code:
    For Each OlMail In OlItems
        If OlMail.Attachments.Count > 0 Then
            For j = 1 To OlMail.Attachments.Count
                OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).FileName
            Next j
        End If
    Next
    Am I supposed to substitute any of these parts w/ other specifics or should it run as it is with the generic references?
    Based on the error, it appears it's looking for more specifics in either the file path from which to grab the attachments --
    or the filepath for which the attachments should be pasted into after extraction...?
    The error I get when the above 4th line is highlighted is:
    Run time error: -2137024893 (80070003) "Cannot save the attachment. Path does not exist. Verify the path is correct.

    The location of the folder that I would like to extract attachments from email is:
    TOP LEVEL is myfirstname.lastname@companyname.com
    2nd LEVEL is INBOX
    3rd LEVEL is atest

    (there's only 2 emails in that "atest" folder and both have an attachment

    The location of where to place the extracted attachments can be anywhere at this point.. how about:
    DESKTOP > Attachments

    From there I can zip and move them into the VM virtual machine area for developers to combine and run reconciliations on..
    Thanks greatly for your help in trying to get this working...

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,442

    Re: Extract Attachment from Email Crashes

    (I tried both and had probs) -wanted to clarify where it should go?
    either should work fine, if you have a lot of code in thisoutlooksession, probably better to put in a separate module, but it should not matter

    as you are now getting a different error, that gives a specific message, path does not exist, you should be able to confirm if it is correct, or if the value assigned to strfolder (which i can not see) does exist and is valid
    also make sure the attachment filename is a valid file for the windows filesystem

    i have very limited emails in my inbox but all the attachments were saved without error, no error occurred if the filename already existed, it would just overwrite the file

    just in case, do a test to make sure you can save the attachments manually
    you could also do a test to make sure the emails are the ones you want in your atest folder, but it should not matter anyway, just save the wrong attachments, but no error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Question Re: Extract Attachment from Email Crashes

    I have no other code in ThisOutlookSession (only what you see here) -- I've never really used VBA within Outlook before so this is a 1st for me..

    I'm getting a diff error I assumed because previously, we were attempting to run it from EXCEL and now we've moved the code into OUTLOOK (but should it be in ThisOutlookSession or in something like Module1?

    You mentioned: "you should be able to confirm if it is correct," How? What steps would I take or where would I look?

    You mentioned: "or if the value assigned to strfolder (which i can not see) does exist and is valid" - How? where would you go/should I go to see if it exists or is valid?

    You mentioned: "also make sure the attachment filename is a valid file for the windows filesystem" - How, where do I go to verify this? Are you meaning this in a literal sense as in an attachment actually being called "filename"? If you can provide some specifics or maybe screen shots of what you mean/where to look, I will chase down the answers right away..

    Can you expand upon what exactly this line means? (where the error is occurring)
    Code:
    OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).FileName
    I think this LOCALS WINDOW might be where you may say to look at the strFolder path... and that's not what I intended for sure!
    The path I noted was essentially the hierarchy of the Outlook folders... (mentioned in first post)
    This path is referring to a folder on the C: drive!? What should "strFolder" be referring to? the place where we WANT attachments to be copied to -- once extracted from emails -- or does strFolder mean something else?
    (SEE PIC)Name:  vba-extract-email-attachment-fail004.jpg
Views: 33
Size:  31.3 KB

    Thank you
    Last edited by ChrisOK; Aug 11th, 2019 at 07:35 PM. Reason: added pic

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Question Re: Extract Attachment from Email Crashes

    Update: after seeing this Locals window refc of c:temp\extract (and going to verify there was such a thing) I found it did not exist.
    For kicks, I went ahead and created "temp" and "extract", re-ran and the code successfully loaded a bunch of files from my Inbox then crashed w/ an error indicating there was an attachment that it could not work with --

    That said, I don't want to ever use my full "INBOX", I wanted to use a sub-folder beneath it called: "atest"
    How do I correct / adjust the code to pull attachments from THAT folder instead of the main INBOX level?
    Also, not sure which level is which?
    Seems like top level is where my firstname.lastname@companyname.com is..
    then seems like 2nd level is: "Inbox"
    3rd level: "atest" (which is the level where my 2 test emails are sitting-- that I want attachments extracted from)

    Also, how do I change the location of where I want them placed?
    I don't really want them in c: temp\extract
    I'd prefer: C:\Users\firstname.lastname\Documents\OLAttachments
    Name:  vba-extract-email-attachment-fail005.JPG
Views: 32
Size:  15.6 KB
    Last edited by ChrisOK; Aug 11th, 2019 at 11:04 PM. Reason: correction of typo

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,442

    Re: Extract Attachment from Email Crashes

    Can you expand upon what exactly this line means? (where the error is occurring)
    first part is saving each attachment from an email in sequence stating at #1 for j = 1 to attachments.count, second part specifies where to save to strfolder and filename for file, using, in this case, the filename of the same uploaded file attachments(J).filename

    c:temp\extract
    that is missing a \ after c:, probably a typo in the post

    I don't really want them in c: temp\extract
    I'd prefer: C:\Users\firstname.lastname\Documents\OLAttachments
    put them anywhere you like, just make sure it exists, i just don't want to fill my documents with test files

    "also make sure the attachment filename is a valid file for the windows filesystem" - How, where do I go to verify this?
    in windows filenames may not contain several characters including
    < (less than)
    > (greater than)
    : (colon)
    " (double quote)
    / (forward slash)
    \ (backslash)
    | (vertical bar or pipe)
    ? (question mark)
    * (asterisk)
    also there are reserved words which can also are not allowed, unicode characters may cause a problem in windows for languages that do not support unicode, google should provide a more complete list, so filenames containing dates like 12/10/2019 would be invalid

    re-ran and the code successfully loaded a bunch of files from my Inbox then crashed w/ an error indicating there was an attachment that it could not work with --
    in this case it would appear that the problem has to be the specific attachment, try saving it manually, if an attachment has the same name as a previously saved one this may also cause an issue, even if it not the specific cause here, it could still cause files to be overwritten and lost

    I wanted to use a sub-folder beneath it called: "atest"
    you can find olfolder.name or .folderpath in the locals window
    changing to
    Code:
    Set OlFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).folders("atest")
    should be enough

    and now we've moved the code into OUTLOOK (but should it be in ThisOutlookSession or in something like Module1?
    it is only housekeeping, either should work just fine

    I think this LOCALS WINDOW
    there is also the immediate window, which is just as useful, you can print to it from the running code
    debug.print strfolder or if the code is paused you can type directly into it ?olfolder.folderpath and the value will be printed below, you can also change values of variable and host of other stuff

    you can add a couple of lines to the code to know what is going on more
    Code:
    For Each OlMail In OlItems
        If OlMail.Attachments.Count > 0 Then
            For j = 1 To OlMail.Attachments.Count
                Debug.Print OLmail.Subject, Olmail.attachements(j).filename;
                OlMail.Attachments.Item(j).SaveAsFile strFolder & "\" & OlMail.Attachments.Item(j).FileName
               Debug.Print "    Saved"
            Next j
        End If
    Next
    look in the immediate window each attachment filename should be listed, with the email subject and if it has been saved, that way if you get a crash you can see which attachment failed, should be the bottom one and not have "saved"
    Last edited by westconn1; Aug 12th, 2019 at 05:22 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    26

    Re: Extract Attachment from Email Crashes

    YAY! It works now! Thanks greatly for expanding - very helpful! So excited this works now, it will save a ton of time dealing with the extraction of attachments to 1100+ emails (that come daily) - generated by an automated system. Rated the post for you too.. You made my week! =-)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width