Excel VBA: Excel Doc Accessing Email and Saving Attachment Error

[bt_24]

Hi Everyone,

Here is a quick review of what is going on. Everyday I get an email with a file attached, and I have outlook rules set up to have this email sent to a specific folder each day. In an excel file I have code that checks that folder each day for a) an unread email and b) if there is an attachment in the file. If both conditions are true then the code should download and save the attachment in xlsb format. To give credit where it is due, most of this code was sourced from items I found on Ron de Bruin website, along with other forums with a few small tweaks.

Option Explicit
Sub do_something_cool()


'Note be sure to early bind with outlook


'Create the file path where I want the file saved
    Dim AttachementPath As String: Dim NewFileName As String
        AttachementPath = "C:\Daily File Download\"
        NewFileName = AttachementPath & "Daily Reporting File " & Format(Date, "MM-DD_YY")
                


Dim olApp As Outlook.Application
Dim ns As Outlook.Namespace
Dim Inbox_Prog_Pace As MAPIFolder


Dim Item As Object
Dim PaceFile As Attachment


    Set ns = GetNamespace("MAPI")
    Set Inbox_Prog_Pace = ns.GetDefaultFolder(olFolderInbox).Folders("Special Location").Folders("Pacing File")
    
'Check the pacing file folder for an unread email
'If there are no unread emails then display a message
    If Inbox_Prog_Pace.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "The pacing file has already been read and will not be downloaded"
        Exit Sub
    End If


'Get the attachment from the 1st unread email
    For Each Item In Inbox_Prog_Pace.Items.Restrict("[Unread] = True")
        'checking if there is actually an attachment
        If Item.Attachments.Count <> 0 Then
            For Each PaceFile In Item.Attachments
                PaceFile.SaveAsFile NewFileName
            Exit For
        Next
        Else
            MsgBox "There is not an attachment in the email, please QA"
        End If
        
        Item.UnRead = False
        DoEvents
        Item.Save
        
        Exit For
    Next


End Sub

The code I have above works to save the file in the right place, but when I try to access it I get the following error - "Excel cannot open the file 'Daily Reporting File 01_19_17'.xlsb' because the file format or file extensions is not valid. Verify that the file has not been corrupted and that the file extensions matched the format of the file."


In the past I used used a line of code like

 PaceFile.SaveAs FileName:= NewFileName & ".xlsb", FileFormat:=50

to save another excel workbook in xlsb format, but when I try to input into the code above in the place of

 PaceFile.SaveAsFile NewFileName

it errors out on me. I am assuming that it has something to do with the PaceFile being declared as an attachment but really I am not sure. I have searched for answers but have been unable to find anything.

Can anyone please help me to save this file in the right format and be able to access it? Thanks for the help.

 

[bt_24]

Any advice out there?

 

[Derek Brown]

What is the extension of the attachment that you are trying to save.

 

[bt_24]

Hi,

The attachment in the email is a csv file, however when I check the folder where the code is saving the file it isn't saving it under any file type

 

[Derek Brown]

You do not give the file extension in the variable NewFileName.
However, I don't think that you can just save a CSV attachment as an XLSB file. I would guess that you need to "open" the CSV file in Excel and then do the SaveAs.

 

[bt_24]

@Derek Brown - thank you for the advice from your suggestion I was able to modify my code and get it to work

-For anyone finding this post in the future, below is the working code that from excel will extract a csv attachment from unread email in specific folder location and save the file as xlsb file type (can easily modify for other types). From this point forward you can do what ever you want with that file.

Sub do_something_cool()


'Note be sure to early bind with outlook


'Create the file path where I want the file saved
    Dim AttachementPath As String: Dim NewFileName As String: Dim NewFileName2 As String
        AttachementPath = "C:\Daily File Download\"  'think about creating environ$ or make this folder if not there
        NewFileName = AttachementPath & "Daily Reporting File " & Format(Date, "MM-DD_YY") & ".csv"
        NewFileName2 = AttachementPath & "Daily Reporting File " & Format(Date, "MM-DD_YY") & ".xlsb"
            'this will be used later to save as proper format after opening csv version
                


Dim olApp As Outlook.Application
Dim ns As Outlook.Namespace
Dim Inbox_Prog_Pace As MAPIFolder


Dim Item As Object
Dim PaceFile As Attachment


    Set ns = GetNamespace("MAPI")
    Set Inbox_Prog_Pace = ns.GetDefaultFolder(olFolderInbox).Folders("Special Location").Folders("Pacing File")
    
'Check the pacing file folder for an unread email
'If there are no undread emails then display a message
    If Inbox_Prog_Pace.Items.Restrict("[UnRead] = True").Count = 0 Then
        MsgBox "The pacing file has already been read and will not be downloaded"
        Exit Sub
    End If


'Get the attachement from the 1st unread email
    For Each Item In Inbox_Prog_Pace.Items.Restrict("[Unread] = True")
        'checking if there is actually an attachment
        If Item.Attachments.Count <> 0 Then
            For Each PaceFile In Item.Attachments
                PaceFile.SaveAsFile NewFileName 'need to save as .csv on order to open the file out of the folder - other types or blank do not work
            Exit For
        Next
        Else
            MsgBox "There is not an attachment in the email, please QA"
        End If
        
        Item.UnRead = False
        DoEvents
        Item.Save
        
        Exit For
    Next
    
'Reopen the file that was just saved and save as xlsb
    Workbooks.Open NewFileName
        
        ActiveWorkbook.SaveAs FileName:=NewFileName2, FileFormat:=50
        
        Kill NewFileName 'this is to delete the csv version so only xlsb is in folder

End Sub