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

 

[GWteB]

Is it possible for you to post an extract of your worksheet using XL2BB?

 

[John.McLaughlin]

Neat tool, thanks for the tip and reply!

I keep getting the same Home Depot list pasted into each new task.

SCHEDULED.xlsm
	A	B	C
1	HOME DEPOT	HY-VEE	NOTHERN TOOL
2	12 x 1 WOOD SCREWS	GRAPEFRUIT JUICE	DIESEL CANS
3	1/2" STEEL BIT COBALT Aisle 15, Bay 005 (MILWA)	DALES STEAK SEASONINGS	12V PIG TAILS
4	1/2" DRILL FLOOR ANCHORS	DINNER
5	1/4 SHUT OFF VALVES FROM MENARDS
6	3/4" COPPER FITTINGS
7
TASKS

 

[GWteB]

I think I see what's going on. The Sendrng variable only consists of one column, so the LastCol variable gets the value 1, from which it follows that the c variable also gets the value 1. So your code examines just one column. Replace this
LastCol = Sendrng.Columns.Count

by this and give it a try

LastCol = SendRng.Parent.Cells(1, SendRng.Parent.Columns.Count).End(xlToLeft).Column

 

[John.McLaughlin]

It merges all the rows together into one body message, and posts that into each new task

HOME DEPOT HY-VEE NOTHERN TOOL

12 x 1 WOOD SCREWS GRAPEFRUIT JUICE DIESEL CANS

1/2" STEEL BIT COBALT Aisle 15, Bay 005 (MILWA) DALES STEAK SEASONINGS 12V PIG TAILS

1/2" DRILL FLOOR ANCHORS DINNER

1/4 SHUT OFF VALVES FROM MENARDS

3/4" COPPER FITTINGS

 

[Fluff]

Maybe

Sub OutlookTasks()

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

    Set ol = CreateObject("Outlook.Application")
    
    ' Get Column heading data as .Subject title of Tasks
    For Each cc In Sheets("Data").Range("A1", Sheets("Data").Range("A1").End(xlToRight))
        strTable = Join(Application.Transpose(Range(cc.Offset(1), cc.End(xlDown))), vbLf)
        Set olTask = ol.CreateItem(3)

        With olTask
            .Subject = cc.Value
            .Body = strTable
            .Save
        End With

    ' Goto next Column heading
    Next cc


End Sub

 

[John.McLaughlin]

Maybe

Sub OutlookTasks()

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

    Set ol = CreateObject("Outlook.Application")
   
    ' Get Column heading data as .Subject title of Tasks
    For Each cc In Sheets("Data").Range("A1", Sheets("Data").Range("A1").End(xlToRight))
        strTable = Join(Application.Transpose(Range(cc.Offset(1), cc.End(xlDown))), vbLf)
        Set olTask = ol.CreateItem(3)

        With olTask
            .Subject = cc.Value
            .Body = strTable
            .Save
        End With

    ' Goto next Column heading
    Next cc


End Sub

WOW THANK YOU! AND it runs much faster!!

Now I will study this, it is important I learn and not just ask for answers (hence my lost week)

Thank you again to both of you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[John.McLaughlin]

Hmm, I am encountering a new Error;

RUN TIME ERROR TYPE '13': TYPE MISMATCH

I deleted a few columns that did not have any data, but it stops a the 14" column now, which is simply

SCHEDULED.xlsm
	N
1	SAFETY SPEED CUT
2	PANEL SAW WHEELS
TASKS

 

[John.McLaughlin]

This is the line highlighted in Debug?

        strTable = Join(Application.Transpose(Range(cc.Offset(1), cc.End(xlDown))), vbLf)