PDA

View Full Version : vba to send appointment from excel to outlook



Pancakes1032
12-31-2014, 10:43 AM
Hi All,
I'm trying to write this code so that when a date is entered in Column I the code will fire and created an appointment in outlook. Here's the situation. Column I is the due date that a client needs to complete their paperwork and the date in which the appointment is entered in outlook. Column E should be the subject line, that holds the client's name. Column L should be the body. This holds whatever notes the employee writes about the clients. This is the starter code I have that clearly doesn't work. I'm having issues with the ranges. The problem is that this code is fired when a change even occurs and this macro is called. I only want this code to schedule an appointment for the date that was entered in Column I. The issue is column I will have many dates, but only the date that is today's date + 10 is the cell that should trigger the appointment. Hopefully someone can help.


Sub first_ten()

Dim objOL As Object
Dim objItem As Object
Dim MyTime As Date
Dim name As String

name = Application.ActiveWorkbook.name
MyTime = Format("9:00")

Windows(name).Activate
Sheets("Employee log").Select
Set objOL = CreateObject("Outlook.Application")
Set objItem = objOL.CreateItem(1)
With objItem

.Start = MyTime & Range("I2:I").Value
.Body = Range("L2")
.Location = Range("K2")
.AllDayEvent = False
.Subject = Range("E2")
.ReminderMinutesBeforeStart = 10
.ReminderSet = True
.Save
End With
Set objItem = Nothing
Set objOL = Nothing
End Sub
Thank you!