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?

 

[Gary McMaster]

Just a guess:

Try adding "DoEvents" after the line(s) that update the calendar.

Gary

 

[Goody61865]

I wasnt sure where to put the "DoEvents"

Here is my code:

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

If ActiveSheet.Cells(lngRow, 2).Text <> "" Then
Set objItem = objOL.createitem(1) ' constant olAppointmentItem = 1

With objItem
.Body = Cells(FormulaCell.Row, "B").Value & vbNewLine & vbNewLine & _
"Your task is due : " & Cells(FormulaCell.Row, "A").Value & _
vbNewLine & vbNewLine & "Please update your task"
.Duration = 60
.Start = Cells(FormulaCell.Row, "D").Value & " 9:00:00 AM"
.Subject = 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]

I'm not sure it will help but I'd put it between the "End With" and "End If".

Maybe the .Save is still in progress when it gets to the MsgBox. DoEvents should force it to do its housekeeping before it displays that modal message box.

Gary

 

[Goody61865]

No luck, thanks though

For some reason it is not recognizing "If Activesheet.Cells blah blah.Value" when running automatic. It runs through just fine manually.

 

[Gary McMaster]

Another guess:

Maybe "CreateObject" shifts the focus to Outlook. Therefore you don't have an active sheet (or workbook for that matter)?

How about "AppActivate" to set the focus back to Excel.

Gary

 

[Goody61865]

Do I replace it? or set another object? I tried both and it still isnt going. I dont understand because manually it runs just fine.

 

[Gary McMaster]

Do I replace it? or set another object?

I don't know what you mean.

Try setting the focus back to Excel using "AppActivate" after you start Outlook.

Gary

 

[Goody61865]

Forgive me, I am relatively new to this. How do I set the focus back using the AppActivate? Every time I try adding it I get an error argument not optional.

 

[Gary McMaster]

Try something like this:

Dim sCaption As String

sCaption = ThisWorkbook.Application.Caption

'Start Outlook here ... Createobject

AppActivate sCaption 'Set focus back to Excel