Extract Data from Outlook and populate it into Excel

[MarkCBB]

Hey there,

I am desperately looking for a way to populate fields in Excel from emails in outlook.
I recieve requests via an excel attachment that is mailed to me. i am looking for code to search Outlook for all mails that have a subjuect called "MarkTest" and the attachment and extract the data from the excel attachment and populate it on the master excel sheet (the one with the code). it also need to be able to do a lookup of an ID that is on both excel sheets and insert the infomation to the right of the code under the same headings.

i.e. the exccel sheet that is sent to me has the following headings ID,Name, Surname, age and comment as does the master sheet. i want the code to match teh ID from the email and paste it by the Corresponding ID on the master sheet. the master sheet will have all the IDs already populcated in column A.

this is also be able to update existing data on the master sheet, with the mail with the latest recieved date taking priority.

I have seen many codes that do somehting similer to this but i am not able to adate them to this, i am also new to VBA. so if you can send an attachment with the code that would be FANTASTIC!!!!!!

Regards,
Mark Blackburn

[GTO]

Greetings Mark,

Most importantly, I see that this is your first post and that you recently joined. Welcome to VBAX! This is a great forum (IMO, the best), and it's because of the great folks here. If you want help in learning how to code in vba, you will just be awfully happy you joined :-)

As to your question, might I suggest that you zip a couple of the incoming workbooks, along with what you want the outcome to look like. It will be a bit of effort possibly, but I think that would be the best shot at helping the helpers help you :-)

Again, glad you joined :-)

Mark

[MarkCBB]

Hi There,

Thanks so much for the warm welcome. Its great to be part of this very helpfull site, hopefully one day I will be able to provide help as well as seek it.

I have attached the the files as requested, this is just an example, of some of the scope. Please let me know if i need to explain anything else. it would be so amazing if this can be done!

The mailed sheets will be coming from different people but the subject of the mail and the name of the attachment will always be the same. The subjuct might however be forwarded to replied. RE: FW: might from part of the mail.

it would be really really cool if the body of teh mail could be inserted into the Comments column.


I look forward and appreciate any and all help and assistance.

Regards,
Mark

[MarkCBB]

I have been searching the web for days now, i have a feeling this is not possible. any help?

[Dr.K]

Not exactly what you wanted, but here is a piece of code I use to QC approve Excel workbooks (I removed all the non-Outlook parts). Maybe you can rework some of this.

It uses Late Binding, so you won't have to add a reference.

Dim OutApp As Object
Dim OutNS As Object
Dim OutRecipient As Object
Dim OutFolder As Object
Dim OutItems As Object
Dim OutMail As Object
Dim OutReply As Object
Dim OutAttachments As Object
Dim OutAttach As Object

Dim AttachFound As Boolean



'Reply to original email

'find original email
Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNameSpace("MAPI")
Set OutRecipient = OutNS.CreateRecipient(BDA_MailboxName)
Set OutFolder = OutNS.GetSharedDefaultFolder(OutRecipient, olFolderInbox)

Set OutItems = OutFolder.Items

'iterate through mailitems in inbox to find Mail Item with correct attachment
For Each OutMail In OutItems
Set OutAttachments = OutMail.Attachments
For Each OutAttach In OutAttachments
If OutAttach.Filename = ActiveWorkbook.Name Then AttachFound = True
Next OutAttach
If AttachFound = True Then Exit For
Next OutMail

Set OutAttach = Nothing
Set OutAttachments = Nothing

'if you can't find, it, inform user and exit sub
If AttachFound = False Then
ActiveWorkbook.Save
MsgBox "The DB entry has been completed, and this Request is now QC Approved." & vbLf _
& vbLf & "However, you will still have to:" & vbLf _
& vbTab & "-Manually email the workbook back to the original requestor" & vbLf _
& vbTab & "-Move the workbook from the ""Performance Request"" folder to the " _
& """Archive"" folder for this year." & vbLf _
& vbTab & "-Delete the original Request email (if you can find it)", vbOKOnly, _
"Couldn't find original Request email"
Set OutMail = Nothing
Set OutItems = Nothing
Set OutFolder = Nothing
Set OutRecipient = Nothing
Set OutNS = Nothing
Set OutApp = Nothing
Exit Sub
End If

'create reply message
Set OutReply = OutMail.Reply

With OutReply
.Attachments.Add ActiveWorkbook.FullName
.Send
End With

Set OutReply = Nothing

OutMail.Delete


'Delete all files with subject lines that end with activeworkbook.name
Set OutItems = OutFolder.Items

'iterate through mailitems in inbox to find Mail Item with correct attachment
For Each OutMail In OutItems
If Right(OutMail.Subject, Len(ActiveWorkbook.Name) - 4) = _
Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Then
OutMail.Delete
End If
Next OutMail

'clear all objects
Set OutMail = Nothing
Set OutItems = Nothing
Set OutFolder = Nothing
Set OutRecipient = Nothing
Set OutNS = Nothing
Set OutApp = Nothing

[MarkCBB]

Hey Dr.K,

Thanks for this, but i have no idea what it means, or how to use it, if possible can you attach your version so i can try firgure it out by sending myself emails and seeing how it works? i would really appriciate it.

Thanks for the responce, i was starting to loss hope. :)

regards,
Mark B

[Dr.K]

Well, part of the problem is that there are many different ways to configure Outlook... the example I showed you is for an exchange server.

Do you know how to use the immediate window for debugging? My best advice is to stick that piece of code into a sub and try stepping through it. Just replace the email address constant (BDA_MailboxName) with a literal string that contains your email address ("MarkCBB@stuff.com").