Create Outlook Task using Column data. Error reading next column :(

John.McLaughlin

Board Regular
Joined
Jul 19, 2011
Messages
162
I have a simple WS to list Items needed, errands to run, etc.

Row 1 is the Task’s .Subject (Title) cell, Rows 2 to X is the running list of info.

I can successfully create all tasks .Subject by reading across Row 1. However, the .Body of each task contains the same data from Column 1, and does not read the new data found under each column heading.

How do I nest(?) my Column For/Next statement to copy the data in the new column?

Thanks in advance!


Code:
Sub OutlookTasks()

        Dim ol As Object, olTask As Object
    Dim cc As Range

    Dim LastRow As Long 
    Dim N As Long, r As Long
    Dim Sendrng As Range
    Set Sendrng = Worksheets("TASKS").Range("A1:A100")

    ' Create .Body of Task by finding/copy all cells in column with data
    ' Store data found in strtable to paste in .Body of Task
    FirstRow = 1
    LastRow = Sendrng.Rows.Count '100
    FirstCol = 1
    LastCol = Sendrng.Columns.Count

    For r = FirstRow To LastRow
    For c = FirstCol To LastCol
    For Each CELL In Cells(r, c)
    strtable = strtable & "  " & CELL.Value
        Next
        Next
    strtable = strtable & vbNewLine
        Next

   
    Set ol = CreateObject("Outlook.Application")
    
    ' Get Column heading data as .Subject title of Tasks
    For Each cc In Range("A1", Range("A1").End(xlToRight))
        Set olTask = ol.CreateItem(3)
        
        With olTask
            .Subject = cc.Value
            .Body = strtable
            .Save
        End With

    ' Goto next Column heading
    Next cc


End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,137,113
Messages
5,679,701
Members
419,850
Latest member
Cbell07958

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top