Solved

Exporting Outlook Notes to CSV

Posted on 2016-08-29
5
35 Views
Last Modified: 2016-10-07
When I export outlook notes to CSV files and open them in Excel the notes are not staying in the column and proper field.
They are being appended to the end of a list of contacts. (which is impossible to then recreate without tons of time)

I have well over 6000 contacts all with several lines of notes (in the open contact notes field) where I put the date of the event and usually some note, some event, etc.

The purpose of the excel is to sort them into particular groups, so that I as manager can monitor last contact on key accounts etc.

If someone knows an easy way to get these to line up correctly let me know.
0
Comment
Question by:Gerry Van Der Bas
5 Comments
 
LVL 23

Expert Comment

by:Brian B
ID: 41776511
When you open them in Excel, all they being separated out into individual columns? In not, you may need to use the text to columns wizard to break them out properly.

Perhaps you can help show the problem by attaching a small sample of the CSV and a sample XLS or picture of how they are appearing in Excel.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41782162
By Outlook notes, do you mean NoteItem objects or something else?
0
 

Author Comment

by:Gerry Van Der Bas
ID: 41782187
Inside an Outlook contact there is a note section (an open whiteboard for notes)
Normally I use these to record contacts and various notes of the contacts.
It makes the entire client information more transportable so I can send to those I manage, and keep sort of a running record of items done and discussed etc.

So, that said, I found a plugin called CodeTwo Outlook Export that allowed me to export with characters limited to 256.

This allowed me to have at least the last 256 characters, which was acceptable for my current need, but not necessarily if I wanted to export to a file for record keeping, or another reason requiring a more complete history of these notes.

I believe it may be because these notes have carriage returns, perhaps other images, links or other things which for some reason is somehow not exporting. When you open a .csv file in excel (which is really the better way so you can see everything, sort, etc, and again transport mass info) you can see some of the notes inserted in their proper column but other notes pertaining to that contact were showing 50 rows down by themselves in individual records, and of course not showing any relation to the contact.

Since these are private files I could not post the document but I show in the attachment a small excerpt .jpg so you can see that in this case after 162 normal listings a series of notes which runs to about row 4200 before there are more correctly supported records. I presume these are overflows of the notes, but you could imagine the work involved in moving these to an appropriate field for each listing.  (and finding only by the notes which record they might apply to.) Really impractical. Anyway I have a bandaid now with the plugin, but would like to know if someone has a solution that can allow an export of ALL the information from within the notes window. (AGAIN of a contact)
1
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 41782975
The field called Notes in the interface is called Body in VBA code (only one of many such confusing discrepancies).  Here is some code that imports data from various ContactItem fields into an Access table; the Notes field is a Memo field, to accommodate large amounts of data:
Public Sub ImportStandardContacts()
'Created by Helen Feddema 17-Dec-2011
'Last modified by Helen Feddema 25-Jan-2012

On Error GoTo ErrorHandler

   Set rst = CurrentDb.OpenRecordset("tblContacts", _
      dbOpenDynaset)
   Set appOutlook = GetObject(, "Outlook.Application")
   Set nms = appOutlook.GetNamespace("MAPI")
   
SelectContactsFolder:
   
On Error Resume Next
   
   Set fldContacts = nms.PickFolder
   
   If fldContacts Is Nothing Then
      GoTo ErrorHandlerExit
   ElseIf fldContacts.DefaultItemType <> olContactItem Then
      strPrompt = "Please select a Contacts folder"
      strTitle = "Select folder"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo SelectContactsFolder
   End If
   
