![]() ' Excel Application, workbook, and sheet object 'tools>references>'Microsoft Excel 16.0 Obecjt Library' (or similar) 'To make sure the excel application was available from within outlook I had to go into the menus and make sure the reference was available. ![]() Sub OpenExcelFileFromOutlookVBA(DataArray) Then in that Excel file there is a macro "Module3.InsertOutlookComments" to paste the data. The macro to open the Excel file is the following. 'Result = MsgBox(MessageInfo, vbOKOnly, "New Message Received") If InStr(item.Subject, "RE: Comments on task ") 0 Then Set item = objectNS.GetItemFromID(EntryIDCollection) Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Folders("Planner e-mails").Items Set objectNS = outlookApp.GetNamespace("MAPI") Private Sub Application_NewMailEx(ByVal EntryIDCollection As String) When an e-mail is added to this folder the following code is executed, where the Title, Date and Body text is passed to another macro which open an Excel file and to paste the data. I have a Folder in outlook where the Planner e-mails are moved automatically that is called "Planner e-mails", but it can work with the main folder if needed. Although maybe there are better options, this works fine for me. ![]() ![]() Let me know what you think.Use an alternative way using the fact that I receive an e-mail when a new comment is posted. 'Hope this is useful or inspirational for others. Maybe I'll skip the moving and work on the planner dashboard instead? With this data in PowerBI a lot more can be achieved, like 'list of unplanned tasks', 'unfinished tasks with due date in the past', 'days with the highest number of tasks planned', etc. That's how far I have created it for now. Using conditional formatting, a blue box is presented instead of the figure '1'. In the value cell, the number of task lines is presented, which is empty or '1' since the table displays a task line per row per day. As rows a drill down is applied from bucket to task name, the days will be the columns. I'm sure there are nice custom visuals around, but for now I'm keeping it lean and standard. The result is a tabular model with tasks, date lines and I have also added a Calendar.įor the gantt chart I'm using the standard cross table. Creating such a date range can be achieved with the 'M' function in query editor: list.dates. So each task will need to have a fact table with all dates from start to due. Tasks have a start date and due date, but we need to show all planning days in that start-due period. In the query editor the excel file is uploaded. out and have it stored in Excel, Sharepoint list, a database or other storage location. Get the planner data: with Power Automate you can call various api's to get plans, tasks, buckets, etc. Here's what I did (I don't want to make this blog to technical, if you want more details send me a pm): Since it's important to have the moving succeeded in time, I've been working on creating a simple gantt chart in PowerBI, using Power Automate for data extraction. Luckily there are more tools in the O365 environment. In a project you want a gantt chart! As far as I know it's not on the road-map, maybe because it's a feature in the paid MS Projects. MS Planner is part of the O365 suite and combines perfectly with Teams, Mail, Calendar, etc. So what's the first thing that comes to mind when preparing this project? Indeed, setup a MS Planner with buckets, due dates and dashboards. In a few weeks from now, I'll be moving to another house.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |