Copying data from e-mail attachement to EXCEL file via macro

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#22
Correct. But the the files are .xlsx. The only difference I could find is that they are read only files.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#23
Is the name of the effected files in upper cases? That makes a difference. Use the Lcase function to turn everything into lower cases.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#24
That's not it unfortunately. The file is in lower cases. I tried the LCase function anyway but with no result. Really weird.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#25
I'll test it - it should work though, since you are only reading it and using an outlook macro rather than excel. I'm not sure if i'll have a chance to test it today, but do have a couple of breaks. (I'm at a conference and don't have a full schedule today.)
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#27
There is no On Error Resume Next in the code.

@Diane I am using the code out of excel and not outlook, if that makes any difference.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#28
If you set a breakpoint on the line with the message box, then place the mouse over the FileName property when the code execution stops , what does it display?
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#29
Sorry, I am not sure what you mean. I set a breakpoint on the line with the message box, ran the code and now the line is highlighted yellow. When I place the mouse over the line "oOlAtch.SaveAsFile AttachmentPath & NewFileName" a box pop ups with "attachement path= ...."
When I put the mouse above other lines nothing happens
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#30
When the code exec stops, look at the value in oOlAtch.Filename. It cannot end with ".xlsx" in lower cases else the msgbox wouldn´t be displayed.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#31
When the code stops, nothing changes. The line still reads If Right$(oOlAtch.Filename, 5) = ".xlsx"
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#32
Sure. You need to look at the content of the oOlAtch.Filename property. One method to do so is to hover with the mouse over "FileName". That should display the prop´s content in a tooltip.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#33
Sorry, but nothing happens when I hover with the mouse over FileName. Only when I hover over NewFileName. Here it says NewFileName= "CP1.xlsx"
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#34
That should mean oOlAtch is declared as object, not as attachment. You can also open the local window to look at a variable´s content. Or select all the "oOlAtch.Filename", then press shift+f9.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#35
So I just checked and oOlAtch wasn't declared at all. But the code still works somehow. When I select oOlAtch.Filename and press F9 it says "Outside of Context" (rough translation as I don't use Excel in english).
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#36
You´re kidding, aren´t you? Of course, debugging only works when the code is running.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#37
Sorry, your are right. I am new to VBA. Now it says value: image001.png

I tried declaring oOlAtch as Attachment but it doesn't work.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
#38
Great, now you see why the msgbox pops up, it´s because some emails have more than just the expected attachments. Your error is a logical one: You don´t know at which position the Excel attachment is, so you cannot display the "not found" message and leave the loop as soon as it hits a non Excel attachment because the Excel attachment you´re looking for could be at the next position.

If you need to ensure that only one attachment per email is saved, then leave the loop after the attachment has been saved. If it should be possible to save more than one attachment, then don´t leave the loop at all.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#39
Okay, so is there a way that VBA checks both attachments and only downloads the .xlsx file? To be honest I though it was already doing exactly that.
 
Top