2 Easy Methods to Quickly Create an Outlook Task for an Excel Workbook

Some users are eager to quickly create an Outlook task for an Excel workbook. Thus, in this article, we will introduce 2 simple approaches to get it.

At times, you may want to create an Outlook task from an Excel file for follow up or other reasons. Actually, Microsoft Excel comes with a native feature of “Create Microsoft Outlook Task”, like the Method 1 introduced in the followings. But, it’ll only attach the Excel workbook to an Outlook task. If what you want is to quickly copy all the contents of all worksheets to an Outlook task’s body, you can use the Method 2 instead. Now read on to get the 2 means in detail.Quickly Create an Outlook Task for an Excel Workbook

Method 1: Use “Create Microsoft Outlook Task” Command in MS Excel

  1. In the first place, open the specific Excel workbook.
  2. Then, click the down arrow button in the Quick Access Toolbar.
  3. Next, choose “More Commands” from the drop down list.More Commands
  4. Later, in the new “Excel Options” window, on “Quick Access Toolbar” tab, add the “Create Microsoft Outlook Task” command to Quick Access Toolbar.
  • First off, select “Commands Not in the Ribbon” from the list of “Choose commands from”.
  • Then, locate and pitch on “Create Microsoft Outlook Task” command.
  • Afterwards, click “Add >>” button in center.
  • Lastly, hit “OK” button.Add "Create Microsoft Outlook Task" Command to QAT
  1. Eventually, after returning to the Excel workbook, click the new button in Quick Access Toolbar.
  2. At once, a new Outlook task will display, which have been attached with the current Excel workbook, like the following screenshot.New Task Attached with the Excel Workbook

Method 2: Copy Excel Workbook to Body of an Outlook Task via VBA Code

  1. At first, open the source Excel file.
  2. Then, press “Alt + F11” to trigger Excel VBA editor.
  3. Subsequently, enable “Microsoft Outlook Object Library”, about which you can refer to “How to Add an Object Library Reference in VBA“.
  4. After that, put the VBA code below into a module or “ThisWorkbook” project.
Sub CreateOutlookTaskforExcelWorkbook()
    Dim objOutlookApp As Outlook.Application
    Dim objTask As Outlook.TaskItem
    Dim objWorksheet As Excel.Worksheet
    Dim objTempWorkbook As Excel.Workbook
    Dim objTempWorksheet As Excel.Worksheet
    Dim strTempFolder As String
    Dim strHTMLFile As String
    Dim objHTMLFile As Object
    Dim objFileSystem As Object
    Dim objTextStream As Object
    Dim objTempMail As Outlook.MailItem
 
    Set objOutlookApp = CreateObject("Outlook.Application")
    Set objTask = objOutlookApp.CreateItem(oltaskitem)
    objTask.Subject = ActiveWorkbook.Name
    objTask.Display
 
    For Each objWorksheet In ActiveWorkbook.Sheets
        objWorksheet.UsedRange.Copy
 
        Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
        Set objTempWorksheet = objTempWorkbook.Sheets(1)
        With objTempWorksheet.Cells(1)
             .PasteSpecial xlPasteValues
             .PasteSpecial xlPasteColumnWidths
             .PasteSpecial xlPasteFormats
        End With
 
        strTempFolder = CStr(Environ("USERPROFILE")) & "\AppData\Local\Temp"
        strHTMLFile = strTempFolder & "\Temp" & Format(Now, "yyyymmddhhmmss") & ".htm"
        Set objHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address)
        objHTMLFile.Publish (True)

        Set objTempMail = objOutlookApp.CreateItem(olMailItem)
 
        Set objFileSystem = CreateObject("Scripting.FileSystemObject")
        Set objTextStream = objFileSystem.OpenTextFile(strHTMLFile)
        objTempMail.HTMLBody = objTextStream.ReadAll
        objTempMail.Display
 
        objTask.Body = objTask.Body & vbCr & "-----------------------" & vbCr & objTempMail.Body

        objTextStream.Close
        objTempWorkbook.Close False
        Kill strHTMLFile
        objTempMail.Close olDiscard
    Next
End Sub

VBA Code - Copy Excel Workbook to Body of an Outlook Task

  1. Eventually, press “F5” key buttons.
  2. When macro finishes, you will get an Outlook task, which contains the Excel workbook contents in the body, shown as the following screenshot.New Task with Excel Workbook Contents in Body

Restore Outlook Data from Backups

It is highly recommended to back up your computer data at regular intervals, of course including your Outlook data files. In this way, you needn’t keep concerned about Outlook data loss. Even if your Outlook file gets damaged, you can retrieve data from backups with effortless ease. But, assuming that you haven’t made data backup, in such cases, you have to attempt PST recovery via a proficient fix utility, such as DataNumen Outlook Repair. It can recover PST without breaking a sweat.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair mdf and outlook repair software products. For more information visit www.datanumen.com

3 responses to “2 Easy Methods to Quickly Create an Outlook Task for an Excel Workbook”

  1. This is really interesting, You’re a very skilled blogger. I have joined your feed and look forward to seeking more of your excellent post. Also, I’ve shared your website in my social networks!

  2. I want to put date, time, name and surname of worker who need to go to medical examination, and then create for each row separate task and assign that task to this worker. So I want to define how task should be created, and then give a command to excel to create all needed tasks, and to send assignments to all workers.

Leave a Reply

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