Hi, So each day I get emails with attachments in csv form but I need them to be in xlsx form when saved. So what I am trying to do is use a rule within outlook that will take the CSV file, convert it, and save it as a xlsx in another folder. Here is what I have so far but it doesn't seem to function properly.
Code
- Public Sub SaveCSVtoExcel(itm As Outlook.MailItem)
- Dim oAttachment As Outlook.Attachment
- Dim mySaveFolder As String
- Dim objSelection As Outlook.Selection
- Dim myFileName As String
- Dim DateFormat
- Dim objOA As Outlook.Application
- Dim myWB As Excel.Workbook
- Dim myApp As Excel.Application
- Set objOA = CreateObject("Outlook.Application")
- Set objSelection = obj.ActiveExplorer.Selection
- mySaveFolder = "myfolder/forsaving"
- Debug.Print itm.Subject
- DateFormat = Format(Now, "mmddyy")
- For Each oAttachment In itm.Attachments
- myFileName = mySaveFolder & "Inventory Balance" & " " & DateFormat
- Select Case Right(objAtt, Len(objAtt) - InStrRev(objAtt, "."))
- Case "csv"
- objAtt.SaveAsFile attchmtName
- If myApp Is Nothing Then
- Set myApp = CreateObject("Excel.Application")
- End If
- Set myWB = myApp.Workbooks.Open(attchmtName)
- myWB.SaveAs saveFolder & DateFormat & " " & objAtt.FileName & "x", FileFormat:=51
- myWB.Close False
- Kill attchmtName
- myApp.Quit
- Case "xlsx", "xlsm"
- ' save in original format
- objAtt.SaveAsFile attchmtName
- End Select
- Next
- Set myWB = Nothing
- Set myApp = Nothing
- Set objOA = Nothing
- Set objSelection = Nothing
- End Sub
