Excel VBA: Excel Doc Accessing Email and Saving Attachment Error

bt_24

New Member
Joined
Jan 16, 2017
Messages
19
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.

Code:
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
Code:
 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
Code:
 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.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

bt_24

New Member
Joined
Jan 16, 2017
Messages
19
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

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
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

New Member
Joined
Jan 16, 2017
Messages
19
@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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top