Running macro in two linked worksheets

Goody61865

Board Regular
Joined
Mar 4, 2011
Messages
50
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?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Goody61865

Board Regular
Joined
Mar 4, 2011
Messages
50
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

Board Regular
Joined
Mar 4, 2011
Messages
50

ADVERTISEMENT

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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
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

Board Regular
Joined
Mar 4, 2011
Messages
50

ADVERTISEMENT

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.
 

Goody61865

Board Regular
Joined
Mar 4, 2011
Messages
50
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

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Try something like this:

Code:
Dim sCaption As String

sCaption = ThisWorkbook.Application.Caption

'Start Outlook here ... Createobject

AppActivate sCaption 'Set focus back to Excel
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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