home | get Sue’s code | forums | share code | registration

Outlook Technologies

Outlook Forms

Languages

Samples

More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 09-Feb-2010 02:48

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

Code level: intermediate Code area: Outlook Expert Techniques Printer Friendly Version
Title: Task to Excel
Description: The code below works to move standard task fields from the task folder to an excel spread sheet. I need to also export many custom fields within this folder. Can anyone tell me how to alter (starting in column D see body of code) the code below to add a custom field called "register" ?
Date: 26-Jan-2003 11:43
Code level: intermediate
Code area: Outlook Expert Techniques
Posted by: Wayne Paschal
Body: Option Explicit Public Sub ContactsToExcel() On Error GoTo ContactsToExcel_Error 'Excel definitions Dim rngRange As Excel.Range Dim wrkSheet As Excel.Worksheet 'Outlook definitions Dim appOL As Outlook.Application Dim nmsNS As Outlook.NameSpace Dim fldTask As Outlook.MAPIFolder Dim itmItems As Outlook.Items Dim itmTask As Outlook.TaskItem 'Must declare as Object because folders may contain different 'types of items 'for testing for task items with the 'standard task form Dim objTmp As Object Dim strRange As String Dim strUNI As String 'Unicode characters Dim intRow As Integer 'Initialize Outlook items Set appOL = CreateObject("Outlook.Application") Set nmsNS = appOL.GetNamespace("MAPI") Set fldTask = nmsNS.GetDefaultFolder(olFolderTasks) Set itmItems = fldTask.Items 'Make sure Sheet 1 is active Set wrkSheet = ActiveWorkbook.Sheets(1) wrkSheet.Activate 'Set Sheet column widths wrkSheet.Columns("A").ColumnWidth = 16 wrkSheet.Columns("B").ColumnWidth = 16 wrkSheet.Columns("C").ColumnWidth = 16 wrkSheet.Columns("D").ColumnWidth = 34 wrkSheet.Columns("E").ColumnWidth = 16 wrkSheet.Columns("F").ColumnWidth = 16 'Column A, Row 1 Sheet Title - Bold, 14 pt, underlined strUNI = "A" Set rngRange = wrkSheet.Range(strUNI & "1") With rngRange .Value = "Tasks from Outlook" .Font.Bold = True .Font.Size = 14 .Font.Underline = xlUnderlineStyleSingle End With 'Column headings - Bold, 12 pt, thick bottom border 'Column A Set rngRange = wrkSheet.Range(strUNI & "2") With rngRange .Value = "Subject" .Font.Bold = True .Font.Size = 12 .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThick End With 'Column B strUNI = Chr(Asc(strUNI) + 1) Set rngRange = wrkSheet.Range(strUNI & "2") With rngRange .Value = "Start Date\" .Font.Bold = True .Font.Size = 12 .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThick End With 'Column C strUNI = Chr(Asc(strUNI) + 1) Set rngRange = wrkSheet.Range(strUNI & "2") With rngRange .Value = "Mileage" .Font.Bold = True .Font.Size = 12 .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlThick End With '*** Enter custom field here as column D "..Register Number.." How? 'Start adding data at Column A, Row 3 intRow = 3 strUNI = "A" For Each objTmp In itmItems 'Make sure that the item is a Task item 'and that it uses the IPM.Task.tasktest form If objTmp.Class = olTask And _ objTmp.MessageClass = "IPM.Task.tasktest" Then Set itmTask = objTmp With itmTask strRange = strUNI & CStr(intRow) Set rngRange = wrkSheet.Range(strRange) rngRange.Value = Nz(.Subject) strUNI = Chr(Asc(strUNI) + 1) strRange = strUNI & CStr(intRow) Set rngRange = wrkSheet.Range(strRange) rngRange.Value = Nz(.StartDate) strUNI = Chr(Asc(strUNI) + 1) strRange = strUNI & CStr(intRow) Set rngRange = wrkSheet.Range(strRange) rngRange.Value = Nz(.Mileage) strUNI = Chr(Asc(strUNI) + 1) strRange = strUNI & CStr(intRow) Set rngRange = wrkSheet.Range(strRange) rngRange.Value = Nz(.Register) 'Back to Column A, next row strUNI = "A" intRow = intRow + 1 End With End If Next objTmp appOL.Quit Exit Sub ContactsToExcel_Error: MsgBox "Error #" & Err.Number & Err.Description appOL.Quit End Sub
All 10comments

	Page [ 1 ]
	Sue Mosher 27-Jan-2003 14:09 Instead of .Register, use .UserProperties("Register"). See http://www.slipstick.com/dev/propsyntax.htm for more information on Outlook property syntax. FWIW, you can also simplify the code greatly by using the Cells method and column and row numbers instead of building a strRange variable: With itmTask wrkSheet.Cells(1,intRow).Value = Nz(.Subject) wrkSheet.Cells(2,intRow).Value = Nz(.StartDate) wrkSheet.Cells(3,intRow).Value = Nz(.Mileage) wrkSheet.Cells(4,intRow).Value = Nz(.UserProperties("Register")) intRow = intRow + 1 End With
	Wayne Paschal 11-Jun-2003 10:32 Is there a way that you can do the same but move all contents of the folder into excel at one time versus row by row? I got this to work but I have anywhere from 160 to 300 entries in the folder on a given day. It can take 15 minutes or more for this statement to run.
	Sue Mosher 20-Jun-2003 17:05 CDO would be an order of magnitude faster, but I'm not sure all the task property tags are documented. See http://www.cdolive.com/cdo10.htm
	Amy Bickel 02-May-2005 15:50 Can you help me with the reverse? Import a list of "tasks" from Excel into my Outlook task list - with multiple columns of information that align with the task information.
	Sue Mosher 03-May-2005 07:44 Amy, see http://www.outlookcode.com/d/customimport.htm#samples
	Dan W 25-Jul-2006 03:22 How can i set a row of information from excel cells as a task in outlook, it needs to happen automatically when a certain date in a cell goes over a certain amount of time.
	Sue Mosher 29-Jul-2006 08:24 Dan, create the task with the Application.CreateItem method, then simply reverse the logic in the current code sample to read data with the Cells() method and set Outlook properties. Follow the link I suggested to Amy if you need more samples.
	Brian 06-Dec-2008 08:57 Sue, This is an old string but it is the most relevant that I can find to my question. Going back to the posting from Amy Bickel 02-May-2005 15:50....the link is no longer available but I'm pretty sure I'm headed in the right direction. In an excel file I wnt to create a button/macro that will generate a Outlook Task and send it to a Usergroup. A Salesman will be complting information in the EXCEL file and when complete assigning the completion to a Sales Support Group. All attempts to write this code generate an email but I want to generate a task with the EXCEL file embedded/attached. I can't seem to get to teh first part which is generating the task: Sub GeneratingTaskInOutlook() Dim objApp As Object Dim OutTask As Object Set objApp = CreateObject("Outlook.Application") Set OutTask = objApp.CreateItem(olTaskItem) With OutTask .Subject = "Test" .Display End With End Sub How do I create a new Task and not a new Email?
	Brian 06-Dec-2008 09:35 Sue, This is an old string but it is the most relevant that I can find to my question. Going back to the posting from Amy Bickel 02-May-2005 15:50....the link is no longer available but I'm pretty sure I'm headed in the right direction. In an excel file I wnt to create a button/macro that will generate a Outlook Task and send it to a Usergroup. A Salesman will be complting information in the EXCEL file and when complete assigning the completion to a Sales Support Group. All attempts to write this code generate an email but I want to generate a task with the EXCEL file embedded/attached. I can't seem to get to teh first part which is generating the task: Sub GeneratingTaskInOutlook() Dim objApp As Object Dim OutTask As Object Set objApp = CreateObject("Outlook.Application") Set OutTask = objApp.CreateItem(olTaskItem) With OutTask .Subject = "Test" .Display End With End Sub How do I create a new Task and not a new Email?
	Brian 06-Dec-2008 11:51 Sue, This is an old string but it is the most relevant that I can find to my question. Going back to the posting from Amy Bickel 02-May-2005 15:50....the link is no longer available but I'm pretty sure I'm headed in the right direction. In an excel file I wnt to create a button/macro that will generate a Outlook Task and send it to a Usergroup. A Salesman will be complting information in the EXCEL file and when complete assigning the completion to a Sales Support Group. All attempts to write this code generate an email but I want to generate a task with the EXCEL file embedded/attached. I can't seem to get to teh first part which is generating the task: Sub GeneratingTaskInOutlook() Dim objApp As Object Dim OutTask As Object Set objApp = CreateObject("Outlook.Application") Set OutTask = objApp.CreateItem(olTaskItem) With OutTask .Subject = "Test" .Display End With End Sub How do I create a new Task and not a new Email?
	Page [ 1 ]

name email

Search Outlook Code