seanmooney
New Member
- Joined
- May 2, 2013
- Messages
- 3
Hi there
Im new to the forum and the company I work for.
I am trying to set up a couple of spreadsheets for the likes of training that will alert me on my outlook account 28days before the training is due to expire.
A friend of mine had started something similar but for carrying out fire risk assessments for a previous company that we worked for and i have tried to alter his spreadsheet.
I currently have this coding
Private Sub AddToOLCalendar()
Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long
Set objOL = CreateObject("Outlook.Application")
lngRow = 2
Do While ActiveSheet.Cells(lngRow, 1) <> ""
If Cells(lngRow, 2) <> "" And Cells(lngRow, 9) <> "y" Then
Set objItem = objOL.CreateItem(1) ' constant olAppointmentItem = 1
With objItem
.Body = "Fire Risk Assessment Review Required for this Property"
.Duration = 720
.Start = Range("J" & lngRow)
.Subject = Range("A" & lngRow)
.Training = Range("I" & lngRow)
.Save
End With
Range("i" & lngRow) = "y"
End If
lngRow = lngRow + 1
Loop
Set objItem = Nothing
Set objOL = Nothing
End Sub
When i press play a box comes up and states:
Cannot coerce parameter value.
Outlook cannon translate your string.
When i then hit debug it highlights "Start = Range("J" & lngRow)"
I had realized at the start that i needed J in as the previous letter was the wrong column from my friends spreadsheet.
Im not sure what to do now, so if anyone can help me out it would be much appreciated.
Im new to the forum and the company I work for.
I am trying to set up a couple of spreadsheets for the likes of training that will alert me on my outlook account 28days before the training is due to expire.
A friend of mine had started something similar but for carrying out fire risk assessments for a previous company that we worked for and i have tried to alter his spreadsheet.
I currently have this coding
Private Sub AddToOLCalendar()
Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long
Set objOL = CreateObject("Outlook.Application")
lngRow = 2
Do While ActiveSheet.Cells(lngRow, 1) <> ""
If Cells(lngRow, 2) <> "" And Cells(lngRow, 9) <> "y" Then
Set objItem = objOL.CreateItem(1) ' constant olAppointmentItem = 1
With objItem
.Body = "Fire Risk Assessment Review Required for this Property"
.Duration = 720
.Start = Range("J" & lngRow)
.Subject = Range("A" & lngRow)
.Training = Range("I" & lngRow)
.Save
End With
Range("i" & lngRow) = "y"
End If
lngRow = lngRow + 1
Loop
Set objItem = Nothing
Set objOL = Nothing
End Sub
When i press play a box comes up and states:
Cannot coerce parameter value.
Outlook cannon translate your string.
When i then hit debug it highlights "Start = Range("J" & lngRow)"
I had realized at the start that i needed J in as the previous letter was the wrong column from my friends spreadsheet.
Im not sure what to do now, so if anyone can help me out it would be much appreciated.