Stop copy-pasting, or how to turn an Outlook email into a Jira task in one click

Hey! My name is Karina Suvorova, at Severstal I am engaged in the automation of user processes. How often do you receive letters and meetings with tasks that need to be completed within a certain time frame? I have a lot. And my colleagues, especially those whose working day consists of such “mail tasks”, even more.

In order not to forget anything, you need a convenient management tool, with categorization and the ability to analyze load, repeatability and actually spent time. And it is – Jira.

But the registration process in Jira is also time consuming – copying the subject and name of the customer, marking the time spent on the task, and performing a bunch of other boring actions. Because of this routine, many departments continue to maintain task lists in notepads and Excel.

To close this gap, we have developed a great solution that is available to everyone and requires no training to support. It works with Outlook. All you need is Jira to host and structured issue storage, and Visual Basic for Applications (VBA) to create them.

For whom and how does the intermediary work, which transfers data from mail to Jira

Our solution will be useful for those who are already working with Jira and want to simplify the process of creating issues (or bypass it altogether). And for those who actively keep timesheets in the good old Excel, and then suffer from sharing, freezing, updating, merging … well, you yourself know everything. Let’s see how this thing looks from the user side and from the development side.

User side work

I will describe the step-by-step actions of the user.

Step 1. Select the letter or scheduled meeting for which you want to create an issue in Jira. This can be an open letter (meeting) window:

Or just a highlighted Outlook item:

Step 2 Run macro. The launch button can be located both in the main Outlook window and in the letter (appointment) itself:

The appearance and name of the button can be changed.

Step 3 A window will open with a form into which elements from the letter (meeting) are dragged. This is a flexible form – there you can configure any drop-down lists and windows that are useful for creating a task. In the example below, the data from the text is filled in, the sender, date, and time of the meeting. The duration of the meeting is automatically pulled up in the work log field.

Step 4 You can edit some form fields. For example, select the desired task type and performer from the drop-down list. Save.

Step 5 After you click “Save”, the fields are checked for correct input (for example, date and time). If there are errors, the macro will report this and offer to change it. After a successful check, a task is created.

In the current example, a number of changes are made: an executor is assigned, a work log is kept (if it was set), the status of the task is changed, the original letter (meeting) is attached as an attachment, the task is opened in the user’s browser.

Step 6 At this stage, you should begin to experience pleasure.

Development work

Tools for implementation:

  1. The project in Jira in which issues will be maintained.

  2. Outlook if you need to migrate emails and appointments to Jira. If tasks are not bound to Outlook items, you can use any other Microsoft product that supports VBA. But our story is about friendship with Outlook.

  3. The created Jira technical user, under whose name issues will be registered. You can save tasks from the employees themselves.

Roles, rights, accesses

For all employees who will participate in the project, you need to set up certain roles for creating and modifying elements in Jira. Roles can be distributed automatically through an Active Directory (AD) group – a very convenient thing!

If tasks will be created on behalf of a technical user, then you need to give him the appropriate rights to the project. Nuance: if you want to also record the time spent on a task in tempo, you need to create a team in Jira-tempo, assign a leader and participants, and give our technical user a role to manage team plans. So he can keep time for each employee! If the tempo plugin is missing, you can use the standard option through the worklog.

There will be some code next.

Step 1. User authorization. At this stage, you can lay down various procedures for the initial preparation of the project for work. For example, checking for project updates and connecting to network folders that contain data for drop-down lists of forms.

Step 2 Since we have an example based on Outlook with partial filling of data from a letter (meeting), the first step is to connect to an Outlook object:

Dim oInspector As Inspector

Set oInspector = Application.ActiveInspector

If oInspector Is Nothing Then

‘1 option if there are no open active messages, then the macro takes the selected in the list of messages to work

Set MailItem = Application.ActiveExplorer.Selection.Item(1)

Else

‘Option 2 if there are open emails, then it takes the active one or the last one the user worked with,

‘if the code is launched from a letter, it takes the current open

SetMailItem = oInspector.CurrentItem

End If

Step 3 After the Outlook object is selected, the user receives a form to fill out, which has already partially included data from the letter (meeting). At the same time, to read some fields of an Outlook object, you must first check whether it is a letter or an appointment:

If MailItem1.Class = olAppointment Then

‘if this is a meeting

TextBox6 = MailItem.Organizer ‘meeting author author

TextBox7 = Format(MailItem.Start, “dd”)

…..

TextBox12 = DateDiff(“n”, CDate(MailItem1.Start), CDate(MailItem.End))

Else

‘if this letter

TextBox6 = MailItem.SenderName ‘the author of the letter to fill in the author field

TextBox7 = Format(Date, “dd”)

TextBox12 = 0

End if

Step 4 The form fields when you click “Save” must pass a logical check. Let’s say it checks for the correct numeric input, for example, so that the month is no more than 12. The received data is transferred to the dictionary, which was declared earlier as public and received the necessary elements as input:

Public myDict As New Dictionary

‘create elements at start and fill with null values

myDict.Add “theme”, vbNullString

myDict.Add “body”, vbNullString

