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

Status
Not open for further replies.

elbrodero

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
POP3
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?
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
The code worked here. Had an error on NewFileName = Hello line, i fixed it by adding quotes: NewFileName = "Hello"
 
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
 
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.
 
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
B Copying data between atbs in a custom form Outlook VBA and Custom Forms 4
N error when copying outlook data "can not copy: the path too deep" Using Outlook 6
R Copying Data from Exchange to PST file Outlook VBA and Custom Forms 1
H Copying email address(es) in body of email and pasting in To field Outlook VBA and Custom Forms 1
P Copying ALL calendar entries from Calender in PST file to IMAP OST file? Using Outlook 1
R VBA for copying sent email to current folder under a shared mailbox Outlook VBA and Custom Forms 17
E Copying the whole e-mail body into excel Outlook VBA and Custom Forms 0
T Copying Outlook Account Setup For Desktop App Using Outlook 5
J Outlook - 2013 - Error msg when copying folders from Online Archives to another user's mailbox Using Outlook 0
Thiago Manzano Copying E-mails to a folder on HD Using Outlook 1
Diane Poremsky Mark Sent Items as Read After Copying with a Rule Using Outlook 0
crazyboy Copying BCM database from backed up mounted image to new drive BCM (Business Contact Manager) 2
oliv- Prevent copying shared contacts Outlook VBA and Custom Forms 5
Paul Butticaz Copying Contacts from SharePoint List (connected to Outlook) to another Conacts folder Using Outlook 1
K Outlook 2010 Not responding after IMAP folder deletion or copying. Using Outlook 2
Mary B Outlook 2013: Rule for copying new email to folder & marking that copy as read Using Outlook 1
S Calendar items are not being uploaded to outlook.com after copying Using Outlook.com accounts in Outlook 1
P open reminders missing after copying outlook pst file Using Outlook 2
L Copying Emails using drag and drop Using Outlook 2
O New to Outlook 2013, dealing with copying vs. moving sent items Using Outlook 0
S Recover messages that disappeared when copying to folder Using Outlook 1
S Copying multiple messages to folders and keeping date/time detail Using Outlook 1
D Copying POP3 folders from Inbox to IMAP Inbox in Outlook 2003 Using Outlook 0
M Copying setups for mutiple users Using Outlook 2
T Location and simple copying of emails and settings, OL 2010 and Win 7 64bit. Using Outlook 3
A Copying additional contact fields when chosing "Contact from the same company" Using Outlook 5
A Copying cc email addresses to Excel - does not copy the <joe.bloggs@isp.com> Using Outlook 1
S Search Indexing (Copying Folders) Using Outlook 0
E Copying multiple folders to PST Outlook VBA and Custom Forms 2
R copying a custom view from a public folder and distributing programatically Outlook VBA and Custom Forms 3
N Copying outlook macros between pcs Outlook VBA and Custom Forms 1
V Copying contents from one field to another field Outlook VBA and Custom Forms 6
C Advanced search terms for "Outlook Data File" Using Outlook 1
C Populate form data into message body Outlook VBA and Custom Forms 1
CWM550 Saving Data: Don't check certain folders Using Outlook 2
CWM550 Importing " Old Skool" Data Using Outlook 0
W Create a Quick Step or VBA to SAVE AS PDF in G:|Data|Client File Outlook VBA and Custom Forms 1
Wotme create email only data file Using Outlook 1
V Backup Calendar, Contacts, Tasks in an POP3 data file Using Outlook 3
e_a_g_l_e_p_i Changing where data .pst is saved to Using Outlook 3
J Deliver new messages to New or Existing Data File? Using Outlook 2
G Outlook 2016: Want IMAP Data Files on My D: Drive and Not C: Drive Using Outlook 1
T vba extract data from msg file as attachment file of mail message Outlook VBA and Custom Forms 1
D Outlook 2016 Unable to load Outlook data pst file Using Outlook 5
V Form data not sending for some users Outlook VBA and Custom Forms 2
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
J Outlook 2013 Change color of text in data fields of contacts in Outlook 2013? Using Outlook 10
T How can Exchange be configured to sync/push one-way so that the server data can't be affected Exchange Server Administration 0
T Extract Data From Outlook Tasks Using Outlook 0
F outlook.com Exchane Server corrupts data Since September 2019 Using Outlook.com accounts in Outlook 6

Similar threads

Back
Top