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

Status
Not open for further replies.

Charlie

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

Senior Member
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

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Thank you. I finally got this working. Here's the final code:

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
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
D Outlook VBA to open Excel attachment and send recipient's email address to a workbook cell? Using Outlook 4
S Pre populate subject + body + attachment to already open email in outlook 2007 Using Outlook 2
C How to open .lnk attachment which is been blocked by outlook Using Outlook 3
M unable to open PDF or (http, www or utube) link attachment to outlook messages because of ' RESTRICT Using Outlook 1
K word 2007 attachment won't open on windows 7 machine using Outlook Web Access v2003 Using Outlook 1
N CANNOT OPEN PREVIEW OF PDF ATTACHMENT IN OUTLOOK MAIL Using Outlook 14
Y message attachment saved by Outlook.Attachment.SaveFileAs not open Outlook VBA and Custom Forms 7
A How to open a specific link automatically with outlook 2016 Outlook VBA and Custom Forms 5
H Upon opening Outlook, make my popmail inbox open instead of outlook.com inbox Using Outlook 1
sahameed82 SharePoint calendar directly open in Outlook Using Outlook 0
C Outlook 2016/2019 hangs after being open for an extended period Using Outlook 4
J How to open OST file in Outlook 2019 & 2016 Using Outlook 1
C Can't Open Outlook 365 Using Outlook 0
M outlook won't open! Using Outlook 1
E Unable to open Outlook 2010 after adding new email account Using Outlook 4
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
A How to open a specific link automatically with outlook Outlook VBA and Custom Forms 13
S Outlook 2010 Cannot Open Attachments Using Outlook 14
N open the hyperlink in Outlook directly instead of browser Using Outlook 1
S Outlook does not open the .pst file created by the Outlook Using Outlook 5
A open Outlook with multiple windows. Using Outlook 0
G Can't open .pst. Message could not access default folder (Outlook 2010 (.pst). Before that was backi Using Outlook 0
I Outlook 2010, 2013 will not open .msg or .eml files Using Outlook.com accounts in Outlook 1
Diane Poremsky Outlook VBA: Work with Open Item or Selected Item New Slipstick.com Articles 0
O Windows 10 x64 Outlook 2013 - URL does not open (anymore) Using Outlook 3
Diane Poremsky Outlook 2013: Control Panel Mail Won't Open New Slipstick.com Articles 0
Diane Poremsky Outlook Links Won't Open In Windows 10 New Slipstick.com Articles 1
L Outlook 2013 won't open MS Office attachments Using Outlook 1
Diane Poremsky Open Shared Mailboxes in Outlook on the Web New Slipstick.com Articles 0
V Cannot start Outlook. Cannot open the Outlook window. Using Outlook 8
O Outlook 2003 can't open contacts. Using Outlook 2
Z Outlook 2007, clicking email addresses fails to open Contact Using Outlook 0
G Outlook calendar entry corrupted. Constant pop up when you open outlook. Unable to delete or remove. Using Outlook 2
J Outlook won't open. PST file busy. Using Outlook 3
M Cannot start MS Office Outlook Cannot open the Outlook window Using Outlook 2
Gareth Outlook Task Will not open. Using Outlook 5
M Can't open same msg file more than once at the same time in Outlook 2010 Using Outlook 7
B What is the best way to use Outlook address book to select customer and then open Excel Outlook VBA and Custom Forms 22
J VBS Script (macro) for word to open Outlook template. Outlook VBA and Custom Forms 2
F Cannot open extracted .msg files from outlook Outlook VBA and Custom Forms 1
L Trying to open contacts pst. file in outlook 365 Using Outlook 3
V Open Outlook Calendar and Tasks views with Folder pane off Using Outlook 2
L Outlook 2007 Open Folder From a Contact Using Outlook 22
B Add-in "always enabled" but Outlook 13 won't open Using Outlook 7
P open reminders missing after copying outlook pst file Using Outlook 2
L Outlook 2007 Macro Open Contact Folder Using Outlook 7
Lucas attachments in outlook don't open, when Icloud is connected Using Outlook 0
T Outlook 2010 Very Slow To Open Recently Using Outlook 9
W outlook refuses to open any attachments ... not related to shadow directory Using Outlook 2
M Disk Reads of outlook.pst while outlook not open Exchange Server Administration 1

Similar threads

Top