Dec 10 2019 09:24 AM
Dec 10 2019 09:24 AM
When exporting a plan to excel, comments for each task are not included. Is there a plan to include comments in export as well ?
It'll be helpful to be able to get each comment as an additional column for every task row to get a full view in excel. This helps in easy backup for the discussions against each task and not just get a plan notes.
Dec 10 2019 11:32 PM
@itsanshulhere you need to use third-party apps to export the complete meta details to Excel and preserve it. Additionally, you can also configure the schedule run to export the data to excel for back up. Let me know if you need to know more about the app.
Jun 12 2020 04:29 AM - edited Jun 12 2020 04:30 AM
I use an alternative way using the fact that I receive an e-mail when a new comment is posted. Although maybe there are better options, this works fine for me.
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. 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.
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String) Dim outlookApp As Outlook.Application Dim objectNS As Outlook.NameSpace Dim inboxItems As Outlook.Items Dim item As Object Dim myTitle As Outlook.MailItem Dim myDate, myBody As String Dim DataArray(3) As Variant Set outlookApp = Outlook.Application Set objectNS = outlookApp.GetNamespace("MAPI") Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Folders("Planner e-mails").Items Set item = objectNS.GetItemFromID(EntryIDCollection) If TypeName(item) = "MailItem" Then If InStr(item.Subject, "RE: Comments on task ") <> 0 Then Set myTitle = item myDate = item.CreationTime myBody = item.Body DataArray(1) = myTitle DataArray(2) = myDate DataArray(3) = myBody OpenExcelFileFromOutlookVBA (DataArray) End If 'Result = MsgBox(MessageInfo, vbOKOnly, "New Message Received") End If End Sub
The macro to open the Excel file is the following. Then in that Excel file there is a macro "Module3.InsertOutlookComments" to paste the data.
Sub OpenExcelFileFromOutlookVBA(DataArray) '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. 'tools>references>'Microsoft Excel 16.0 Obecjt Library' (or similar) ' Excel Application, workbook, and sheet object Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Object ' Filename Dim fileDoesExist As Boolean Dim FileName As String ' Create Excel Application Set xlApp = CreateObject("Excel.Application") xlApp.Visible = False Set xlBook = xlApp.Workbooks.Open("S:\Excel file.xlsm") Set xlSheet = xlBook.Sheets(1) ' Do stuff with Excel workbook With xlApp With xlBook Dim Comment As String Dim CommentDate As Date Dim Project As String CommentDate = DataArray(2) If DateDiff("n", CommentDate, Now()) < 10 Then Comment = Left(DataArray(3), 20) Project = DataArray(1) xlBook.Application.Run "Module3.InsertOutlookComments", DataArray End If End With End With xlBook.Close SaveChanges:=True xlApp.Quit End Sub