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

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#1
Hello,

everyday I get several e-mails with either an excel or PDF file attached that has the following body:

Code: 1000

Date Company Currency Amount text
11.11.2018 AAA Dollar 1000 bla
11.11.2018 BBB Dollar 1000 bla
12.11.2018 AAA Dollar 1000 bla

I want to extract data for today's date into an excel file that looks like this:

Date Company Currency Amount Code

In the end the excel file should be filled out like this:

Date Company Currency Amount Code
11.11.2018 AAA Dollar 1000 1000
11.11.2018 BBB Dollar 1000 1000

My VBA skills are too limited to come up with a code by myself unfortunately. I am wondering if this is even possible to do via VBA?
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#3

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#4
Sorry, I couldn't edit my post somehow therefore I will double post.

To clarify my problem: It would be sufficient if I was able to extract a certain cell from an XLS email attachment and export it into another cell of another excel file. However, for every e-mail the value to extract is in a different cell and it also should be copied into a different cell.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#5
So you need to get this from an attachment.... how is the cell identified? Is the copy-to-cell the next one in in the list? As long as their a pattern to use to find the cell and a way to identify the copy-to-cell, its doable. You'll need to save the attachment and open it then grab the values. This is doable from outlook... but there needs to be a way to find the correct cell to use.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#6
So here are two exemplary attachments from two different e-mails:

Unbenannt.PNG


Unbenannt.PNG




I have template that looks like this:

Unbenannt.PNG



I need the cells "amounts" from the attachments for today's date copied and pasted into the template so that it looks like this in the end:


Unbenannt.PNG


Each sender usually sends the same mail with the amount in the same cell. However the cells differ across senders as you can see in the two examples.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#7
as long as you are picking up all amounts in the files, you should be able to find the column where amt is and select the cells with values. Then you'll put them in the next available cell. if you need to match values with entries, you should pick up the entire row and fill in all values, not just the amount.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#8
Unfortunately, I am not picking up all amounts in the files. Only the sum of all amounts for today's date for a specific company. E.g. if the sender paid two times to the same company I will only use the sum (which he also sends me).

This will be quite the challenge for a newbie like me but I will keep you posted if I make any progress.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#9
Its going to be a challenge for anyone - trying to pickup the totals without having the fields marked. If they are done with formulas, you might be able to find the field, but that is out of my expertise. What you need to do is get it working as a excel macro then convert it to run from outlook.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#10
So, I narrowed down the problem a little bit.

For each e-mail I know looked up which cell I need to grab and made a note of it. So the code I will need to produce know must do the following:

1. Look through newly arrived e-mail in outlook subfolder and check if it has an xls attachment
2. Check each sheet of the attachment if it has a certain number (company ID) within range A1:E20
3. If it is company 1001 then copy value of cell D12 (of the sheet where the ID was found) and paste into my excel template into cell F1
4. If it is not company 1001 check if it is company 1002
5. If it is company 1002 copy cell D8 and paste into my excel template into cell F2
6. If it is not company 1002 check if it is company 2003 etc...

