I want to migrate Outlook journal to excel including contact names

Status
Not open for further replies.
F

Francois Plante

Member
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
 
Hi,
Thanks for the feedback, I really appreciate your help. I will get back to you shortly
 
Status
Not open for further replies.
< Outlook 2010 sent emails vanish | Create Calendar Alert Event form Mail Subject line? >
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
S How to Move Data from Mac Outlook to Windows Outlook? Using Outlook 2
T What is the best way of using the Classic Outlook client to change my email address? Using Outlook 4
T Creating a second email account on an Outlook client Using Outlook 2
Daviddavidsonz How to Schedule Emails in Microsoft Outlook Using Outlook 0
S Rule not running in Outlook Classic unless... Using Outlook 9
J column with actual email address in Outlook Classic Using Outlook 2
S Not receving all email since converting from Outlook 2007 to Outlook Classic Using Outlook 3
Rupert Dragwater Outlook 365 taking too long to synchronize each time email is being checked Using Outlook 17
Hornblower409 Outlook VBA Code Example - Unified inbox in Outlook Classic Outlook VBA and Custom Forms 5
J Need replacement for iTunes to locally (USB) sync. iPhone 17 to Classic Outlook 2024 Using Outlook 4
Thomas Fast Best way to manage incoming customer emails automatically in Outlook? Using Outlook 1
I Outlook 2024 Windows 11 Gmail - sudden log in problems Using Outlook 13
S Outlook Classic on 2024 Using Outlook 10
L Fresh Install of Windows 11, saved previous image, how to retrieve Outlook Contacts Using Outlook 10
C Can't Use Custom Contact form in Outlook Classic since early January 2026 Outlook VBA and Custom Forms 7
e_a_g_l_e_p_i Need help updating email in Outlook 2021 Using Outlook 10
V Outlook spam filter misbehaving Using Outlook 9
L what are the downsides of running both classic and new outlook on same win 11 pc? Using Outlook 2
P Preventing permanent deletions in Outlook on the Web Using Outlook 0
L any trick to embedding images in new outlook and outlook on the web contacts? Using Outlook 4
L new outlook contacts searching Using Outlook 5
R Outlook 2010 Outlook 2010 migration question Using Outlook 2
W New Outlook PEOPLE blank Using Outlook 6
C New Outlook issues with Gmail, particularly labels/folders Using Outlook 3
E What is the next workaround for macro in New Outlook 1.2025.1111.100 Outlook VBA and Custom Forms 3
E Need to digitally sign macro but VBA\Outlook crash Outlook VBA and Custom Forms 4
P Outlook 2003 no longer opens "without" Folder List Showing in Navigation Pane Using Outlook 2
Hornblower409 Outlook 2010 - Never ending update Using Outlook 0
V Outlook created new profile? Using Outlook 1
J Outlook inbox question Using Outlook 4
T How to Add AT&T Contacts to Outlook 365 Using Outlook 5
F Outlook 2021 outlook on iPhone Using Outlook 1
F Outlook 2021 Outlook on iPhone asks for password Using Outlook 0
P ics calendar entries suddenly open up new Outlook Using Outlook 3
P Outlook "forgets" password until system rebooted Using Outlook 2
N Why does Outlook keeping adding to the email address I have in my notes portion of a contact? Using Outlook 2
M Anyone integrated AI website builders with Outlook for automated client communications? Using Outlook 4
V Gmail in Outlook Using Outlook 2
T Where has the Copilot icon gone in my Outlook desktop client? Using Outlook 10
P New way by Microsoft to get people to use the new Outlook Using Outlook 4
C How to keep emails in account in Outlook after closing the IMAP account Using Outlook 1

Similar threads

Back
Top