VBA - DueDate and Next Without For errors

Kirexin

New Member
Joined
Apr 27, 2015
Messages
6
Hey guys, great forum, I've been using it a lot while doing this work in Excel.

I am writing code to have Tasks in Outlook created by a huge list of tasks in an Excel document. I want to skip all the blank rows and rows of assignments that are already completed. So I am trying to make it so if the Due Date column (Column E) is NOT blank and the Actual Completion Date column (Column E) IS blank then it should create an outlook task. I have it so it is able to create outlook tasks but I am running into two problems.

1) In the section right under "For i = 9 to 25000" where it is supposed to ignore the row based on the above mentioned criteria it gives me the error "Compile error: Next without For" on the line that says Next i

2) In the section where I am trying to assign a due date near the bottom it gives me an Error 13 for the line .DueDate = DateValue(strDate)

Here is the code
Code:
Sub CheckBinding()
Dim olApp As Outlook.Application
Set olApp = New Outlook.Application
MsgBox olApp.Name
End Sub

Sub CreateTask()
Dim olApp As New Outlook.Application
Dim olName As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olTasks As Outlook.Items
Dim olNewTask As Outlook.TaskItem
Dim strSubject As String
Dim strDate As String
Dim strBody As String
Dim reminderdate As String
Dim ws As Worksheet
Dim i As Long
Dim DueCheck As Range


Set ws = Worksheets("KPI") 'sheet where dates are
Set olName = olApp.GetNamespace("MAPI")
Set olFolder = olName.GetDefaultFolder(olFolderTasks)
Set olTasks = olFolder.Items


LR = ws.Range("C9").End(xlDown).Row 'get row for last cell in column D with value


For i = 9 To 25000 'assuming the rows have headers, so loop starts on row 2
   If Not IsEmpty(ws.Range("E", i)) And ws.Range("J", i) = "" Then
        Next i
    Else
strSubject = ws.Range("C" & i)  'takes subject from column c
strDate = ws.Range("E" & i) 'takes date from column e
strBody = ws.Range("C8") & Chr(10) & ws.Range("C" & i) & Chr(10) & Chr(10) & ws.Range("D8") & Chr(10) & ws.Range("D" & i) & Chr(10) & Chr(10) & ws.Range("F8") & Chr(10) & ws.Range("F" & i) & Chr(10) & Chr(10) & ws.Range("G8") & Chr(10) & ws.Range("G" & i) & Chr(10) & Chr(10) & ws.Range("K7") & Chr(10) & ws.Range("K" & i)
Set olNewTask = olTasks.Add(olTaskItem) 'delete task if it exists 'an error is generated if task doesn't exist

On Error Resume Next
olTasks.Item (strSubject)
If Err.Number = 0 Then
olTasks.Item(strSubject).Delete
End If
On Error GoTo 0
'create new task

With olNewTaskthat
Subject = strSubject
.Importance = olImportanceNormal
.DueDate = DateValue(strDate)
.Body = strBody
.ReminderSet = True
.Save
End With
    End If
Next i
End Sub

Sub CreateOutlookTask()
End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Your description was a bit vague (and contradictory), but I believe this may be what you are looking for.

Code:
Sub CreateTask()
Dim olApp As New Outlook.Application
Dim olName As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olTasks As Outlook.Items
Dim olNewTask As Outlook.TaskItem
Dim strSubject As String
Dim strDate As String
Dim strBody As String
Dim reminderdate As String
Dim ws As Worksheet
Dim i As Long
Dim DueCheck As Range

Set ws = Worksheets("KPI") 'sheet where dates are
Set olName = olApp.GetNamespace("MAPI")
Set olFolder = olName.GetDefaultFolder(olFolderTasks)
Set olTasks = olFolder.Items

LR = ws.Range("C9").End(xlDown).Row 'get row for last cell in column D with value

