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

[1qaz2wsx]

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

 

[jsotola]

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

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