An Outlook routine to export sticky notes and then import them to OneNote

Bill was looking for a way to move his Outlook sticky notes to OneNote. Way back when, I wrote an addin for Outlook 2007 that would export Outlook 2007 notes to OneNote 2007 at : https://blogs.msdn.com/b/descapa/archive/2007/02/14/export-your-outlook-notes-to-onenote.aspx. I have never had time to update it to 2010, and Bill had a better idea anyway.

 

Here's what he had to say about his VBA code:

 

"Here’s the VBA code that I stored in Outlook.  This creates a main c:\MyOutlookNotes folder, then subfolders by Category.

File names are based on note Subject.  I then used your 2010 .txt to OneNote transfer program, doing each folder separately to maintain tabs as my categories in OneNote.

I needed to massage the note subject as some had (#)  appended on the end for some reason. Also some of my subjects had PDA folder locations appended to the front, which I removed from the file name.

Probably a more efficient way exists to code this, but I don’t plan on using this again.

Hope this is helpful for someone."

 

He chose to export his Outlook sticky notes to text format, then use my text importer to import them. He made one other change - he has sorted his Outlook notes by category, and exports all the notes that have the same category to the same folder on the hard drive. He wrote a short macro for Outlook to do this, so you don't even need to install a utility to get his routine. Here's the code:

 

Sub ExportNotes()

'WJ Pommersheim Dec 23, 2010

'Exports all of Outlook Notes (text) into text files,

'with separate folders for each category.

'These will be in c:\MyOutlookNotes, with subfolders by category

Dim fname As String, ibi As Integer

Dim myNote As Folder, myItem As NoteItem

Dim TopDir As String, TheCat As String

    TopDir = "c:\MyOutlookNotes\"

    If Len(Dir(TopDir, vbDirectory)) = 0 Then MkDir TopDir 'create top directory if necessary

    Set myNote = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderNotes)

    For ibi = 1 To myNote.Items.Count

        ChDir TopDir

        If myNote.Items(ibi).Class = olNote Then    'Text notes only

            Set myItem = myNote.Items(ibi)

            TheCat = myItem.Categories

            fname = myItem.Subject

            If Right(fname, 1) = ")" Then fname = Left(fname, Len(myItem.Subject) - 3)  'Remove (#) if present

            fname = Right(fname, Len(fname) - InStr(fname, "\"))    'Remove folder text if present (old PDA format)

            If Len(Dir(TheCat, vbDirectory)) = 0 Then MkDir TheCat 'Need to create directory

            ChDir TopDir & TheCat

            Open fname & ".txt" For Output As #1  'This only saves note text, whereas item.SaveAs adds mod date, category

            Print #1, myItem.Body

            Close #1

        End If

   Next

End Sub

 

If you have never used VBA in Outlook and want to try this out, here's a quickstart:

1. Start Outlook
2. Hit ALT+F11. This launches VBA for Outlook.
3. On the left will be the Project tree. Expand it until it looks like this:
4. 
   1. You should "ThisOutlookSesstion." Double click it
   2. You will see the code editor open to the right. Paste the code above into it:
5. 

1. Hit F5 to run it. It goes pretty fast.
2. If you want to step through it line by line, use F8.

 

That's it. When you are done, you will have a c:\MyOutlookNotes folder with all your notes in TXT format. Now you can import them to OneNote.

 

Thanks for the code, Bill!

 

Questions, comments, concerns and criticisms always welcome,

John

Comments

• Anonymous
  March 26, 2011
  I found I had problems with the code because I had characters in file names that didn't work.  I had to add some replace statements and work through my data to see what I had done.  I suspect there's a way to get them all, but this replace just gets the ones I hit and was built up incrementally as I went through the data.TheCat = Replace(myItem.Categories, "/", "-")fname = Trim(Replace(Replace(Replace(Replace(myItem.Subject, "/", "-"), "?", ""), "*", ""), Chr$(9), ""))Hope this helps somebody.
• Anonymous
  March 27, 2011
  Thanks for the tip - that would definitely cause problems with file names otherwise.
• Anonymous
  June 14, 2011
  Thank you so much for your time, and the precise instructions. This worked very well in tandem with your text to onenote import script. WIth the inability of Windows Mobile 7 to sync Outlook notes, your work was really useful.
• Anonymous
  October 05, 2011
  How can the layman do this? Can you get me an executable that would do it instead?
• Anonymous
  October 06, 2011
  As a temporary workaround, you can select all the sticky notes you have in outlook (CTRL+A), then click File | Save As and save them as a text file.  Then you can copy that text into OneNote.  This makes one big page out of all your notes, which may or may not be desirable.  But it might get you unblocked.Theres is also an Outlook addin I did for Outlook 2007 and OneNote 2007 that may work for you at www.codeproject.com/.../OutlookToOneNoteAddin.aspx.  The key here is that you will need to create a notebook in OneNote 2007 format to use as the location to send the notes.  You can then convert it to 2010 format, or drag/drop the section into a permanent notebook location.
• Anonymous
  January 18, 2012
  I ran the VBA code above and have my Outlook Notes in .txt files under my categories.  But the importer I see above works with Notes 2010.  I need to import into Notes 2007.   Is there an importer that pulls the categories and notes files into OneNote 2007 and keeps the categories?Also, does note 2007 work with the new OneNotes app for the iphone?
• Anonymous
  January 18, 2012
  Jay - the OneNote 2007 text importer is at blogs.msdn.com/.../customer-feedback-results-in-another-powertoy.aspxIt retains the data of the categories, but does not convert them to OneNote tags.The new iPhone/iPad apps use Skydrive, which requires OneNote 2010.  OneNote 2007 will not work with these applications.I hope this helps.
• Anonymous
  January 24, 2012
  I got this to work. Thank you very much.
• Anonymous
  January 27, 2012
  I had to add some more characters to watch out for:TheCat = Replace(myItem.Categories, "/", "-")fname = Replace(Replace(Replace(Replace(Replace(myItem.Subject, "", "-"), "[", "("), "]", ")"), ":", ""), Chr$(34), Chr$(39))fname = Trim(Replace(Replace(Replace(Replace(fname, "/", "-"), "?", ""), "*", ""), Chr$(9), ""))
• Anonymous
  January 27, 2012
  I also found a way to see what note is causing a problem. After running the VBA subroutine and you get an error, the last note completed is good, it will be the next note that caused the problem.  You can see which note by comparing the text that was created with what is in Outlook by sorting your notes in Outlook.  In Outlook Notes set the Current View to Notes List and then click on the Created column header and sort by the earliest date first.
• Anonymous
  January 27, 2012
  I also had to upgrade to OneNote 2010 from OneNote 2007 in order to run the Text Importer.   Everything imported correctly and now I have all my notes in OneNote. I then set up OneNote on my PC to sync that folder to SkyDrive..  Thanks for everyone's help and pointers on this process.  OneNote on my PC and Iphone work seamlessly, even better than my PC and Palm did with more functionality.
• Anonymous
  January 27, 2012
  Jay - thanks for the list of broken characters.  You should test software :)
• Anonymous
  December 12, 2012
  Almost a year later ... just ran this script to export my Outlook notes so I can get them into OneNote and start using it. The script (with corrections above) worked great. Thank you so much for posting it!
• Anonymous
  August 19, 2013
  Thanks for the work on that handy script.Could someone please explain the following comment from the script...<'This only saves note text, whereas item.SaveAs adds mod date, category>by showing me how to include note category and modifictation date in the text files.Thank you
• Anonymous
  August 27, 2013
  Rob - the routine already sorts the notes into subfolders based on category.  You would need to get LastModificationTime from the item (myItem.LastModificationTime) and add it to the Print command.So delete the given Print command and replace with this:           contents = myItem.Body & " " & myItem.CreationTime & " " & myItem.LastModificationTime           Print #1, contentsand then way at the top addDim contents as String (to the end of the first Dim command)Now the categories will be preserved in the folder name and the last modified time will be added to the end of each exported text file.Make since? I can roll this up as a whole new macro if you want.
• Anonymous
  March 09, 2014
  Many thanks for this post. This collective knowledge bank is fantastic for everyone!
• Anonymous
  August 21, 2014
  Thanks all very much for the VBA routine and mods. What change would need to be made to the VBA to make it operate on the Notes in Personal Folders in Outlook 2013? The VBA is currently operating on the Outlook 2013 folder titled "Notes (this computer only)".
• Anonymous
  August 21, 2014
  The comment has been removed
• Anonymous
  August 21, 2014
  Thanks John, Would I manually navigate to the folder in Outlook or Windows Explorer? I'm not knowledgeable where it's located in Explorer within the Outlook data tree.
• Anonymous
  August 21, 2014
  In Outlook.  Just open the Sticky Notes folder you want to export, then run the macro.  The macro will use the current folder in the Active (Outlook) Explorer window.
• Anonymous
  August 21, 2014
  The Error message "The macros in this project are disabled" is occurring since the last change. Haven't been able to find the problem source.
• Anonymous
  August 21, 2014
  Does this help?  answers.microsoft.com/.../56def698-ac75-4e2e-ba00-3d20d62420ec
• Anonymous
  December 12, 2014
  Hi all - not a techie here...  Where do you add the text replace code, or do you run it separately?
• Anonymous
  December 12, 2014
  Run it separately, after you export the Outlook sticky notes.
• Anonymous
  December 13, 2014
  Hi there - I tried to run the export and it only extracted a small part of the notes I have... I thought the text above was to correct characters that would stop the export from running. I tried to figure out which Note is stopping it - no luck so far.Any ideas while the export isn't completing otherwise?Thanks so much.
• Anonymous
  December 13, 2014
  Sorry - one more piece of info... I get the following error when it stops:Run time error '76'Path not found
• Anonymous
  December 14, 2014
  Did you update the code to use Bill Hyde's code (the very first post here)?  There is probably a character - such as a question mark - that is being used in the path.
• Anonymous
  December 15, 2014
  Thanks John, that helps.  I didn't ask my previous question clearly enough, and misunderstood that I had to run Bill's code separately. Can you please tell me what part of the original script do I replace with Bill's code? Sorry - still learning on this kind of stuff.  Thanks!
• Anonymous
  December 17, 2014
  Sorry to keep asking questions!  Where do I put his code in the original code and or what do I replace?  I tried and still got an error.  Thanks.
• Anonymous
  December 17, 2014
  And now I get a new message: "An error occured when trying to verify the project's signature."  Sorry! Not sure what I am doing wrong.
• Anonymous
  January 21, 2015
  It eventually worked, sort of.  I cobbled together the code from Bill Hyde, added another character to the list to replace, and made it through. I still didn't have a lot of luck with the step by step troubleshooting to figure out which note was hanging up though. If someone can clarify exactly how the code should be combined, would still be appreciated.  Thanks.
• Anonymous
  February 18, 2015
  Hello, I know this post is old. I just wanted to say thank you for this macro. I combined the code from all the edits mentioned in the comments. Hopefully this helps someone else. I had around 1,600 notes in Outlook and there seems to be limit where it will not run if there are more than 511 Notes. So, I had to split the Notes between different users on the left hand column in order for it to work with no issues. If some one knows how to get around the 511 limit, that would be awesome. Anyway, here is the code:Sub ExportNotes()'WJ Pommersheim Dec 23, 2010'Exports all of Outlook Notes (text) into text files,'with separate folders for each category.'These will be in c:MyOutlookNotes, with subfolders by categoryDim fname As String, ibi As Integer, contents as StringDim myNote As Folder, myItem As NoteItemDim TopDir As String, TheCat As String   TopDir = "c:MyOutlookNotes"   If Len(Dir(TopDir, vbDirectory)) = 0 Then MkDir TopDir 'create top directory if necessary   Set myNote = Application.ActiveExplorer.CurrentFolder()   For ibi = 1 To myNote.Items.Count       ChDir TopDir       If myNote.Items(ibi).Class = olNote Then    'Text notes only           Set myItem = myNote.Items(ibi)           TheCat = Replace(myItem.Categories, "/", "-")           fname = Replace(Replace(Replace(Replace(Replace(myItem.Subject, "", "-"), "[", "("), "]", ")"), ":", ""), Chr$(34), Chr$(39))           fname = Trim(Replace(Replace(Replace(Replace(fname, "/", "-"), "?", ""), "*", ""), Chr$(9), ""))           If Right(fname, 1) = ")" Then fname = Left(fname, Len(myItem.Subject) - 3)  'Remove (#) if present           fname = Right(fname, Len(fname) - InStr(fname, ""))    'Remove folder text if present (old PDA format)           If Len(Dir(TheCat, vbDirectory)) = 0 Then MkDir TheCat 'Need to create directory           ChDir TopDir & TheCat           Open fname & ".txt" For Output As #1  'This only saves note text, whereas item.SaveAs adds mod date, category           contents = myItem.Body & " " & myItem.CreationTime & " " & myItem.LastModificationTime           Print #1, contents           Close #1       End If  NextEnd Sub
• Anonymous
  February 19, 2015
  Tom - Thanks so much!!!  I am going to try this... as the previous did not end up working for me.
• Anonymous
  February 19, 2015
  Your Welcome Ramesh. Hopefully, it works for you.
• Anonymous
  February 19, 2015
  The comment has been removed
• Anonymous
  May 19, 2015
  I had my notes in different folders. so I used the powershell cobbled together from the net to export the notes into folders corresponding to my notes folders and then imported each folder using the text importer - #Script to convert Outlook Notes to text, so that they can import into OneNote #Tried  the OneNote provider for powershell from cid-83dfdc4530b17dd8.skydrive.live.com/.../OneNotePowershell.msi #to push directly into OneNote, but this provider doesnt work for OneNote 2010. #Export notes to file system and then use the text importer to import, then prune and graft sections as needed #Watch out for Outlook security prompts - these will prevent the script from getting access - #WHere should Notes be stored in OneNote - $OneNotePath = "E:TempMyNotes" #This needs to exist to begin with - #Constants used by Outlook set-variable olFolderNotes -option Constant -value 12 set-variable olNote -option Constant -value 44 function Process-Folder { Param ($objParentFolder) write-host "Processing folder:" + $objParentFolder.Name    $FolderPath = "." + ($objParentFolder.Name)    #Create a section for this folder -    if (    (test-path  -Path $FolderPath   ) -eq $false   )        {        Write-Host "Creating folder: $FolderPath"        New-Item -Path $FolderPath -type directory        }    cd $FolderPath ForEach ( $myNote in $objParentFolder.Items ) #Process Notes { if ($myNote.Class -eq $olNote) { $Subject = $myNote.Subject.Trim()            $Subject = $Subject.Replace(":", "-") $Subject = $Subject.Replace("", " OR ") $Subject = $Subject.Replace("/", " OR ") $Body = $myNote.Body write-host "`t$Subject"            $NotePath = "." + $Subject

           write-host "Note path is $NotePath"

           If (    (test-path -Path $NotePath) -eq $false    )                {                write-host "Creating note: $NotePath"                new-item -Path $NotePath -ItemType file                }            $Body | Out-File -FilePath $NotePath } } cd .. #comment if folder structure mirroring OUtlook structure is desired ForEach ( $myFolder in $objParentFolder.Folders ) { Process-Folder $myFolder }    #cd .. #Dont want nested structure, as the text importer gets them all into the same section } #Change directory to the path where Notes are to be stored. Each call into Process-Folder will create a section in this path #The PS provider cannot create SectionGroup, so the Outlook folder hierarchy is going to get flattened, but notes will be kepts in respective folders pushd cd ($OneNotePath) $objOutlook = New-Object -com Outlook.Application $objNamespace = $objOutlook.GetNamespace("MAPI") $objNotes = $objNamespace.GetDefaultFolder($olFolderNotes) Process-Folder($objNotes) popd

• Anonymous
  October 26, 2015
  The comment has been removed