Consulting

Results 1 to 7 of 7

Thread: Extract Data from Outlook and populate it into Excel

  1. #1
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location

    Extract Data from Outlook and populate it into Excel

    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
    Last edited by MarkCBB; 06-30-2010 at 09:54 PM.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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

  3. #3
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    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
    Last edited by MarkCBB; 07-01-2010 at 12:07 AM.

  4. #4
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    I have been searching the web for days now, i have a feeling this is not possible. any help?

  5. #5
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    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.

    [vba] 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[/vba]

  6. #6
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    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

  7. #7
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    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").

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •