Ndtyjky

I'm trying to save the daily system generated report attached to the e-mail to a folder.
My script saves and renames the file, but when I try to open the file it says:
The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.
Below the script:

Public Sub UnzipFileInOutlook(itm As Outlook.MailItem)

 Dim objAtt As Outlook.Attachment

 Dim saveFolder As String

 saveFolder = "C:UsersachengDesktop"

 For Each objAtt In itm.Attachments

 objAtt.SaveAsFile saveFolder & "Order_History_Report.xlsx"

 Set objAtt = Nothing

 Next

End Sub

I do not understand: “When I use the code objAtt.SaveAsFile saveFolder only, the file isn't corrupted.”

Unless I include a filename in the path, SaveAsFile always stops with an error for me. I have tried every variation that I can think of but none will save the file.

I have never had SaveAsFile corrupt a file. My theory is your emails have more than one attachment and you are saving the wrong one. Your code it:

saveFolder = "C:UsersachengDesktop"

For Each objAtt In itm.Attachments

   objAtt.SaveAsFile saveFolder & "Order_History_Report.xlsx"

   Set objAtt = Nothing

Next

If the email has two attachments, this code will:

• Save the first attachment as “C:UsersachengDesktopOrder_History_Report.xlsx” overwriting any file of this name retained from yesterday.


• Save the second attachment as “C:UsersachengDesktopOrder_History_Report.xlsx” overwriting the first attachment.

Most emails today use Html images for logos and pictures but a few still use embedded images which are carried as attachments but not reported to the user as attachments.

The code below will test my theory. Copy this code to a new module:

Option Explicit

Public Sub InvestigateEmails()



  Dim Exp As Explorer

  Dim InxA As Long

  Dim ItemCrnt As MailItem



  Set Exp = Outlook.Application.ActiveExplorer



  If Exp.Selection.Count = 0 Then

    Call MsgBox("Pleaase select one or more emails then try again", vbOKOnly)

    Exit Sub

  Else

    For Each ItemCrnt In Exp.Selection

      With ItemCrnt

        Debug.Print "From (Sender email address): " & .SenderEmailAddress

        Debug.Print "Subject: " & .Subject

        Debug.Print "Sent on: " & .SentOn

        Debug.Print "Received: " & .ReceivedTime

        If .Attachments.Count = 0 Then

          Debug.Print "No attachments"

        Else

          Debug.Print "Attachments:"

          Debug.Print "No.|Type|Path|Filename|DisplayName|"

          For InxA = 1 To .Attachments.Count

            With .Attachments(InxA)

              Debug.Print InxA & "|";

              Select Case .Type

                Case olByValue

                  Debug.Print "Val";

                Case olEmbeddeditem

                  Debug.Print "Ebd";

                Case olByReference

                  Debug.Print "Ref";

                Case olOLE

                  Debug.Print "OLE";

                Case Else

                  Debug.Print "Unk;"

              End Select

              ' Not all types have all properties.  This code handles

              ' those missing properties of which I am aware.  However,

              ' I have never found an attachment of type Reference or OLE.

              ' Additional code may be required for them.

              Select Case .Type

                Case olEmbeddeditem

                  Debug.Print "|";

                Case Else

                  Debug.Print "|" & .PathName;

              End Select

              Debug.Print "|" & .Filename;

              Debug.Print "|" & .DisplayName & "|"

            End With

          Next

        End If

        Debug.Print "--------------------------"

      End With

    Next

  End If



End Sub

To use this code, select one or more of the emails you wish to process and then run InvestigateEmails. It will output a selection of properties for each selected email. Your Immediate window will look something like:

From (Sender email address): a.j.dallimore@MyIsp.com

Subject: Test

Sent on: 15/11/2018 11:22:41

Received: 15/11/2018 11:22:49

Attachments:

No.|Type|Path|Filename|DisplayName|

1|Val||002 View from back of Neville Tower.jpg|002 View from back of Neville Tower.jpg|

2|Val||image001.jpg|image001.jpg|

--------------------------

I sent the above email to myself. Attachment 1 was a regular attachment that was listed in the header and could be dragged to a folder or clicked to open it. Attachment 2 was embedded and appeared as a picture in the body of the email but was not listed in the header.

My theory is that given one of your emails, InvestigateEmails will list more than one attachment per email and the last attachment will not be an Excel workbook. You save that attachment with an extension of “xlsx” so Excel tries and fails to open it.

By the way, I use this routine whenever I am developed new code to process emails. I might plan for my new code to run off an event or a rule but this routine makes testing so much easier. I amend this code to call my new code and select a simple email. When I have debugged my code to work with a simple email, I try a more complicated one. There is no easier way of controlling which emails are passed to a macro for processing. I suggest you keep this macro for future use. Alternatively, I can tell you where to find a larger version which outputs to a desktop text file.

The rest of this answer assumes my theory is correct. If my theory is wrong, we may need to investigate further.

Before showing you how to save the correct attachment with a static file name, I will say that I doubt this is a good idea. If I understand correctly, each day you receive a workbook which overwrites the previous day’s workbook. You extract data from the workbook and save it in your database. This may seem like a good idea but this can go disastrously wrong. Years’ ago, I knew of a similar situation although, thankfully, I was not personally involved. Like you, they were receiving files, extracting data and then discarding the files. This all seemed to work for some months but they then discovered that the data was not being extracted correctly. If they had kept the original source files, fixing their database would have been difficult but possible. But without the original data, there was nothing they could do.

I recommend you save your files with a name like: “Order_History_Report 181115.xlsx”. This will allow you to archive all the original files against any future need to reprocess them. Processing a file with a name like this is a little more difficult but I can explain how to do this.

Returning to your code, the easiest correction is:

For Each objAtt In itm.Attachments

   If LCase(Right$(objAtt.DisplayName, 4)) = ".xlsx" Then

     objAtt.SaveAsFile saveFolder & "Order_History_Report.xlsx"

     Set objAtt = Nothing

     Exit For

   End If

Next

This code ignores any attachments that are not regular workbooks and, once it has found a regular workbook, it does not look for more.