myDict.Add “aftor”, vbNullString

myDict.Add “isexecutor”, vbNullString

‘passing the data that was selected/written to the form to the dictionary on save

myDictItem(“tema”) = TextBox2

myDict.Item(“body”) = TextBox5

myDict.Item(“aftor”) = TextBox6

myDict.Item(“isexecutor”) = ComboBox2.Value

‘purge on completion

myDict.RemoveAll

Step 5 The form has been completed and verified. We create a Jira element based on the received data. First you need to collect the technical parameters of the project or task for posting by viewing the web page code: you need a project ID, task type IDs. In general, we need a list of all the parameters that we will pass through the post request. Also, field parameters can be found on the official website of api Jira, api tempo.

Step 6 After collecting the field IDs, we create the JSON format in VBA:

issue = “{ “”fields””: { “”assignee””:{“”name””: “”” & j_assignee & “””}, “”summary””: “”” & themeBIC & “”” , “”description””: “”” & descriptionBIC & “””, “”customfield_10808″”: “”” & j_reporter & “””,””project””: { “”id””: “”” & pid & “””, “”name””: “”” & ProgName & “”” }, “”issuetype””: { “”id””: “”” & issuetype & “”” } } } }”

!Important note. Posting the task itself should be done correctly via MSXML2.xmlhttp, but making changes to the task via WinHttp.WinHttpRequest.5.1.

With xmlhttp

.Open “post”, “https://jira.yourdomain.com/rest/api/2/issue“, False

.setRequestHeader “Authorization”, “Basic” & EncodeBase64

.setRequestHeader “Content-type”, “application/json”

.setRequestHeader “X-Atlassian-token”, “no-check”

.Send issue

result = .responseText

statusHml = .status

end with

EncodeBase64 is a function in which we pass the login and password from the Jira user. In order not to store confidential data in the VBA code itself, you can use the environment variable for users of this development and put the password there. This is the most secure password storage option for a VBA project.

Private Function EncodeBase64() As String

Text = “username:” & Environ(“the name of the environment variable you created”)

With CreateObject(“ADODB.Stream”)

.Open: .Type=2: .Charset=”utf-8″

.writetext Text: .Position = 0: .Type = 1: b = .Read

With CreateObject(“Microsoft.XMLDOM”).createElement(“b64”)

.DataType=”bin.base64″: .nodeTypedValue=b

EncodeBase64 = Replace(Mid(.Text, 5), vbLf, “”)

end with

.Close

end with

end function

And here is an example of how, after creating a task, enter the time in the work log and a comment on the work (which is equal to the topic of the task). I draw your attention: to search for parameters, we go to api tempo*:

Split_result = Split(result, “”””) ‘separate the result of work from posting the task, then use it to get the ID of the created task

If masTik.Item(“time”) > vbNullString Then ‘ check that the user entered the time of work in the form

‘!be aware that the name that we used when creating the task as assignee (executor) may differ from worker – to whom we keep a log of work.

issue = “{“”started””: “”” & myDict.Item(“day”) & “””, “”timeSpentSeconds””: “”” & myDict.Item(“time”) * 60 & “” “, “”worker”” : “”” & j_assignee & “””, “”comment”” : “”” & themeBIC & “”” , originTaskId””: “”” & Split_result(7) & “”” }”

With xmlhttp

.Open “post”, ” https://jira.yourdomain.com /rest/tempo-timesheets/4/worklogs/“, False ‘ note that the tempo api is already used here

.setRequestHeader “Authorization”, “Basic” & EncodeBase64

.setRequestHeader “Content-type”, “application/json”

.setRequestHeader “X-Atlassian-token”, “no-check”

.Send issue

result = .responseText

statusHml = .status

end with

end if

*Code without tempo:

Jobs are assigned to the person who creates the task. In this case, the launch will be from an employee with a password request before starting work.

issue = “{“”started””: “”” & DataJira & “””, “”timeSpentSeconds””: “”” & timeSpentSeconds & “””}}”

With xmlhttp1

.Open “post”, “https://jira.yourdomain.com /rest/api/2/issue/” & Split_result(7) & “/worklog”, False

.setRequestHeader “Authorization”, “Basic” & EncodeBase64

.setRequestHeader “Content-type”, “application/json”

.setRequestHeader “X-Atlassian-token”, “no-check”

.Send issue

result = .responseText

statusHml = .status

end with

End if

This project also uses changing the task status and attaching the original email (meeting) to the attachment of the created task.

Macro Effects

As a result, we have a Jira project in which it is convenient to look:

  • workload of employees;

  • skewed distribution of tasks;

  • analysis of redundancy of requests from clients;

  • labor costs by performers, tasks or clients.

All this happens online, while employees spend a minimum of time entering all this information. I really like the solution myself, and the demand for its implementation in the company is quite high.

Thanks for reading! In continuation of this article, I have two more topics. I have not yet decided what to talk about next time. What are you more interested in: how does Jira help in tracking breakdowns of automated processes on servers, or how to automatically (without employees) collect tasks in Jira from the meeting calendar?

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *