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
 
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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
 
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
Good morning Diana, Let me look at this and will get back to you. Thank you very much fo the help!
 
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
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,
 
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
use .companies, not .companynames, to grab the company field.
 
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
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
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 0
L Opening People Outlook 2021 Using Outlook 0
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 2
O Outlook - Switch from Exchange to IMAP Using Outlook 0
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
H Integrating Alexa & Outlook Pro 2021 Using Outlook 2
Z Automatically adjust Outlook Reading Pane from bottom to right depending on portrait or landscape window Using Outlook 1
Rupert Dragwater Background colors not saving in Outlook 365 Using Outlook 15
petunia Outlook tasks module sunsetting? Exchange Server Administration 3
G Save emails as msg file from Outlook Web AddIn (Office JS) Outlook VBA and Custom Forms 0
D Outlook VBA forward the selected email to the original sender’s email ID (including the email used in TO, CC Field) from the email chain Outlook VBA and Custom Forms 3
U Outlook 2021 not showing contact cards in Searches Using Outlook 1
C Outlook - Macro to block senders domain - Macro Fix Outlook VBA and Custom Forms 2
H Outlook 365 O365 outlook calendar item editing Using Outlook 1
J Outlook 365 html inline images Using Outlook 0
Rupert Dragwater How to get Outlook 365 to open from websites Using Outlook 5
S Why do I have to close and reopen Outlook for macros to work? Outlook VBA and Custom Forms 2
J Outlook 2021 ScanPST errors (yet again ... sorry): repair button missing Outlook 2021 Using Outlook 0
HarvMan Outlook 365 - Rule to Move an Incoming Message to Another Folder Using Outlook 4
K Moved pst to new computer, now Gmail not coming into Outlook Using Outlook 7
S Email Macros to go to a SHARED Outlook mailbox Draft folder...NOT my personal Outlook Draft folder Using Outlook 2
F Running Scripts in Outlook 2021 Using Outlook 0
Nufc1980 Outlook "Please treat this as private label" auto added to some emails - Help. Using Outlook 3
S Outlook 2019 Custom outlook Add-in using Visual Studio Outlook VBA and Custom Forms 0
V Outlook macros no longer run until VB editor is opened Outlook VBA and Custom Forms 0
R Outlook 365 How to integrate a third-party app with Outlook to track email and sms? Using Outlook 2
e_a_g_l_e_p_i I can't believe what I am seeing when trying to install Outlook 2021 Using Outlook 9
Kika Melo Outlook Calendar deleted appointments not in Deleted Items folder Using Outlook 3
P How to get a QR code for automatic signin with Outlook for iOS Using Outlook 5
J Migrating Outlook Using Outlook 1
Retired Geek Outlook for the MAC with Yahoo accounts now very broken Using Outlook 9
S Outlook 2002- "Send" button has disappeared. Help please. Using Outlook 1

Similar threads

Top