I want to migrate Outlook journal to excel including contact names

Status
Not open for further replies.
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
I would like to know how to migrate Outlook 2010 Journal fields into excel spreadsheet including the contact names.
using the Import/export fucntion, the conatct names, that are linked to the journal note, do not export.
Any special batch file to do?

Thanks for the help

François
 
Try a custom view with the fields you need in a table (list) view. Select all, copy and paste into Excel.

You definitely won't be able to use the Import/Export wizard but might be able to use a macro.
 
I already did but in my note body there are several lines and the copy paste only gives me the firts line :(
I don't know how macro works though
 
I'll see if i have a macro we can use. The other option (if it's a one time thing) is to export and copy - this will get you the full notes field and anything not exported. You'll need a unique field (usually the date/time) you can sort on to match the records.
 
I tweaked the code at http://www.slipstick.com/developer/code-samples/macro-export-outlook-fields-excel/ to use the journal entries.

Code:
Option Explicit
Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim currentExplorer As Explorer
Dim Selection As Selection
Dim olItem As Outlook.JournalItem
Dim obj As Object
Dim strColB, strColC, strColD, strColE, strColF, strColG, strColH       As String
               
' Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
strPath = enviro & "\Documents\test.xlsx"
     On Error Resume Next
     Set xlApp = GetObject(, "Excel.Application")
     If Err <> 0 Then
         Application.StatusBar = "Please wait while Excel source is opened ... "
         Set xlApp = CreateObject("Excel.Application")
         bXStarted = True
     End If
     On Error GoTo 0
     'Open the workbook to input the data
     Set xlWB = xlApp.Workbooks.Open(strPath)
     Set xlSheet = xlWB.Sheets("Sheet1")
    ' Process the message record
    
    On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
  For Each obj In Selection
    Set olItem = obj
   
'collect the fields
' journal
With olItem
    strColB = .Subject
    strColC = .Type
    strColD = .Body
    strColE = .Start
    strColF = .Duration
    strColG = .ContactNames
    strColH = .Categories
End With
'write them in the excel sheet
  xlSheet.Range("B" & rCount) = strColB
  xlSheet.Range("c" & rCount) = strColC
  xlSheet.Range("d" & rCount) = strColD
  xlSheet.Range("e" & rCount) = strColE
  xlSheet.Range("f" & rCount) = strColF
  xlSheet.Range("g" & rCount) = strColG
  xlSheet.Range("h" & rCount) = strColH
 
'Next row
  rCount = rCount + 1
Next
     xlWB.Close 1
     If bXStarted Then
         xlApp.Quit
     End If
    
     Set olItem = Nothing
     Set obj = Nothing
     Set currentExplorer = Nothing
     Set xlApp = Nothing
     Set xlWB = Nothing
     Set xlSheet = Nothing
End Sub
 
Good morning Diana, Let me look at this and will get back to you. Thank you very much fo the help!
 
Good morning Diane, it works great, thank you very much. There is one thing I did not realize though, some of my customers have the same exact name. Is there a way where I can add the Company name in another column. I used your codes and added lines for the Company and then tried with the spelling CompanyNames but nothing shows up. I'm trying to find out if that field has another name than what I see. Canb you help me out?
Regards,
 
here is what I tried but it gives me an error


Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim currentExplorer As Explorer
Dim Selection As Selection
Dim olItem As Outlook.JournalItem, contactItem
Dim obj As Object
Dim strColB, strColC, strColD, strColE, strColF, strColG, strColH, strColI, strColJ As String

' Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
strPath = enviro & "\Documents\testfp.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Sheet1")
' Process the message record

On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection
Set olItem = obj

'collect the fields
' journal
With olItem
strColB = .Subject
strColC = .Type
strColD = .Body
strColE = .Start
strColF = .Duration
strColG = .ContactNames
strColH = .Categories

'collect the fields
' contact

strColI = .CompanyNames
strColJ = .BusinessAddressPostalCode

End With
'write them in the excel sheet
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("c" & rCount) = strColC
xlSheet.Range("d" & rCount) = strColD
xlSheet.Range("e" & rCount) = strColE
xlSheet.Range("f" & rCount) = strColF
xlSheet.Range("g" & rCount) = strColG
xlSheet.Range("h" & rCount) = strColH
xlSheet.Range("i" & rCount) = strColI
xlSheet.Range("j" & rCount) = strColJ

