Sending Outlook Calender invites automatically when a cell is updated in excel spreadsheet

1qaz2wsx

New Member
Joined
Jan 7, 2014
Messages
1
Hey everybody,

I'm new to coding and VBA. I'm a civil engineer and as such, am decent with Excel but not the inner workings of it. I have created a spreadsheet that displays new dates in column D based on provided dates in Column B and recurrence intervals selected in column C. The dates in column D are when a training deadline occurs. I have been trying to get a macros written so that when a cell in column d is updated, an outlook appointment message is sent to the person doing the updating. The idea here is that anyone can use this without future coding (so their address would be placed in cell say A1)

So far all i have gotten is excel to send an email to me with the excel spreadsheet as an attachment, not really what I want. Please help! Again, I dont want somethign specific to my computer settings, I want to be able to send the spreadsheet out with simple instructions for the user to change their email in cell A1 and then every time a cell changes in column D, it automatically generate an appointment reminder for the new date. is this possible? i'm a noob, so dumb it down for me as much as possible. I'm in way over my head here
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
here is some bare bones code

put recipient in A1 , start time B1, end time D1 (start and end is date and time ... excel long date)

please check if this code works for you

Code:
Sub testEmail()


    ' reference "Microsoft Outlook Object Library" must be loaded to use these
'    Dim oApp As Outlook.Application
'    Dim oCi As Outlook.ContactItem
'    Dim oMi As Outlook.MailItem
'    Dim oFl As Outlook.Folder
'    Dim oAppt As Outlook.AppointmentItem
        
        
    ' no reference needed
    Dim oApp As Object
    Dim oCi As Object
    Dim oMi As Object
    Dim oFl As Object
    Dim oAppt As Object


        
    Set oApp = CreateObject("Outlook.Application")
    
    
    ' send an email
'    Set oMi = oApp.CreateItem(olMailItem)
'    oMi.attachments.Add ThisWorkbook.Path & "\test.xlsm"
'    oMi.Body = "test message"
'    oMi.Subject = "subject line"
'    oMi.To = Cells(Application.ActiveCell.Row, 7).Value
'    oMi.Display
'    oMi.Send
'    Set oMi = Nothing
    
'    Stop
    
    Set oAppt = oApp.CreateItem(olAppointmentItem)
    oAppt.Body = "test appointment"
    oAppt.Recipients.Add Range("a1")
    oAppt.Start = Range("b1")
    oAppt.RecurrenceState
    oAppt.End = Range("d1")
    oAppt.Display
'    oAppt.Send                              ' uncomment line to do send
    Set oAppt = Nothing
    
    
    Set oApp = Nothing
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,560
Members
417,220
Latest member
lam150498

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