Hi, thanks to google I have managed to find some code to enable me to extract emails to transfer the data onto an excel spreadsheet. I would like to take this further by splitting the Outlook Body Text into individual cells but not sure how. The following is my current code:
Sub EmailFolderToExcel()
Set objApp = Application
Dim olns As Outlook.Namespace
Set olns = Outlook.GetNamespace("MAPI")
Set myinbox = olns.PickFolder
'Set myItems = myinbox.Items
Dim xlApp As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim EachElement()
Dim myRecipient As Outlook.Recipient
Dim ExcelWasNotRunning As Boolean
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set xlApp = New Excel.Application
xlApp.Visible = True
End If
On Error GoTo 0
Set wkb = ActiveWorkbook
Set wks = wkb.Sheets(1)
With wks
StartCount = 1 'how many emails (start at 1 to leave row one for headers)
strEmailContents = ""
For Each outlookmessage In myinbox.Items
StartCount = StartCount + 1 'increment email count
.Range("A" & StartCount).Value = outlookmessage.ReceivedTime
.Range("B" & StartCount).Value = outlookmessage.SenderName
.Range("C" & StartCount).Value = outlookmessage.Subject
.Range("D" & StartCount).Value = outlookmessage.SenderEmailAddress
.Range("D" & StartCount).Value = outlookmessage.Body
Next
End With
Set myOlApp = Nothing
Set olns = Nothing
Set myinbox = Nothing
Set myItems = Nothing
End Sub
Currently the outlookmessage.Body is pasted into 1 cell, but the following is an example of how the email body looks like:
1) Please select type of change:
Temporary
2) Please complete:
Name:
Humpty
ID:
12345678
Reason for Change:
Plan Leave
Current shift:
08:00 - 17:00
Date of Change:
16/10/12
End date if applicable:
16/10/12
If somebody could please advise me or if there are any tutorials, as its all well, somebody writing this for me, but I want to make sure I learn at the same time as to how what the code all means.
Thanks in advance.
Bookmarks