How To Open Outlook Attachment in Excel, Modify some Data, then Re-Save It

[Charlie]

Hello. This is my first post here. I've been working on this problem for days to no avail and am coming here in the hope someone can help me.

I receive a number of .csv file attachments every day. Here's what I'm trying to accomplish:

1. Open the .csv file in Excel
   
2. Delete the last 6 rows of the file (they're random length; changes every day)
   
3. Save the file
   

Does anyone know how to do that? Saving attachments isn't the problem, it's the Excel part where I'm having problems ... early binding vs. late binding ... none of the code samples I've found seem to work, and none of the other boards seem to know the the solution because they know how to get it to work in Excel but it won't work in Outlook. I need it to work in Outlook VBA.

Thank you for your help.

Charlie

 

[Forum Admin]

Do you have the code that works in Excel (deleting the last 6 rows)? Once you know you have working code, you need to tweak it a little to use in Outlook.

If you are the only user, I would add the Excel object model to Outlook - in the VB Editor, choose Tools, References and find Excel in the list.

Do you want to save the edited attachment back to the message or just save it on the hard drive?

 

[Charlie]

Thank you. I finally got this working. Here's the final code:

Public Sub saveAttachtoDisk(itm As Outlook.MailItem) 
 
Dim objAtt As Outlook.Attachment 
 
Dim saveFolder As String 
 
Dim attachName As String 
 
Dim oXL As Excel.Application 
 
Dim oWB As Excel.workbook 
 
Dim oSheet As Excel.Worksheet 
 
Dim oRng As Excel.Range 
 
saveFolder = "C:\Temp\"
  'Grab attachment
     For Each objAtt In itm.Attachments
         objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
         attachName = objAtt.DisplayName
         Set objAtt = Nothing
     Next
  
  ' Start Excel and get Application object.
     Set oXL = CreateObject("Excel.Application")
     oXL.Visible = True
   
  ' Get a new workbook.
     Set oWB = oXL.Workbooks.Open(saveFolder & attachName)
     Set oSheet = oWB.ActiveSheet
       
   ' Find Last Row and Clear Contents; Do this 5 Times
     Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
     oRng.Cells.ClearContents
   
     Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
     oRng.Cells.ClearContents
   
     Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
     oRng.Cells.ClearContents
   
     Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
     oRng.Cells.ClearContents
   
     Set oRng = oSheet.Columns("A:A").Find("*", oSheet.[a1], xlValues, , xlByRows, xlPrevious)
     oRng.Cells.ClearContents
     
   ' Make sure Excel is visible and give the user control
   ' of Microsoft Excel's lifetime.
     oXL.Visible = True
     oXL.UserControl = True
   
   'Save the File
     oWB.Save
     oWB.Saved = True
   
   ' Quite, Close and Make sure you release object references.
     oWB.Close
     oXL.Quit
     Set oRng = Nothing
     Set oSheet = Nothing
     Set oWB = Nothing
     Set oXL = Nothing
    
 
End Sub

 

[Forum Admin]

Cool. Thanks for sharing.