Trouble combining my For/Next loops? “Invalid Next Control Variable Reference’

[John.McLaughlin]

I have a basic WS I want to paste to Outlook Tasks using Ron de Bruin’s RangeToHTML (that part works!).

The column heading is the Task name, the rows below is the .Subject body.

The macro creates a new Task name for each of the Column headings, but copies the same rows from Column A to each new Task, and not the rows under the next column.

If I move the code that reads the rows, into the For/Next that creates the Task Name, I get “Invalid Next Control Variable Reference’

Where am I going wrong?

Admittedly, I only have a basic VBA understanding, and can usually modify snippets /cobble code together to work, but I have been stuck on this for over a week now. Thanks in advance for any help you can provide.

The below code copies the same rows from Col A to every new tasks

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

 

[Fluff]

Ok, how about

    ' Get Column heading data as .Subject title of Tasks
   With Sheets("Tasks")
      For Each cc In .Range("A1", .Cells(1, Columns.Count).End(xlToLeft))
         Set rng = .Range(cc.Offset(1), .Cells(Rows.Count, cc.Column).End(xlUp))
         If rng.Count = 1 Then
            strTable = rng.Value
         Else
            strTable = Join(Application.Transpose(rng), vbLf)
         End If
         
         Set olTask = ol.CreateItem(3)
         With olTask
            .Subject = cc.Value
            .Body = strTable
            .Save
         End With

'     Goto next Column heading
      Next cc
   End With

 

[John.McLaughlin]

I think that fixed it! I even added back a Column with no data in the rows, and it still created the Task.

Again, many thanks, and after reading what you have done, I don't think I would of figured it out. But, I will study the working code, and will be able to modify and use it in other automation chores.

All my best to you for sharing your knowledge and time, I wish you a great summer!

 

[Fluff]

You're welcome & thanks for the feedback.