Do Until "" Loop

R

ryguy7272

The loop structure has always been difficult for me to grasp. Sigh...

I'm trying to figure out a way to loop through all used cells in Column C,
and run until a blank cell (probably "") is found, then stop. I would
probably start on C2, so I am thinking the looping portion of the code may be
something like this:
Dim i As Integer
i = 2
Do Until Cells(i, 3).Value = ""
....other code in here???
i = i + 1
Loop


The code that I have so far is below (and this works fine without the loop):
Sub GetOutlookReference()
'Outlook objects
Dim olApp As Outlook.Application
'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
'*********************************************
Dim objApp As Object
Dim OutTask As Object
Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask

.StartDate = Range("C1")
.DueDate = Range("C2")
.Subject = Range("C3")

.Importance = olImportanceHigh
.Display
.ReminderSet = True
End With
'*********************************************
'If Outlook isn't running, start it and remember

If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If
End Sub

Finally, how can I get the values, which will be dates, to update as a Task
item in Outlook, and then get this to save automatically without the user
choosing to save the change (because all of these extra clicks would kind of
defeat the purpose of the loop, right)...


TIA,
Ryan---
 
S

smw226 via OfficeKB.com

Hi ryguy,

There are a few ways you can tacle this:

=======================================================================
Range("D2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
=======================================================================

as for Outlook I;m a Notes man I'm afraid.

HTH

Simon

====================================
 
R

ryguy7272

This is where I am right now:
Sub GetOutlookReference()

'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
i = 2
j = 2
Do Until Cells(i, 5).Value = ""
Do Until Cells(j, 3).Value = ""
'*********************************************

Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask


.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value

.Importance = olImportanceHigh
.Display
.ReminderSet = True
.ReminderPlaySound = True
.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
End With
'*********************************************
j = j + 1
i = i + 1
Loop
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If

End Sub


The problem is that it gives me the same values for each i and each j...I
guess it is not incrementing. Any suggestions? BTW, thanks Simon, but I
think I'm going to try to get my initial concept to work; it seems to make a
little more sense to me.
 
P

Peter T

I don't know which cells you want your loops to look at but at a glance it
doesn't look right at all. Make a simple routine that loops cells and does
nothing else, except either to select cells(i,5) and (j,3) or print their
address's to the Immediate window. Step through the code with F8. You might
want to ,move the line that increments i to the outer loop, don't know.

Different issue -

Dim olApp As Outlook.Application
implies you have a reference set to outlook in Tools Ref's. It also requires
users have Outlook installed and a version same or newer than the reference
that was saved with your project.

On error resume next
' attempt to attach to an existing instance
Set olApp = GetObject(, "Outlook.Application")
On error Goto 0 ' don't want to paper over errors

If olApp is nothing then
Set olApp = New Outlook.Application
End if

OTH, if you are not sure it's safe to distribute with Early Binding, after
testing and taking advantage of the intellisense, remove the reference in
Tools/Ref's

Dim olApp as Object
On error resume next
' attempt to attach to an existing instance
Set olApp = GetObject(, "Outlook.Application")

If olApp is nothing then
CreateObject("Outlook.Application")
End if
On error Goto 0

If olApp is Nothing then
Msgbox "Outlook does not appear to be installed"
Exit Sub
End if


With either approach remove
Dim objApp As Object and
Set objApp = CreateObject("Outlook.Application")
and replace objApp with olApp

Also move 'Dim OutTask As Object' out of the loops and place at the top of
your routine.

Regards,
Peter T
 
R

ryguy7272

Thanks to everyone who looked at my post. The code for the j loop seems to
be working alright (the j loop pulls information from the worksheet and
populates it in the 'Subject' area of the task). The code for the i loop
returns the current date in the 'Reminder' area of the task. All of the
dates that I want to send to the reminder areas of the task should come from
column E, but this part of the loop doesn't seem to be working. Specific
dates are calculated in column E; I want these specific dates to be sent to
each task...not the current date sent to each task.

I'm going to keep trying a few more things. If I resolve the issue, I'll
post back with the solution. Until then, if anyone has any additional
thoughts please let me know...


Thanks,
Ryan--
 
R

ryguy7272

Finally got it working:
Sub GetOutlookReference()
'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = 2
j = 2
k = 2
l = 2

Do Until Cells(i, 5).Value = ""
'*********************************************
Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask
.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value
.Body = Cells(k, 1).Value & " - " & Cells(l, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
'.ReminderTime = [NextPM]
'.DueDate = [NextPM]
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
.Close
End With
'*********************************************
l = l + 1
k = k + 1
j = j + 1
i = i + 1
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If
End Sub

People's names are in Column A, which is Dimmed as k
Names of Tasks are in Column D, which is Dimmed as j
Descriptions of Tasks are in Column E, which is Dimmed as l
Dates are in Column F, which is Dimmed as i

The only think I can't figure out now is how to close each Task item after
it is opened and populated with the appropriate information. I think I'll
check out the Outlook DG to see if I can find a solution over there. Thanks
for the help!!

Regards,
Ryan—
 
D

Dana DeLouis

Hi. Here are some thoughts.
It appears to me that i,j,k,l all point to the same Row. Perhaps only use 1
variable.
It seems you could Set OutTask only once. I threw in a named variable to
see if that might be an option you would like.
Again, this is not tested, but my give some ideas to try.


Dim R As Long ' (R)ow
Dim objApp As Object
Dim OutTask As Object

'Set them once here
Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)

Const Dte As Long = 5
Const Subj As Long = 3

R = 2 'Start in Row 2

Do Until Cells(R, Dte).Value = vbNullString
With OutTask
.StartDate = Cells(R, Dte).Value
.Subject = Cells(R, Subj).Value
.Body = Cells(R, 1).Value & " - " & Cells(R, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
.Save
.Close
End With

R = R + 1
Loop

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007


ryguy7272 said:
Finally got it working:
Sub GetOutlookReference()
'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = 2
j = 2
k = 2
l = 2

Do Until Cells(i, 5).Value = ""
'*********************************************
Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask
.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value
.Body = Cells(k, 1).Value & " - " & Cells(l, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
'.ReminderTime = [NextPM]
'.DueDate = [NextPM]
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
.Close
End With
'*********************************************
l = l + 1
k = k + 1
j = j + 1
i = i + 1
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If
End Sub

People's names are in Column A, which is Dimmed as k
Names of Tasks are in Column D, which is Dimmed as j
Descriptions of Tasks are in Column E, which is Dimmed as l
Dates are in Column F, which is Dimmed as i

The only think I can't figure out now is how to close each Task item after
it is opened and populated with the appropriate information. I think I'll
check out the Outlook DG to see if I can find a solution over there.
Thanks
for the help!!

Regards,
Ryan-


--
RyGuy


ryguy7272 said:
Thanks to everyone who looked at my post. The code for the j loop seems
to
be working alright (the j loop pulls information from the worksheet and
populates it in the 'Subject' area of the task). The code for the i loop
returns the current date in the 'Reminder' area of the task. All of the
dates that I want to send to the reminder areas of the task should come
from
column E, but this part of the loop doesn't seem to be working. Specific
dates are calculated in column E; I want these specific dates to be sent
to
each task...not the current date sent to each task.

I'm going to keep trying a few more things. If I resolve the issue, I'll
post back with the solution. Until then, if anyone has any additional
thoughts please let me know...


Thanks,
Ryan--
Click to expand...
Click to expand...
 
R

ryguy7272

Thanks, I actually went with this:
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = 2
j = 2
k = 2
l = 2

Do Until Cells(i, 5).Value = ""
'*********************************************
Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
'Set objOutlookApp = Application
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask
.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value
.Body = Cells(k, 1).Value & " - " & Cells(l, 4).Value
.Importance = olImportanceHigh
'.Display
.ReminderSet = True
'.ReminderTime = [NextPM]
'.DueDate = [NextPM]
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
'.Close
End With
'*********************************************
l = l + 1
k = k + 1
j = j + 1
i = i + 1
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
'Set olApp = CreateObject("Outlook.Application")
Set objOutlook = Application
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If

Not sure if there is a better way of doing it, but it seems to work pretty
well.

Regards,
Ryan--
--
RyGuy


Dana DeLouis said:
Hi. Here are some thoughts.
It appears to me that i,j,k,l all point to the same Row. Perhaps only use 1
variable.
It seems you could Set OutTask only once. I threw in a named variable to
see if that might be an option you would like.
Again, this is not tested, but my give some ideas to try.


Dim R As Long ' (R)ow
Dim objApp As Object
Dim OutTask As Object

'Set them once here
Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)

Const Dte As Long = 5
Const Subj As Long = 3

R = 2 'Start in Row 2

Do Until Cells(R, Dte).Value = vbNullString
With OutTask
.StartDate = Cells(R, Dte).Value
.Subject = Cells(R, Subj).Value
.Body = Cells(R, 1).Value & " - " & Cells(R, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
.Save
.Close
End With

R = R + 1
Loop

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007


ryguy7272 said:
Finally got it working:
Sub GetOutlookReference()
'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = 2
j = 2
k = 2
l = 2

Do Until Cells(i, 5).Value = ""
'*********************************************
Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask
.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value
.Body = Cells(k, 1).Value & " - " & Cells(l, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
'.ReminderTime = [NextPM]
'.DueDate = [NextPM]
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
.Close
End With
'*********************************************
l = l + 1
k = k + 1
j = j + 1
i = i + 1
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If
End Sub

People's names are in Column A, which is Dimmed as k
Names of Tasks are in Column D, which is Dimmed as j
Descriptions of Tasks are in Column E, which is Dimmed as l
Dates are in Column F, which is Dimmed as i

The only think I can't figure out now is how to close each Task item after
it is opened and populated with the appropriate information. I think I'll
check out the Outlook DG to see if I can find a solution over there.
Thanks
for the help!!

Regards,
Ryan-


--
RyGuy


ryguy7272 said:
Thanks to everyone who looked at my post. The code for the j loop seems
to
be working alright (the j loop pulls information from the worksheet and
populates it in the 'Subject' area of the task). The code for the i loop
returns the current date in the 'Reminder' area of the task. All of the
dates that I want to send to the reminder areas of the task should come
from
column E, but this part of the loop doesn't seem to be working. Specific
dates are calculated in column E; I want these specific dates to be sent
to
each task...not the current date sent to each task.

I'm going to keep trying a few more things. If I resolve the issue, I'll
post back with the solution. Until then, if anyone has any additional
thoughts please let me know...


Thanks,
Ryan--
--
RyGuy


:

I don't know which cells you want your loops to look at but at a glance
it
doesn't look right at all. Make a simple routine that loops cells and
does
nothing else, except either to select cells(i,5) and (j,3) or print
their
address's to the Immediate window. Step through the code with F8. You
might
want to ,move the line that increments i to the outer loop, don't know.

Different issue -

Dim olApp As Outlook.Application
implies you have a reference set to outlook in Tools Ref's. It also
requires
users have Outlook installed and a version same or newer than the
reference
that was saved with your project.

On error resume next
' attempt to attach to an existing instance
Set olApp = GetObject(, "Outlook.Application")
On error Goto 0 ' don't want to paper over errors

If olApp is nothing then
Set olApp = New Outlook.Application
End if

OTH, if you are not sure it's safe to distribute with Early Binding,
after
testing and taking advantage of the intellisense, remove the reference
in
Tools/Ref's

Dim olApp as Object
On error resume next
' attempt to attach to an existing instance
Set olApp = GetObject(, "Outlook.Application")

If olApp is nothing then
CreateObject("Outlook.Application")
End if
On error Goto 0

If olApp is Nothing then
Msgbox "Outlook does not appear to be installed"
Exit Sub
End if


With either approach remove
Dim objApp As Object and
Set objApp = CreateObject("Outlook.Application")
and replace objApp with olApp

Also move 'Dim OutTask As Object' out of the loops and place at the top
of
your routine.

Regards,
Peter T

This is where I am right now:
Sub GetOutlookReference()

'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
i = 2
j = 2
Do Until Cells(i, 5).Value = ""
Do Until Cells(j, 3).Value = ""
'*********************************************

Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask


.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value

.Importance = olImportanceHigh
.Display
.ReminderSet = True
.ReminderPlaySound = True
.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
End With
'*********************************************
j = j + 1
i = i + 1
Loop
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not
installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If

End Sub


The problem is that it gives me the same values for each i and each
j...I
guess it is not incrementing. Any suggestions? BTW, thanks Simon,
but I
think I'm going to try to get my initial concept to work; it seems to
make
a
little more sense to me.


--
RyGuy


:

Hi ryguy,

There are a few ways you can tacle this:

=======================================================================
Range("D2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
=======================================================================

as for Outlook I;m a Notes man I'm afraid.
Click to expand...
Click to expand...
Click to expand...
 
P

Peter T

I haven't tried your code but surely it doesn't make sense to start by
attempting to set a reference to an existing instance of Outlook. But you
don't use that reference at all, instead you create a new instance of
Outlook in EACH loop.

Regards,
Peter T

ryguy7272 said:
Thanks, I actually went with this:
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = 2
j = 2
k = 2
l = 2

Do Until Cells(i, 5).Value = ""
'*********************************************
Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
'Set objOutlookApp = Application
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask
.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value
.Body = Cells(k, 1).Value & " - " & Cells(l, 4).Value
.Importance = olImportanceHigh
'.Display
.ReminderSet = True
'.ReminderTime = [NextPM]
'.DueDate = [NextPM]
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
'.Close
End With
'*********************************************
l = l + 1
k = k + 1
j = j + 1
i = i + 1
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
'Set olApp = CreateObject("Outlook.Application")
Set objOutlook = Application
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If

Not sure if there is a better way of doing it, but it seems to work pretty
well.

Regards,
Ryan--
--
RyGuy


Dana DeLouis said:
Hi. Here are some thoughts.
It appears to me that i,j,k,l all point to the same Row. Perhaps only use 1
variable.
It seems you could Set OutTask only once. I threw in a named variable to
see if that might be an option you would like.
Again, this is not tested, but my give some ideas to try.


Dim R As Long ' (R)ow
Dim objApp As Object
Dim OutTask As Object

'Set them once here
Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)

Const Dte As Long = 5
Const Subj As Long = 3

R = 2 'Start in Row 2

Do Until Cells(R, Dte).Value = vbNullString
With OutTask
.StartDate = Cells(R, Dte).Value
.Subject = Cells(R, Subj).Value
.Body = Cells(R, 1).Value & " - " & Cells(R, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
.Save
.Close
End With

R = R + 1
Loop

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007


ryguy7272 said:
Finally got it working:
Sub GetOutlookReference()
'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

i = 2
j = 2
k = 2
l = 2

Do Until Cells(i, 5).Value = ""
'*********************************************
Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask
.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value
.Body = Cells(k, 1).Value & " - " & Cells(l, 4).Value
.Importance = olImportanceHigh
.Display
.ReminderSet = True
'.ReminderTime = [NextPM]
'.DueDate = [NextPM]
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
.Close
End With
'*********************************************
l = l + 1
k = k + 1
j = j + 1
i = i + 1
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If
End Sub

People's names are in Column A, which is Dimmed as k
Names of Tasks are in Column D, which is Dimmed as j
Descriptions of Tasks are in Column E, which is Dimmed as l
Dates are in Column F, which is Dimmed as i

The only think I can't figure out now is how to close each Task item after
it is opened and populated with the appropriate information. I think I'll
check out the Outlook DG to see if I can find a solution over there.
Thanks
for the help!!

Regards,
Ryan-


--
RyGuy


:

Thanks to everyone who looked at my post. The code for the j loop seems
to
be working alright (the j loop pulls information from the worksheet and
populates it in the 'Subject' area of the task). The code for the i loop
returns the current date in the 'Reminder' area of the task. All of the
dates that I want to send to the reminder areas of the task should come
from
column E, but this part of the loop doesn't seem to be working. Specific
dates are calculated in column E; I want these specific dates to be sent
to
each task...not the current date sent to each task.

I'm going to keep trying a few more things. If I resolve the issue, I'll
post back with the solution. Until then, if anyone has any additional
thoughts please let me know...


Thanks,
Ryan--
--
RyGuy


:

I don't know which cells you want your loops to look at but at a glance
it
doesn't look right at all. Make a simple routine that loops cells and
does
nothing else, except either to select cells(i,5) and (j,3) or print
their
address's to the Immediate window. Step through the code with F8. You
might
want to ,move the line that increments i to the outer loop, don't know.

Different issue -

Dim olApp As Outlook.Application
implies you have a reference set to outlook in Tools Ref's. It also
requires
users have Outlook installed and a version same or newer than the
reference
that was saved with your project.

On error resume next
' attempt to attach to an existing instance
Set olApp = GetObject(, "Outlook.Application")
On error Goto 0 ' don't want to paper over errors

If olApp is nothing then
Set olApp = New Outlook.Application
End if

OTH, if you are not sure it's safe to distribute with Early Binding,
after
testing and taking advantage of the intellisense, remove the reference
in
Tools/Ref's

Dim olApp as Object
On error resume next
' attempt to attach to an existing instance
Set olApp = GetObject(, "Outlook.Application")

If olApp is nothing then
CreateObject("Outlook.Application")
End if
On error Goto 0

If olApp is Nothing then
Msgbox "Outlook does not appear to be installed"
Exit Sub
End if


With either approach remove
Dim objApp As Object and
Set objApp = CreateObject("Outlook.Application")
and replace objApp with olApp

Also move 'Dim OutTask As Object' out of the loops and place at the top
of
your routine.

Regards,
Peter T

This is where I am right now:
Sub GetOutlookReference()

'Outlook objects
Dim olApp As Outlook.Application

'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

'*********************************************
Dim i As Integer
Dim j As Integer
i = 2
j = 2
Do Until Cells(i, 5).Value = ""
Do Until Cells(j, 3).Value = ""
'*********************************************

Dim objApp As Object
Dim OutTask As Object

Set objApp = CreateObject("Outlook.Application")
Set OutTask = objApp.CreateItem(olTaskItem)
With OutTask


.StartDate = Cells(i, 5).Value
.Subject = Cells(j, 3).Value

.Importance = olImportanceHigh
.Display
.ReminderSet = True
.ReminderPlaySound = True
.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
.Save
End With
'*********************************************
j = j + 1
i = i + 1
Loop
Loop

'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If

' If Outlook still isn't running, Outlook cannot open or is not
installed
If olApp Is Nothing Then
Call MsgBox("Outlook could not be opened. Exiting macro.", _
vbCritical, Application.Name)
End If

End Sub


The problem is that it gives me the same values for each i and each
j...I
guess it is not incrementing. Any suggestions? BTW, thanks Simon,
but I
think I'm going to try to get my initial concept to work; it seems to
make
a
little more sense to me.


--
RyGuy


:

Hi ryguy,

There are a few ways you can tacle this:

=======================================================================
Range("D2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
=======================================================================

as for Outlook I;m a Notes man I'm afraid.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Loop 0
Match style 0
getting text out of TextBox into code 0
outlook task 6
send an outlook task to colleagues 5
outlook task recipient 6
Outlook private calendar 1
AddPicturesToShapes 0

Top