Home / GitHub Page

First trial to create note from Outlook

Joplin API is opening up all possibilities of integrations :slight_smile:.

This piece of VBA (Visual Basic for Application) code to send the subject of selected emails from Outook to Joplin Desktop:

Public Sub SendToJoplin()

    Dim sJSONString As String
    Dim objItem As Outlook.MailItem

    For Each objItem In ActiveExplorer.Selection
        With CreateObject("MSXML2.XMLHTTP")
            .Open "POST", "http://127.0.0.1:41184/notes?token=?", False
            .Send "{ ""title"": """ & objItem.ConversationTopic & """}"
            Do Until .ReadyState = 4: DoEvents: Loop
                sJSONString = .ResponseText
        End With
    Next
    'Debug.Print sJSONString 'Uncomment to see joplin response
End Sub

Update:
Here is the gist for the complete code: https://gist.github.com/ramisedhom/0f34c5d6a8d73f0b98ac4bea2ec30be0

2 Likes

Trying to send email body to joplin:

.Send "{ ""title"": """ & objItem.ConversationTopic & """, ""body"": """ & objItem.Body & """ }"

This should send email body as plain text to the note, but got this error:

{“error”:“Internal Server Error: Unexpected token \r in JSON at position 60”}

Found the fix for above error:

{“error”:“Internal Server Error: Unexpected token \r in JSON at position 60”}

2 characters (“Carriage Return”, “New Line”) need to be replaced and “Double Quote” need to be escaped.

So the code to send email body as HTML:

Public Sub SendToJoplin()

    Dim sToken, sURL, sEscapedBody, sJSONString As String
    Dim objItem As Outlook.MailItem

    sToken = "??"
    sURL = "http://127.0.0.1:41184/notes?token=" & sToken

    For Each objItem In ActiveExplorer.Selection
        sEscapedBody = EscapeBody(objItem.HTMLBody)
        With CreateObject("MSXML2.XMLHTTP")
            .Open "POST", sURL, False
            .Send "{ ""title"": """ & objItem.ConversationTopic & """, ""body_html"": """ & sEscapedBody & """ }"
            Do Until .ReadyState = 4: DoEvents: Loop
                sJSONString = .ResponseText
        End With
    Next
    'Debug.Print sJSONString 'Uncomment to see joplin response
End Sub

Public Function EscapeBody(sText As String)
    EscapeBody = Replace(Replace(sText, Chr(34), "\" & Chr(34)), vbCrLf, "\n")
End Function

P.S. To send email body as plain text, use objItem.Body instead objItem.HTMLBody

Thanks for sharing, that’s pretty cool. How would you go about using this snippet from outlook? Is it something you attach to a button?

This only works for the installed version, right?
I must use the portable one, because afaik it is not possible to change the path were Joplin stores the notes in the installed version, right?

The Joplin’s API also works in the Portable version.
(And the Oultook part doesn’t care: it sends the request to the API address and doesn’t even know what handles it.)

This VBA procedure need to be added to Outlook VBA using Alt+F11 or through Developer ribbon:

image

Then paste the code in “ThisOutlookSession”:

Then the procedure could be triggered through button, Outlook rules, or through another VBA.

The simplest choice is add a button to “Quick Access Toolbar” like below:

File > Options > Quick Access Toolbar

1 Like

Works great. Perhaps it is worth to mention that “sToken” must be filled with the token shown in the Joplin App.
And yes, it also works with the portable version.

The only thing im missing is: Could something be implemented that allows to select the notebook the mail is clipped to?
For now it is placed somewhere and could only be found by searching in Joplin

And would it be possible to clip the whole mail including sender, date, subject, etc?

I was testing on joplin instance with 1 notebook so I didn’t think for this option and note immediately appear in this 1 notebook.

To select a notebook, parent_id need to be added to JSON. If you know your notebook id, use following code:
P.S. You need replace:

  1. REPLACE ME WITH YOUR TOKEN
  2. REPLACE ME WITH YOUR NOTEBOOK ID
Public Sub SendToJoplin()

    Dim sToken, sURL, sEscapedBody, sJSONString As String
    Dim objItem As Outlook.MailItem
    
    sToken = "REPLACE ME WITH YOUR TOKEN"
    sURL = "http://127.0.0.1:41184/notes?token=" & sToken

    For Each objItem In ActiveExplorer.Selection
        sEscapedBody = EscapeBody(objItem.HTMLBody)
        With CreateObject("MSXML2.XMLHTTP")
            .Open "POST", sURL, False
            .Send "{ ""title"": """ & objItem.ConversationTopic & """" _
            & ", ""parent_id"": ""REPLACE ME WITH YOUR NOTEBOOK ID""" _
            & ", ""body_html"": """ & sEscapedBody & """" _
            & " }"
            Do Until .ReadyState = 4: DoEvents: Loop
                sJSONString = .ResponseText
        End With
    Next
    'Debug.Print sJSONString 'Uncomment to see joplin response

End Sub

If you don’t know your notebook id, run this code to get all notebooks and their IDs:

Private Sub GetFoldersFromJoplin()
    
    Dim sToken, sURL, sJSONString As String
    
    sToken = "REPLACE ME WITH YOUR TOKEN"
    sURL = "http://127.0.0.1:41184/folders?token=" & sToken

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sURL, False
        .Send
        Do Until .ReadyState = 4: DoEvents: Loop
            sJSONString = .ResponseText
        End With
    Debug.Print sJSONString
    
End Sub

Check the response in “Immediate Window” (CTLR+G), copy and paste JSON response into any JSON formatter tool (e.g. https://jsoneditoronline.org/). You can easily copy the target notebook id and use it in the SendToJoplin() procedure.

1 Like

Thanks for that clarification, but this will mean I still have fixed notebook, where the mails are clipped to.
But what if I want to decide where to clipp a mail for each incoming mail, e.g. mail 1 => notebook test, mail 2 => notebook holiday, etc?

A workarround would be to create a notebook “clipped mails” and move all clipped mails from there to the final destiantion.
=> did this worked:), but the above mentioned would be nice:)

What about clipping not only the mail body but also the whole mail including sender, date, subject, etc?

I use an _Inbox notebook, since my OneNote time and not as a workaround.

With the majority of the mails I get such errors:

{"error":"Internal Server Error: Unexpected token \t in JSON at position ..."}
{"error":"Internal Server Error: Unexpected token \r in JSON at position ..."}

We probably have to use a dedicated JSON encoder.

To choose notebook, you can use InputBox function.

To clip another email fields, you need to choose mail properties and add them to the send method.

I’ll try to code both later on, and post the code here.

I search for that, I found that the following characters are reserved in JSON and must be properly escaped to be used in strings:

  • Backspace is replaced with \b
  • Form feed is replaced with \f
  • Newline is replaced with \n
  • Carriage return is replaced with \r
  • Tab is replaced with \t
  • Double quote is replaced with "
  • Backslash is replaced with \

This function need to be rewritten to escape above list of characters:

Public Function EscapeBody(sText As String)
    EscapeBody = Replace(Replace(sText, Chr(34), "\" & Chr(34)), vbCrLf, "\n")
End Function

You might have problems with utf8 encoding too. Maybe a lib like this one could help: https://github.com/VBA-tools/VBA-JSON

Would you replace EscapeBody procedure with following code and try:

Public Function EscapeBody(sText As String)
    EscapeBody = sText
    EscapeBody = Replace(EscapeBody, "\", "\\")                 'Backslash is replaced with \\
    EscapeBody = Replace(EscapeBody, Chr(34), "\" & Chr(34))    'Double quote is replaced with \"
    EscapeBody = Replace(EscapeBody, vbCr, "\r")                'Carriage return is replaced with \r
    EscapeBody = Replace(EscapeBody, vbLf, "\n")                'Newline is replaced with \n
    EscapeBody = Replace(EscapeBody, Chr(8), "\b")              'Backspace is replaced with \b
    EscapeBody = Replace(EscapeBody, Chr(12), "\f")             'Form feed is replaced with \f
    EscapeBody = Replace(EscapeBody, vbTab, "\t")               'Tab is replaced with \t
End Function

Let me know your testing results :slight_smile:

Perfect! :+1:

@laurent: No problems with UTF-8 until now.
I’m a bit surprised that VBA has no integrated JSON functions.

wonderful,i have tyid it and feel cool.i need it.but how to download image in email?

I think this will be a bit complicated but partially doable. I can see 2 types of images:

  1. Images in <img> tag if MailItem.BodyFormat is olFormatHTML. Most properly I should use third-party regex library to get the path in src.
  2. “Embedded” or inline images if MailItem.BodyFormat is olFormatRichText and those should be handled at MailItem.Attachments.

First both types need to be saved into local files,
Second, use Joplin API: POST /resources to upload them to Joplin.
Third, it will be doable to replace <img> with resource id but impossible to place the inline image since there is not placeholder for it in MailItem.HTMLBody as far as I see.

Cool looking forward. Would be great if this works:)