First trial to create note from Outlook

rami.sedhom · 22 December 2019 15:35

Joplin API is opening up all possibilities of integrations .

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

rami.sedhom · 22 December 2019 16:00

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"}

rami.sedhom · 22 December 2019 16:54

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

laurent · 22 December 2019 22:28

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?

iknow79 · 22 December 2019 23:30

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?

zblesk · 22 December 2019 23:58

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

rami.sedhom · 23 December 2019 09:22

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

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

iknow79 · 23 December 2019 20:59

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?

rami.sedhom · 23 December 2019 23:06

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:

REPLACE ME WITH YOUR TOKEN
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.

iknow79 · 24 December 2019 00:25

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?

Tekki · 24 December 2019 07:54

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

Tekki · 24 December 2019 09:03

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.

rami.sedhom · 24 December 2019 10:28

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.

rami.sedhom · 24 December 2019 10:42

Tekki:

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.

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

laurent · 24 December 2019 10:57

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

rami.sedhom · 24 December 2019 13:51

Tekki:

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.

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

Tekki · 24 December 2019 18:15

Perfect!

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

puyizhen · 25 December 2019 07:03

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

rami.sedhom · 25 December 2019 23:40

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

Images in <img> tag if MailItem.BodyFormat is olFormatHTML. Most properly I should use third-party regex library to get the path in src.
"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.

iknow79 · 26 December 2019 01:39

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

Topic		Replies	Views
Export Outlook Notes to Joplin Apps	1	335	4 April 2024
Unexpected token API error Support	3	1826	8 June 2019
How to handle emails with Joplin desktop Lounge tips-and-tricks	7	842	18 April 2024
Export email from Outlook to Joplin Apps	2	1619	14 April 2022
Joplin-instant-mail instantly converts email into a note or todo item in Joplin Apps	6	1937	16 October 2020

First trial to create note from Outlook

Related topics