Export email data to specific fields in CSV file (or XLS file)

Question

This is regarding Excel & Outlook 2013 -

I am working on a problem where I need to...

Receive an email
Have that email automatically moved to a specific folder
Have any email in said folder exported into a .csv file
The data in that file must be placed under specific columns.

I can achieve all of the above, except #4. The script places the entire body of the email in different cells depending on breakpoints.

Here's a copy of the email I need to export:

Area of Interest: Post a Job Type of Job: Full-time Campus Location: Montgomery --------------------- Contact Information: Title: Manager Contact Last Name: Wilson Contact First Name: Allison Address: 3424 Peachtree Rd NE City: Atlanta State: Georgia Zip: 30326 Phone: 4042669876 Email: specialtyma@pyapc.com --------------------- Company Information: Company Name: Pershing, Yoakley & Associates Company Phone: 4042669876 Company Fax Number: Company Website: Type Of Business: --------------------- Job Details: Job Title: Medical Assistant Start Date: August 1, 2016 Job Type: Full-time Salary Range: $25,000 Referral Source: --------------------- Job Description: A specialty practice in Montgomery, AL seeks a Medical Assistant. Prior experience in a medical practice is preferred. Candidates must have great interpersonal and customer service skills, and must be self-starters and multi-taskers – assisting physician with examination and treatment of patient and maintenance of clinical equipment. Interested candidates should submit their resume to specialtyma@pyapc.com. --------------------- Application Process: --------------------- Hiring Process: Phone Interview: Yes Background Check: Yes Reference Check: Yes Credit Check: No Technical Test: No Personality Test: No Physical Exam: No Driving Records: No Other: No --------------------- Requested way to receive resumes: Fax: No Mail: No Email: Yes Apply in person: No Apply online: No --------------------- Additional Requests:

The above bolded areas, have to fall into the following areas:

Job Title | Company Name | Description | Contact Name | Contact Email | Zip | Salary | Start Date |

Here is what I have so far (referring to #'s 1-3 above)... which I've placed in ThisOutlookSession of Outlook.

Whatever is not bolded, should not go to the CSV either.

Option Explicit 
Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()

    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")

     '// The INCOMING JOBS folder must be a folder of the INBOX.

    Set Items = objNS.GetDefaultFolder(olFolderInbox).Folders("Incoming Jobs").Items

End Sub
Private Sub Items_ItemAdd(ByVal item As Object)

    On Error GoTo ErrorHandler

    Dim Msg As Outlook.MailItem

    Dim iFile As Integer

    If TypeName(item) = "MailItem" Then



        Set Msg = item

        iFile = FreeFile
        Open "C:\Temp\INCOMING_JOBS.CSV" For Append As #iFile

        Print #iFile, Replace(Msg.Body, vbCrLf, ",")
        Close #iFile

    End If

ExitPoint:

    Exit Sub

ErrorHandler:

    MsgBox Err.Number & " - " & Err.Description
    Resume ExitPoint

     '// Debug only
    Resume

End Sub

Have you seen [This](http://stackoverflow.com/questions/11781320/download-attachment-from-outlook-and-open-in-excel) ? — Siddharth Rout, Jul 29 '16 at 18:12
I just looked at it. But pulling standard Outlook objects isn't the hard part. The hard part is pulling out specific parts of the email body, and placing them in their respective cells in the CSV. But, good looking! Thanks. — Jared Newnam, Jul 29 '16 at 18:24

Tim Williams · Accepted Answer · 2016-07-29T18:32:34.320

2

If you just do this:

Print #iFile, Replace(Msg.Body, vbCrLf, ",")

then it's going to save all of the content to the file.

What you will need to do is to first split the file on vbCrLf into an array of lines:

Dim arr
arr = Split(Msg.Body, vbCrLf)

Then you need to loop over those lines looking for the specific ones you want to extract: it would be best to place that into a function which you can call from your main code:

Untested:

Function LineContent(arr, txtHeader) as String
    Dim rv as string, i as long
    for i=lbound(arr) to ubound(arr)
        if arr(i) Like txtHeader & "*" then
            rv = trim(replace(arr(i),txtHeader,"")
            exit for
        end if
    next i

    LineContent = rv
End function

Then call that function like this:

Dim cLastName as String
cLastName = LineContent(arr, "Contact Last Name:")

Once you have all the variables you need you can append them in one line to your CSV.

Caveats: none of the values you want to extract can contain line breaks (that might be a problem for "job description"), and if any of the values could contain a comma you need to wrap them in "" before writing them to your CSV.

edited Jul 29 '16 at 18:32

answered Jul 29 '16 at 18:24

Tim Williams

122,926
8
79
101

++ Nice :) `rv = trim(replace(arr(i),txtHeader & ":","")` or `rv = trim(Split(arr(i),":")(1)` – Siddharth Rout Jul 29 '16 at 18:29
@SiddharthRout - I put the ":" in txtHeader when calling the function... – Tim Williams Jul 29 '16 at 18:31
Yeah I saw that :D. – Siddharth Rout Jul 29 '16 at 18:31
@TimWilliams - Regarding the line breaks... the paragraph lines do break, but they're not from manually entering the line break on the web form. It comes over as one line of text. Will that carry into the CSV as one cell? – Jared Newnam Jul 29 '16 at 19:11
Yes it should: it does contain commas though so be sure to wrap it in `""`. If any of the values can contain " then you need to escape those by doubling them up. – Tim Williams Jul 29 '16 at 19:27

Export email data to specific fields in CSV file (or XLS file)

1 Answers1