Outlook VBA Convert .xls attachment to .xlsx

Question

I have the following code snippet from some Outlook VBA code I am creating to automatically export and file a bunch of excel attachments we are getting each day:

Atmt.SaveAsFile FullFileName_And_Path

Atmt stands for attachment and represents the current attachment that VBA is looping through. FullFileName_And_Path and is a string which contains the filename and path that it needs to save it as.

The problem I am having is that some of these attachments are in the old .xls format. Now, when a human does this, they open the file and simply save it as a .xlsx file... Is there command I can use to make VBA simply convert the file to xlsx?

AndASM AndASM · Accepted Answer · 2014-09-29T20:24:24

Outlook cannot do this directly. You need to save a temporary copy of the file, then create an instance of Excel, open the file with Excel, save it as the new format, then close Excel.

So first, add a reference to Excel in the VBA editor goto Tools->References and add Microsoft Excel 14.0 Object Library (Note, 14 is Excel 2010, the number may be different depending on your Office version)

Then something like this should work (untested code):

Public Sub ConvertXlsToXlsx(Atmt As Attachment, FullFileName_And_Path As String)
  Dim tempPath As String
  Dim ExcelApp As Excel.Application
  Dim wb As Excel.Workbook

  tempPath = Environ("TEMP") & "\converttemp.xls"
  Atmt.SaveAsFile tempPath

  ExcelApp = New Excel.Application
  Set wb = ExcelApp.Workbooks.Open(tempPath)
  wb.SaveAs FullFileName_And_Path, Excel.XlFileFormat.xlOpenXMLWorkbook
  wb.Close False
  Set wb = Nothing
  ExcelApp.Quit
  Set ExcelApp = Nothing

  Kill wb 'Clean up the temp file
End Sub

Outlook VBA Convert .xls attachment to .xlsx

1 Answers