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 : http://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. clip_image001
    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. clip_image002
  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 (37)

  1. Anonymous says:

    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.

  2. JohnGuin says:

    Thanks for the tip - that would definitely cause problems with file names otherwise.

  3. Anonymous says:

    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.

  4. Anonymous says:

    How can the layman do this? Can you get me an executable that would do it instead?

  5. JohnGuin says:

    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 http://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.

  6. Anonymous says:

    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?

  7. Anonymous says:

    Jay - the OneNote 2007 text importer is at blogs.msdn.com/.../customer-feedback-results-in-another-powertoy.aspx

    It 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.

  8. Anonymous says:

    I got this to work. Thank you very much.

  9. Anonymous says:

    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), ""))

  10. Anonymous says:

    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.

  11. Anonymous says:

    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.

  12. Anonymous says:

    Jay - thanks for the list of broken characters.  You should test software 🙂

  13. Anonymous says:

    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!

  14. Anonymous says:

    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

  15. Anonymous says:

    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, contents

    and then way at the top add

    Dim 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.

  16. Anonymous says:

    Many thanks for this post. This collective knowledge bank is fantastic for everyone!

  17. Anonymous says:

    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)".

  18. JohnGuin says:

    You could navigate by hand to the Sticky Notes folder you want to export and change this line:

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

    to

    Set myNote = Application.ActiveExplorer.CurrentFolder()

  19. Anonymous says:

    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.

  20. JohnGuin says:

    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.

  21. Anonymous says:

    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.

  22. Anonymous says:

    Hi all - not a techie here...  Where do you add the text replace code, or do you run it separately?

  23. JohnGuin says:

    Run it separately, after you export the Outlook sticky notes.

  24. Anonymous says:

    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.

  25. Anonymous says:

    Sorry - one more piece of info... I get the following error when it stops:

    Run time error '76'

    Path not found

  26. JohnGuin says:

    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.

  27. Anonymous says:

    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!

  28. Anonymous says:

    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.

  29. Anonymous says:

    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.

  30. Anonymous says:

    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.

  31. Anonymous says:

    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 category

    Dim fname As String, ibi As Integer, contents as String

    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.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

      Next

    End Sub

  32. Anonymous says:

    Tom - Thanks so much!!!  I am going to try this... as the previous did not end up working for me.

  33. Anonymous says:

    Your Welcome Ramesh. Hopefully, it works for you.

  34. Anonymous says:

    Hello, I tried to run this macro and I get Error 52 "Bad file name or number". I sync my notes from my iPhone to Outlook 2013 and I do not necessarily have a "Subject" in the note. If I press F8 and go line by line, it stops at "Open fname & ".txt" For Output As #1  'This only saves note text, whereas item.SaveAs adds mod date, category". So, I can only assume that when this macro tries to write the filename it errors out because the file name is too long. Is there any way to limit the filename that is being generated? Any help would be much appreciated.

  35. Anonymous says:

    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

  36. Trisha Cplorado says:

    I'm no computer geek. My school days didn't include code writing.This code stuff is way over my head. Has any of you geniuses created an app to do that? I need to migrate office outlook 2003 into 2012 copyright version of OneNote packaged in Office 365. I bet I'm not the only one who would buy it.

Skip to main content