This seems doable now.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#11
So what I so far managed to do (it's not that much..):

Sub hello()

Dim x As Workbook
Dim y As Workbook
Dim ThisCell1 As Range

'## Open both workbooks first:
Set x = Workbooks.Open("C:\CP")
Set y = Workbooks.Open("C:\CP_Template")

For Each ThisCell1 In x.Sheets("sheet1").Range("A1:D10")

If ThisCell1 = "1001" Then
x.Sheets("Sheet1").Range("D12").Copy

'Now, paste to y worksheet:
y.Sheets("CP_Template").Range("D3").PasteSpecial Paste:=xlValues

'Close x:
x.Close

ElseIf ThisCell1 = "2002" Then
x.Sheets("Sheet1").Range("D12").Copy

'Now, paste to y worksheet:
y.Sheets("CP_Template").Range("D12").PasteSpecial Paste:=xlValues

'Close x:
x.Close
Exit For
End If
Next
End Sub

This automatically extracts the value I am looking for and inserts it into my template file if I download the file manually beforehand.

Now, I am wondering how to make VBA download the attachment of the newest e-mail automatically into the folder and rename it to "CP" and then move the e-mail into another outlook folder.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#12
So, I narrowed down the problem a little bit.

For each e-mail I know looked up which cell I need to grab and made a note of it. So the code I will need to produce know must do the following:

1. Look through newly arrived e-mail in outlook subfolder and check if it has an xls attachment
2. Check each sheet of the attachment if it has a certain number (company ID) within range A1:E20
3. If it is company 1001 then copy value of cell D12 (of the sheet where the ID was found) and paste into my excel template into cell F1
4. If it is not company 1001 check if it is company 1002
5. If it is company 1002 copy cell D8 and paste into my excel template into cell F2
6. If it is not company 1002 check if it is company 2003 etc...

This seems doable now.
Yeah... it should be. Will try to look at your code sample this afternoon.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#14
Okay, so now I am lost again and I have no idea why. I have this code to download the attachment from my newest e-mail and it worked like a charm for days but now it stopped working but I don't know what changed. Maybe you can give me a clue?

Option Explicit

Const olFolderInbox As Integer = 6
Const AttachmentPath As String = "C:\Users\me"


Sub ExtractFirstUnreadEmailDetails()
Dim oOlAp As Object, oOlns As Object, oOlInb As Object
Dim oOlItm As Object, oOlAtch As Object

'~~> New File Name for the attachment
Dim NewFileName As String
NewFileName = Hello

'~~> Get Outlook instance
Set oOlAp = GetObject(, "Outlook.application")
Set oOlns = oOlAp.GetNamespace("MAPI")
Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

'~~> Check if there are any actual unread emails
If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
MsgBox "NO Unread Email In Inbox"
Exit Sub
End If



For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
'~~> Check if the email actually has an attachment
If oOlItm.Attachments.Count <> 0 Then
For Each oOlAtch In oOlItm.Attachments
'~~> Download the attachment
oOlAtch.SaveAsFile NewFileName & oOlAtch.Filename
Exit For
Next
Else
MsgBox "The First item doesn't have an attachment"
End If
Exit For
Next

If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
MsgBox "NO Unread Email In Inbox"
Exit Sub
End If

'~~> Mark 1st unread email as read
For Each oOlItm In oOlInb.Items.Restrict("[UnRead] = True")
oOlItm.UnRead = False
DoEvents
oOlItm.Save
Exit For
Next
End Sub

Everything works expect for the download. I cannot see the file in the folder.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#16
it worked like a charm for days but now it stopped working but I don't know what changed.
i would first check the macro security settings - an update may have changed them. If the project is signed, remove the signature and resign it.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#17
The code worked here. Had an error on NewFileName = Hello line, i fixed it by adding quotes: NewFileName = "Hello"
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#18
Hi Diane,

the code works fine now, thanks for checking. Just one problem remains. The code doesn't recognize excel attachments if they are "read-only". Below is my code again.

For Each oOlItm In olFolder.Items.Restrict("[UnRead] = True")
'~~> Check if the email actually has an attachment
If oOlItm.Attachments.Count <> 0 Then
For Each oOlAtch In oOlItm.Attachments
If Right$(oOlAtch.Filename, 5) = ".xlsx" Then
'~~> Download the attachment
oOlAtch.SaveAsFile AttachmentPath & NewFileName
Else
MsgBox "The First item doesn't have an Excel attachment"
Exit Sub
End If
Exit For
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#19
The code doesn't recognize excel attachments if they are "read-only". Below is my code again.
The macro won't run on the workbook attachments if they are read only? That shouldn't make a difference. The code looks fine... i'm out of town for a conference and may not have a chance to test it this week.
 

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
#20
Exactly if the file is read only, then the code won't download the file and says: The First item doesn't have an Excel attachment.
 
Top