Thread: Excel macro - export to Outlook .ics

Hey!

I have this macro in Excel which exports events to an .ics file that i can open with Outlook Calendar. It automaticly populates the calendar with information in the Excel document:

Sub Generate_ICS()
    Dim rng1 As Range, X, i As Long, v As Long
    Dim objFSO, objFile
    Dim FilePath As String
    FilePath = "G:\Service vent - FILNAVNE MÅ IKKE ÆNDRES!\Service til kalender.ics"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(FilePath)
    Set rng1 = Range([a5], Cells(Rows.Count, "B").End(xlUp))
    X = rng1
 
    objFile.write "BEGIN:VCALENDAR" & vbCrLf
    For i = 1 To UBound(X, 1)
        objFile.write "BEGIN:VEVENT" & vbCrLf & "DTSTART:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "DTEND:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "RRULE:FREQ=YEARLY" & _
                      vbCrLf & "SUMMARY:" & X(i, 1) & vbCrLf & "END:VEVENT" & vbCrLf
    Next i
    objFile.write "END:VCALENDAR"
End Sub

It works perfectly however I wan't to do a few modifications to fit my needs. I know absolutely nothing about coding these macros, so I need someone to help me out.

Additionally I wan't to:
- Pick up dates from column 'H' instead of 'B'. (I've tried to just change B to H, but that didn't work)
- Make the events all day events, so that the starting/ending time is not showed up in the calendar, but only the title/name.
- Put in a reminder 1 week before.
- Place all the events in a category.
- Include additional information (more columns). I have a column for location info, aswell as a column for description. I wan't the macro to recognize these columns as location and description.

I hope it's not too much of a trouble to make the modifications.


Thanks!

Best regards
Jeppe C.