'Next row
rCount = rCount + 1
Next
xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If

Set olItem = Nothing
Set obj = Nothing
Set currentExplorer = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
 
Does it error on any specific line?
 
use .companies, not .companynames, to grab the company field.
 
Hi,
Thanks for the feedback, I really appreciate your help. I will get back to you shortly
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
D Outlook 2016 Migrate 'On My Computer' (local storage) Calendar from Mac Outlook to Exchange Account Using Outlook 5
B Migrate Outlook 2007 to Office 365 Using Outlook 3
T Migrate POP3 Folders and Emails to outlook.com via Outlook 2013 Using Outlook.com accounts in Outlook 2
J migrate outlook mail folders from user's old workstation to their new pc Using Outlook 6
C Migrate Exchange to Office 365 Using Outlook 2
J Migrate from godaddy 365 to office 365 home Using Outlook 8
P Migrate Office 365 mailbox to a different on-premises domain Using Outlook 1
T OL BCM 2007 - Possible to migrate SQL Server Express 2012 ? BCM (Business Contact Manager) 2
P Migrate profile - cached mode setting Using Outlook 2
A Outlook can't remember outlook.com, Exchange password. Using Outlook 0
S Related messages show in main Outlook window vice new Advanced Find windows Using Outlook 1
H Force Outlook 2019 with GMail 2-Step to Require Login? Using Outlook 0
G Retaining Tabs in outlook body Using Outlook 2
V Setting up Outlook 2021 on new computer Using Outlook 2
G Add Map It button to Custom Contacts Form in Outlook Outlook VBA and Custom Forms 1
X Custom icon (not from Office 365) for a macro in Outlook Outlook VBA and Custom Forms 1
Victor_50 Problem - Google Workspace will stop "unsafe" access to Outlook end 2024 Using Outlook 3
C New pc, new outlook, is it possible to import auto-complete emailaddress Using Outlook 4
T Outlook 365 won't take new working password Using Outlook 0
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
P Can't add custom field to custom Outlook form, it always adds to the Folder instead Outlook VBA and Custom Forms 2
B Sync Outlook Public Folders to Contacts Using Outlook 2
D Delete Outlook emails from MS server Using Outlook 12
B Outlook tasks and PDF Using Outlook 4
D Outlook 2019 is no longer asking for password ... Using Outlook 5
Kika Melo How to mark as Junk any message not from Contacts (in Outlook.com) Using Outlook 3
L Outlook attachments from OneDrive as links Using Outlook 0
G Outlook 365 My iCloud Outlook doesn’t work after reinstalling Microsoft365 on Windows 10 PC – now I get error message on contacts and calendar Using Outlook 1
T How to Export & Import GMAIL Contacts into Outlook 2021 ? Using Outlook 4
M Synchronization and backup of Outlook from local to server. Using Outlook 8
T How to get an EVENT COLOR option in Outlook 2021 ? Using Outlook 0
K How can I delete an e-mail from Outlook Using Outlook 1
V Outlook Error The Attempted operation Failed. An Object Could Not be found Outlook VBA and Custom Forms 0
P Yahoo/IMAP folder rename by Outlook desktop 365 Using Outlook 0
A Outlook 2019 folder counter Using Outlook 0
A Relocate Search Bar in Outlook Using Outlook 2
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 10
L Opening People Outlook 2021 Using Outlook 2
e_a_g_l_e_p_i Outlook 2021 not letting me setup my Gmail using pop Using Outlook 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
P Can no longer sync Outlook with iPhone calendar after iPhone update to 17.1.1 Using Outlook 7
O Outlook - Switch from Exchange to IMAP Using Outlook 2
e_a_g_l_e_p_i Is it possible to have a reminder in Outlook 2021 for every 90 days Using Outlook 3
farrissf Outlook 2016 Optimizing Email Searches in Outlook 2016: Seeking Insights on Quick Search vs Advanced Search Features Using Outlook 0
C Advanced search terms for "Outlook Data File" Using Outlook 1
N Reply to Outlook messages by moving messages to a specific Outlook folder Outlook VBA and Custom Forms 1
O How to find out the domain and server settings that my Outlook is using? Using Outlook 2
A Outlook 365 (OutLook For Mac)Move "On My Computer" Folder Items From Old To New Mac Computer Using Outlook 3

Similar threads

Back
Top