Outlook: Save Excel attachment and change file extension

[PDizzle]

Hi all,
I have no prior experience in VBA and just google searched solutions to my problem.
I was able to make the "Save Excel attachment"-part work with the code below. Now, since the attachment is a .xls file and gives me a corrupted file message when opening (other worksheets pull data from this saved file), I would like to save it automatically as a .xlsx file. I tried to find a solution but the things I have tried would not work. Hope to find some help here.
Thanks in advance!


Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim dateFormat
dateFormat = Format(itm.ReceivedTime - 1, "yyyymmdd_")
saveFolder = "THIS IS MY FOLDER"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & dateFormat & objAtt.DisplayName
Set objAtt = Nothing
Next
End Sub

[KilianD]

Hi,
I don´t think you can do that. Possible solution would be to save the attachment, call excel macro to open it, save as *.xlsx and delete the original file.
Regards

[skatonni]

See how this works out


Sub Save_Attachments_xlsx(itm As Outlook.MailItem)
Dim objAtt As Outlook.attachment
Dim attchmtName As String
Dim objSelection As Outlook.Selection
Dim objOL As Outlook.Application
Dim xl As Excel.Application
Dim xlwkbk As Excel.Workbook
Set objOL = CreateObject("Outlook.Application")
Set objSelection = objOL.ActiveExplorer.Selection
Dim saveFolder As String
Dim dateFormat
saveFolder = "C:\Test\"
Debug.Print itm.Subject
dateFormat = Format(itm.ReceivedTime - 1, "yyyymmdd_")
For Each objAtt In itm.Attachments
attchmtName = saveFolder & dateFormat & objAtt.FileName
Select Case Right(objAtt, Len(objAtt) - InStrRev(objAtt, "."))
Case "xls"
objAtt.SaveAsFile attchmtName
' open the attachment
If xl Is Nothing Then
Set xl = CreateObject("Excel.Application")
End If
Set xlwkbk = xl.Workbooks.Open(attchmtName)
' FileFormat:=51 is the key
xlwkbk.SaveAs saveFolder & dateFormat & objAtt.FileName & "x", FileFormat:=51
xlwkbk.Close False
Kill attchmtName
xl.Quit
Case "xlsx", "xlsm"
' save in original format
objAtt.SaveAsFile attchmtName
End Select
Next
Set xlwkbk = Nothing
Set xl = Nothing
Set objOL = Nothing
Set objSelection = Nothing
End Sub

[AmroK86]

skatonni, I know this is really old, but its exactly what I'm looking to do, except, the attachments that I'm receiving are in CSV format, and I need to open each one in Excel and save the file as xls or xlsx (and get rid of the original csv).

I'm setting a rule on outlook to run this code for all emails from a specific sender with a specific subject, but nothing happens when I try to run the rule.

I used a similar piece of code PDizzle did, but when appending the filename with "xls" the file type appears to be correct in Windows, but the date data in the CVS is not being converted into a date format I need it to be compared to when I manually open the file in Excel and saved it as xls or xlsx.

I would really appreciate your help? or anyone that can advise on what I need to change?

My current code:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)

Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim dateFormat As String

saveFolder = "C:\Users\Akhoudeir\Desktop\Test"
dateFormat = Format(itm.ReceivedTime, "yyyy-mm-dd H-mm")

For Each objAtt In itm.Attachments

If InStr(objAtt.DisplayName, ".xml") Then
objAtt.SaveAsFile saveFolder & "\" & dateFormat & " - " & objAtt.DisplayName
End If

Set objAtt = Nothing
Next

End Sub

[gmayor]

As others have said, you cannot simply change the extension and make it a different file type. If the file cannot be opened, I suspect the solutions offered will not work, as they require the file to be opened, though I agree with skatonni's use of the filename of the attachment, rather than the displayname, and that alone may cause it to work. Watch out too for the correct number of path separators.

There is no reason why Excel cannot open xls format files, though check that the file type isn't blocked in Excel's file block settings. With a slight change to your code, it works here in Outlook 2016 and the xls format file can be opened without problem in Excel 2010 and Excel 2016.


Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim dateFormat As String

dateFormat = Format(itm.ReceivedTime - 1, "yyyymmdd_")
saveFolder = "D:\Path\"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & dateFormat & objAtt.fileName
Next
Set objAtt = Nothing
End Sub

[AmroK86]

Thanks for the response gmayor.

Apologies if if I wasn't clear but I do want the file to be opened in excel and saved as a different file type. The code I included works well for me and changes the file's extension into xls but for some reason I only get the required outcome (date data format) when I manually open the file in Excel and save as xls/xlsx.

So to clarify, I want to be able to use Slaton's code but open a .csv file in Excel and have excel save it as a xls or xlsx file and after that get rid of the original csv.

[gmayor]

Now I'm even more puzzled. Your original message said that the attachment was XLS format and that the file reported corruption when you tried to open it.

If you cannot open it, you cannot convert it to anything. Changing the extension simply changes the extension. It doesn't change what the file is.

Where now does CSV come into this? CSV is yet another format that Excel can open. It is not however the same as XLS format, nor XLSX.

What is the attachment format?

Can you open the attachment normally in Excel?

[shrowded]

so the difference between your code shown below, and the code above is that your code is saving down the file off the email and changing the name. You aren't doing the extra step above to change the file extension. so in reality your files are being saved as "blah blah blah\2016-11-08 1-24\.xls.csv to fix this.. you need to add in the coding from skatonni. I would replace your For Each Next loop with his. and change the case "xls" to "csv" . this will have it loop through the itm.attachments for a csv file. His code continues to open the file in a new or existing instance of excel and then uses the excel .saveas to change the file format. which will truly change the format like you do when you click save as in excel and change the filetype at the bottom of the box. you would then likely need to adjust the "File Format:=51" line to be the proper file type that you want as the output.

skatonni, I know this is really old, but its exactly what I'm looking to do, except, the attachments that I'm receiving are in CSV format, and I need to open each one in Excel and save the file as xls or xlsx (and get rid of the original csv).

I'm setting a rule on outlook to run this code for all emails from a specific sender with a specific subject, but nothing happens when I try to run the rule.

I used a similar piece of code PDizzle did, but when appending the filename with "xls" the file type appears to be correct in Windows, but the date data in the CVS is not being converted into a date format I need it to be compared to when I manually open the file in Excel and saved it as xls or xlsx.

I would really appreciate your help? or anyone that can advise on what I need to change?

My current code:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)

Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim dateFormat As String

saveFolder = "C:\Users\Akhoudeir\Desktop\Test"
dateFormat = Format(itm.ReceivedTime, "yyyy-mm-dd H-mm")

For Each objAtt In itm.Attachments

If InStr(objAtt.DisplayName, ".xml") Then
objAtt.SaveAsFile saveFolder & "\" & dateFormat & " - " & objAtt.DisplayName
End If

Set objAtt = Nothing
Next

End Sub