Running macro in two linked worksheets

[Goody61865]

I put a due date on one worksheet without the outlook code and it goes to another worksheet with the outlook code. This code populates the outlook calender with the event. My problem is that the code runs great both automatically and manually but it only fills the calender event when ran manually. Why wont it populate the calender in auto when the code runs?

 

[Goody61865]

That worked, but now a new problem. The date of my calender entry is December 30, 1899. I am assuming that means that it is not reading the correct cell, but I havent changed anything and it worked fine before.

 

[Gary McMaster]

Try your original code (slightly modified below) without using "ActiveSheet". I'm not certain of it, but I think Excel is losing track of the Active sheet when you start Outlook and Outlook receives the focus. For example: If you had multiple instances of Excel running each of them would have an active sheet. How would it know which activesheet you were talking about when it reached your original line "Activesheet ...."?

Be sure to change "Sheet1" to the real name.

This of course does not address your new "Y19K" issue

Sub Update_Calender(FormulaCell As Range)

Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long

Set objOL = CreateObject("Outlook.Application")

lngRow = 4

Dim oSheet As Worksheet
Set oSheet = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to actual sheet name

If oSheet.Cells(lngRow, 2).Text <> "" Then
    Set objItem = objOL.createitem(1) ' constant olAppointmentItem = 1
    
    With objItem
        .Body = oSheet.Cells(FormulaCell.Row, "B").Value & vbNewLine & vbNewLine & _
        "Your task is due : " & oSheet.Cells(FormulaCell.Row, "A").Value & _
        vbNewLine & vbNewLine & "Please update your task"
        .Duration = 60
        .Start = oSheet.Cells(FormulaCell.Row, "D").Value & " 9:00:00 AM"
        .Subject = oSheet.Range("B4").Value
        .ReminderMinutesBeforeStart = "30"
        .Save
    End With
End If

lngRow = lngRow + 1

Set objItem = Nothing
Set objOL = Nothing

MsgBox "Your due date for this task has been added to your calender"

End Sub

 

[Gary McMaster]

Bit of a screw-up there. You should set the "oSheet" variable before you start Outlook (CreateObject).

Sorry for the mistake.

Gary

 

[Goody61865]

It appears so far that this is working, thanks.

I will verify it tomorrow. Thanks again you were a huge help, I have been looking at this thing for 2 days.

 

[Goody61865]

Hmmm It appears to be working despite the oSheet variable. How do I set that?

 

[Gary McMaster]

It was already set by this line:

Set oSheet = ThisWorkbook.Worksheets("Sheet1") 'Change the sheet name if needed

But you need to move the above line and its "Dim" statement above the:

Set objOL = CreateObject("Outlook.Application") statement.

Gary