For i = 9 To 25000 'assuming the rows have headers, so loop starts on row 2
   If Not IsEmpty(ws.Range("E" & i)) And ws.Range("J" & i) = "" Then
        
        strSubject = ws.Range("C" & i)  'takes subject from column c
        strDate = ws.Range("E" & i) 'takes date from column e
        strBody = ws.Range("C8") & Chr(10) & ws.Range("C" & i) & Chr(10) & Chr(10) & ws.Range("D8") & Chr(10) & ws.Range("D" & i) & Chr(10) & Chr(10) & ws.Range("F8") & Chr(10) & ws.Range("F" & i) & Chr(10) & Chr(10) & ws.Range("G8") & Chr(10) & ws.Range("G" & i) & Chr(10) & Chr(10) & ws.Range("K7") & Chr(10) & ws.Range("K" & i)
        Set olNewTask = olTasks.Add(olTaskItem) 'delete task if it exists 'an error is generated if task doesn't exist
        
        On Error Resume Next
        olTasks.Item (strSubject)
        If Err.Number = 0 Then
        olTasks.Item(strSubject).Delete
        End If
        On Error GoTo 0
        'create new task
        
        With olNewTask
            Subject = strSubject
            .Importance = olImportanceNormal
            .DueDate = DateValue(strDate)
            .Body = strBody
            .ReminderSet = True
            .Save
        End With
    End If
Next i
End Sub
 

Kirexin

New Member
Joined
Apr 27, 2015
Messages
6
Thanks BiocideJ. It is picking up the correct days for the due date but it is still saying error 13. I think that when the loop gets to a blank cell it is trying to read it as a date and it isn't work. What do you think?
 

Kirexin

New Member
Joined
Apr 27, 2015
Messages
6
I've tried a number of ways for it to skip the ranges where the Due Date (column E) are blank but I can't get it to work. I can remove the duedate part and it runs fine except it obviously doesn't have a duedate in outlook or I can leave it in and it works fine until it encounters the first blank cell in the due date column.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
You really don't say what you expect to happen with a blank due date, so I am going to assume you will add the task without a Due Date.

In that case change this:
.DueDate = DateValue(strDate)

to this:
If strDate <> "" Then .DueDate = DateValue(strDate)
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
OK, perhaps this amendment to the IF statement will cause the test to be more reliable.
If this still doesn't work, perhaps you can better define what is in column E. (i.e. typed values, formulas, etc.)

NOTE: This code will still error out if there is a non-blank, non-date value in column E. If you expect that that will happen, let me know and I will try to make a work-around.

Code:
Sub CreateTask()
Dim olApp As New Outlook.Application
Dim olName As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olTasks As Outlook.Items
Dim olNewTask As Outlook.TaskItem
Dim strSubject As String
Dim strDate As String
Dim strBody As String
Dim reminderdate As String
Dim ws As Worksheet
Dim i As Long
Dim DueCheck As Range
Set ws = Worksheets("KPI") 'sheet where dates are
Set olName = olApp.GetNamespace("MAPI")
Set olFolder = olName.GetDefaultFolder(olFolderTasks)
Set olTasks = olFolder.Items
LR = ws.Range("C9").End(xlDown).Row 'get row for last cell in column D with value
For i = 9 To 25000 'assuming the rows have headers, so loop starts on row 2
    strDate = ws.Range("E" & i) 'takes date from column e
    If strDate <> "" And ws.Range("J" & i) = "" Then
        
        strSubject = ws.Range("C" & i)  'takes subject from column c
        strBody = ws.Range("C8") & Chr(10) & ws.Range("C" & i) & Chr(10) & Chr(10) & ws.Range("D8") & Chr(10) & ws.Range("D" & i) & Chr(10) & Chr(10) & ws.Range("F8") & Chr(10) & ws.Range("F" & i) & Chr(10) & Chr(10) & ws.Range("G8") & Chr(10) & ws.Range("G" & i) & Chr(10) & Chr(10) & ws.Range("K7") & Chr(10) & ws.Range("K" & i)
        Set olNewTask = olTasks.Add(olTaskItem) 'delete task if it exists 'an error is generated if task doesn't exist
        
        On Error Resume Next
        olTasks.Item (strSubject)
        If Err.Number = 0 Then
        olTasks.Item(strSubject).Delete
        End If
        On Error GoTo 0
        'create new task
        
        With olNewTask
            Subject = strSubject
            .Importance = olImportanceNormal
            .DueDate = DateValue(strDate)
            .Body = strBody
            .ReminderSet = True
            .Save
        End With
    End If
Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,657
Messages
5,488,123
Members
407,625
Latest member
Alanacran

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top