Hi,

I had alot of help yesterday from Jacob with the following macro, but am
getting a 'run time error 13' when trying to run the macro, and i cannot see
why.

Any help much appreciated

Sub OLApp()

Dim objOL As Object, objApp As Object, lngRow As Long

Set objOL = CreateObject("Outlook.Application")

For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("E" & lngRow) = "" Then
Set objApp = objOL.CreateItem(1)
With objApp
..Subject = "Change Password for system" & Range("A" & lngRow)
..Start = Range("B" & lngRow)
..ReminderPlaySound = True
..Save
End With
Range("E" & lngRow) = "Done"
End If
Next
Set objOL = Nothing
End Sub

Please ignore, i have worked it out.

Thanks

Check your other post.

I dont see any reason why that should give an error.
--
Jacob (MVP - Excel)


"Dan Wood" wrote:

Please ignore, i have worked it out.

Thanks

Sorry i was being stupid again like yesterday! I simply had to change the
line .Start = Range("B" & lngRow) to the correct column!

Sorry!

I do have another question though.

Is there a way to automatically clear the colum 'E' if something in colom
'C' is changed?

Try the below..

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Dim varData As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If varData < "" And varData < Target.Value Then
Target.Offset(, 2).ClearContents
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varData = Target.Value
End Sub

--
Jacob (MVP - Excel)


"Dan Wood" wrote:

I do have another question though.

Is there a way to automatically clear the colum 'E' if something in colom
'C' is changed?

nice !


"Jacob Skaria" wrote:

Try the below..

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.


Dim varData As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If varData < "" And varData < Target.Value Then
Target.Offset(, 2).ClearContents
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varData = Target.Value
End Sub

--
Jacob (MVP - Excel)


"Dan Wood" wrote:

I do have another question though.

Is there a way to automatically clear the colum 'E' if something in colom
'C' is changed?