Outlook vs Excel - date format issue between modules

[PRPowell]

Hi,

I have cobbled together a spreadsheet using what I know and what I could google - it is designed to enable easy tracking of out-of date documents, and send a calendar meeting on the expiry date as a marker, with some tweaks to ensure eople aren't flooded with unnecessary reminders and multiple calendar items. The problem I cannot get past though is the "ExpiryDate" term not being interpreted properly when used to indicate the .Start for the meeting request.

If ANYONE can figure this out, and in a way that doesn't require a significant re-write, then I would be most grateful. Hopefully, it's just me too dumb not knowing how to convert from one data type to another. The rest of the code works as I need it to.

Here's the calendar module:

Sub SendCal(ByVal SendTo As String, ByVal CCTo As String, ByVal Subj As String, ByVal Body As String)

Const olMeeting = 1
Const olAppointmentItem = 1
Dim olApp As Object
Dim olApt As Object
Dim olCal As Object

Application.ScreenUpdating = False

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
MyApp = True
Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set olCal = olApp.CreateItem(olAppointmentItem)

With olCal
.RequiredAttendees = SendTo & ";" & CCTo
.MeetingStatus = olMeeting
.Start = ExpiryDate + TimeValue("12:00:00")
.Duration = 60
.Subject = Subj
.Location = "N/A"
.Body = Body
.BusyStatus = olBusy
.Reminderset = False
.Display
Application.Wait (Now + TimeValue("0:00:05"))
Application.SendKeys "%s"
End With

Application.ScreenUpdating = True

Set olApp = Nothing
Set olEmail = Nothing

End Sub

I want the term "ExpiryDate" to be picked up from the preceding module:

Sub CheckForExpiryDates()

Dim Cell As Range
Dim ExpiryDate As Range
Dim Mail_Subj As String
Dim Cal_Msg As String

Dim Rng As Range
Dim RngEnd As Range

Set Rng = Worksheets("Sheet1").Range("A2")
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))

For Each Cell In Rng.Cells
Set ExpiryDate = Cell.Offset(0, 2)
Set CCTo = Cell.Offset(0, 5)
Mail_Subj = "ToRRs Renewal Due: " & Cell.Offset(0, 0)
Cal_Msg = "Example Text"
If DateDiff("d", Now(), ExpiryDate) <= 364 And Cell.Offset(0, 6) = "" Then
SendCal Cell.Offset(0, 1), CCTo, Mail_Subj, Cal_Msg
Cell.Offset(0, 6) = Now()
End If
Next Cell

End Sub

Currently what I think is happening is the "ExpiryDate" value (eg 12/02/2013) is either treated like a numeric sum or ignored entirely, which is annoying.

Thanks in advance,

Paul

[Norie]

Paul

ExpiryDate is out of scope of the sub SendCal, so when you refer to it in that sub VBA sees it as an undeclared variable.

Why not pass ExpiryDate to SendCal as you do SendTo, CCTo etc?

[PRPowell]

Norie,

Thanks for this - that's got that sorted nicely. I've now encountered a new issue - but I need to restructure it to make seperate modules for the mail and calendar parts. Once I've done that, if the problem is beyond my skill to fix (not unlikely...), then I'll get back on here with it.
Thanks!
Paul