Need help with some coding

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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

s.ridd

Board Regular
Joined
Nov 23, 2012
Messages
178
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:

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

Not sure what this is doing either

Code:
.Training = Range("I" & lngRow)

Simon
 

seanmooney

New Member
Joined
May 2, 2013
Messages
3
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

Board Regular
Joined
Nov 23, 2012
Messages
178
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

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

Hope this is of some help

Simon
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,561
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top