On Error GoTo ErrorHandler
   
   strTitle = "Question"
   strPrompt = "Automatically overwrite duplicates (Yes) or ask for each duplicate (No)?"
   intResult = MsgBox(prompt:=strPrompt, _
      Buttons:=vbQuestion + vbYesNo, _
      Title:=strTitle)
   
   intNewCount = 0
   intUpdateCount = 0
   
   For Each itm In fldContacts.Items
      If itm.Class = olContact Then
         Set con = itm
         
         'Determine whether contact is already in tblContacts
         strFirstName = con.FirstName
         strLastName = con.LastName
         strFullName = strFirstName & " " & strLastName
         strSearch = "[FirstName] = " & Chr(39) & strFirstName _
            & Chr(39) & " And [LastName] = " & Chr(39) _
            & strLastName & Chr(39)
         Debug.Print "Search string: " & strSearch
         rst.FindFirst strSearch
         
         Debug.Print "Notes: " & con.Body
         
         If rst.NoMatch = True Then
            'Add contact as a new record
            rst.AddNew
            rst![FirstName] = con.FirstName
            rst![LastName] = con.LastName
            rst![Salutation] = con.NickName
            rst![StreetAddress] = con.BusinessAddressStreet
            rst![City] = con.BusinessAddressCity
            rst![StateOrProvince] = con.BusinessAddressState
            rst![PostalCode] = con.BusinessAddressPostalCode
            rst![Country] = con.BusinessAddressCountry
            rst![CompanyName] = con.CompanyName
            rst![JobTitle] = con.JobTitle
            rst![WorkPhone] = con.BusinessTelephoneNumber
            rst![MobilePhone] = con.MobileTelephoneNumber
            rst![FaxNumber] = con.BusinessFaxNumber
            rst![EmailName] = con.Email1Address
            rst![Notes] = con.Body
            rst.Update
            intNewCount = intNewCount + 1
         Else
            If intResult = vbYes Then
               rst.Edit
               rst![FirstName] = con.FirstName
               rst![LastName] = con.LastName
               rst![Salutation] = con.NickName
               rst![StreetAddress] = con.BusinessAddressStreet
               rst![City] = con.BusinessAddressCity
               rst![StateOrProvince] = con.BusinessAddressState
               rst![PostalCode] = con.BusinessAddressPostalCode
               rst![Country] = con.BusinessAddressCountry
               rst![CompanyName] = con.CompanyName
               rst![JobTitle] = con.JobTitle
               rst![WorkPhone] = con.BusinessTelephoneNumber
               rst![MobilePhone] = con.MobileTelephoneNumber
               rst![FaxNumber] = con.BusinessFaxNumber
               rst![EmailName] = con.Email1Address
               rst![Notes] = con.Body
               rst.Update
               intUpdateCount = intUpdateCount + 1
            ElseIf intResult = vbNo Then
               strTitle = "Duplicate contact"
               strPrompt = strFullName & " is already in tblContacts; " _
                  & "overwrite record with data from Outlook?"
               intReturn = MsgBox(prompt:=strPrompt, _
                  Buttons:=vbQuestion + vbYesNo, _
                  Title:=strTitle)
               If intReturn = vbYes Then
                  rst.Edit
                  rst![FirstName] = con.FirstName
                  rst![LastName] = con.LastName
                  rst![Salutation] = con.NickName
                  rst![StreetAddress] = con.BusinessAddressStreet
                  rst![City] = con.BusinessAddressCity
                  rst![StateOrProvince] = con.BusinessAddressState
                  rst![PostalCode] = con.BusinessAddressPostalCode
                  rst![Country] = con.BusinessAddressCountry
                  rst![CompanyName] = con.CompanyName
                  rst![JobTitle] = con.JobTitle
                  rst![WorkPhone] = con.BusinessTelephoneNumber
                  rst![MobilePhone] = con.MobileTelephoneNumber
                  rst![FaxNumber] = con.BusinessFaxNumber
                  rst![EmailName] = con.Email1Address
                  rst.Update
                  intUpdateCount = intUpdateCount + 1
               Else
                  GoTo NextItem
               End If
           End If
         End If
      End If
      
NextItem:
   Next itm
   
   Debug.Print "New count: " & intNewCount
   Debug.Print "Update count: " & intUpdateCount
   
   If intNewCount = 0 Then
      If intUpdateCount = 0 Then
         strPrompt = "No contacts added or updated"
      ElseIf intUpdateCount = 1 Then
         strPrompt = "No contacts added; 1 contact updated"
      ElseIf intUpdateCount > 1 Then
         strPrompt = "No contacts added; " & CStr(intUpdateCount) _
            & " contacts updated"
      End If
   ElseIf intNewCount = 1 Then
      If intUpdateCount = 1 Then
         strPrompt = "1 contact added; 1 contact updated"
      ElseIf intUpdateCount > 1 Then
         strPrompt = "1 contact added; " & CStr(intUpdateCount) _
            & " contacts updated"
      End If
   ElseIf intNewCount > 1 Then
      If intUpdateCount = 1 Then
         strPrompt = CStr(intNewCount) & " contacts added; " _
            & "1 contact updated"
      ElseIf intUpdateCount > 1 Then
         strPrompt = CStr(intNewCount) & " contacts added; " _
            & CStr(intUpdateCount) & " contacts updated"
      End If
   End If

   strTitle = "Import done"
   MsgBox prompt:=strPrompt, _
      Buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in ImportStandardContacts procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Sub

Open in new window


The Access table can then be exported to a comma-delimited file, as in this code:
Public Sub ExportToCSV()
'Created by Helen Feddema 3-Sep-2016
'Last modified by Helen Feddema 3-Sep-2016

On Error GoTo ErrorHandler
   
   Dim strTable As String
   Dim strCSVFileAndPath As String
   
   strTable = "tblContacts"
   strCSVFileAndPath = "C:\Users\Helen Feddema Home\Documents\CSV Files\Contacts.csv"
   DoCmd.TransferText transfertype:=acExportDelim, _
      TableName:=strTable, _
      FileName:=strCSVFileAndPath, _
      hasfieldnames:=True
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in ExportToCSV procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


You could probably combine these two procedures into one, if you wish.
1
 
LVL 13

Expert Comment

by:frankhelk
ID: 41833314
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Helen_Feddema (https:#a41782975)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Use email signature images to promote corporate certifications and industry awards.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now