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
G Outlook 2021 (New) doesn't respect default browser Using Outlook 0
B Outlook or iPhone turning tabs into spaces in Outlook Notes Using Outlook 1
P newly installed Office 365 includes OLD Outlook Using Outlook 6
R Outlook ribbon menu default? Using Outlook 7
H Spam email in Gmail not visible in Outlook Using Outlook 3
J How to transfer Win 10 Outlook to new Windows 11 pc? Using Outlook 9
J Renegade spam URL line displayed in old local Outlook 365 email title Using Outlook 3
G Reduce whitespace in Outlook desktop Contact Cards display Using Outlook 3
C Outlook classic via 365 Using Outlook 2
Dr. Demento Analogous Outlook code to read info into an array (or collection or whatever) Outlook VBA and Custom Forms 7
S Repair Outlook Using Outlook 8
V Outlook Form ListBox is not editable Outlook VBA and Custom Forms 2
F Outlook's contacts Using Outlook 1
D Outlook 2003 stopped dead Using Outlook 2
G Cannot receive emails from gmail account in Outlook 365 Using Outlook 1
E "Cannot display the folder. MS Outlook cannot access the specified file location" Using Outlook 8
P Outlook 2016 Working Offline Using Outlook 2
Rupert Dragwater Cannot reestablish gmail (email address) account in Outlook 365 Using Outlook 11
O Outlook 365 synchronisieren Exchange Server Administration 1
kburrows Outlook Classic - JPG files are corrupted when opened or saved Using Outlook 3
F Sync Outlook Calendar Using Outlook 0
G Change default font size in sticky notes - Outlook Desktop 2021 Using Outlook 2
C VBA in "New Outlook?" Using Outlook 0
D New Outlook with Business Basic Plans Using Outlook 0
D Outlook 2021 not working with Outlook 2003 installed Using Outlook 5
D Outlook 2003 stopped working - get they dialog box asking for username & Password Using Outlook 2
T Outlook 2021 hangs in close on taskbar occasionally Using Outlook 1
M Duplicate removal feature in Outlook 2021 is faulty Using Outlook 2
D.Moore Outlook COM addins source folder Using Outlook 12
P Removing Outlook 365 Account from Send/Receive Using Outlook 3
kburrows Outlook Automatically Merging Contacts Using Outlook 2
A Outlook 2016 Outlook 2016 vs. New Outlook Using Outlook 4
D Outlook Desktop App Email Software Using Outlook 0
efire9207 VBA Outlook Contacts Outlook VBA and Custom Forms 6
M Outlook not logging in to server Using Outlook 0
J Outlook macro to run before email is being send Outlook VBA and Custom Forms 3
R Outlook 2021 change view Using Outlook 2
K Outlook font corrupted in some point sizes, resets on close/open Using Outlook 2
J Is the Windows Outlook Tasks module really going to be gone? Using Outlook 6
F Outlook 2010 and Hotmail Using Outlook 1
A Outlook 2021 needs 'enter' for people search Using Outlook 2

Similar threads

Back
Top