Need help with some coding

[seanmooney]

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.

 

[seanmooney]

Anyone able to help?

 

[s.ridd]

The error message you're getting means that one of your inputs isn't the right format. This is most likely to be the .Start property. You could try this:

.Start = Format(Range("J" & lngRow), "dd/mm/yyyy")

Not sure what this is doing either

.Training = Range("I" & lngRow)

Simon

 

[seanmooney]

Simon, many thanks for the reply.

I will try this in the morning when I get into work.

would there be another code that I could use if the coding that I have is wrong.

all I am looking to do is have a spreadsheet with a column with the employees names, the training type and when it is due to expire then link this to an outlook address to receive either an e-mail or a calendar reminder.

 

[s.ridd]

The little addition to the code provided should solve the initial error.

As for the .Training field, I'm taking a guess that this is a user defined field you're trying to populate. If so, how are you going to use this? Do you have some other code or process to sort/count/filter/etc by this field or is it just some information that is handy to know? If it is the latter you might find it easier to just add it in to the subject. If it is the former then you want to google .UserProperties (the help is pretty useful), you might start somewhere like replacing .Training with

.UserProperties.Add("Training", olCombination, True, 1).Value = Range("I" & lngRow)

Hope this is of some help

Simon