When I follow the exact steps with Microsoft Outlook open and running in the background, running the macro gives me an error " Compile error: invalid outside procedure". Any help would be appreciated.
This article is talking about creating Outlook reminders based on data of Excel spreadsheet.
If you want to create Outlook reminders from Excel, please do as follows.
1. Create a worksheet contains the column headers and corresponding reminder fields as below screenshot shown.
Note: For the busy status column, number 2 means that the reminder will be shown as Busy in your Outlook calendar. You can change it to 1 (Tentative), 3 (Out of Office), 4 (Working elsewhere), or 5 (Free) as you need.
2. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
3. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the Code window.
VBA code: Create Outlook reminders from Excel spreadsheet
Sub AddAppointments() 'Update by Extendoffice 20180608 Dim I As Long Dim xRg As Range Dim xOutApp As Object Dim xOutItem As Object Set xOutApp = CreateObject("Outlook.Application") Set xRg = Range("A2:G2") For I = 1 To xRg.Rows.Count Set xOutItem = xOutApp.createitem(1) Debug.Print xRg.Cells(I, 1).Value xOutItem.Subject = xRg.Cells(I, 1).Value xOutItem.Location = xRg.Cells(I, 2).Value xOutItem.Start = xRg.Cells(I, 3).Value xOutItem.Duration = xRg.Cells(I, 4).Value If Trim(xRg.Cells(I, 5).Value) = "" Then xOutItem.BusyStatus = 2 Else xOutItem.BusyStatus = xRg.Cells(I, 5).Value End If If xRg.Cells(I, 6).Value > 0 Then xOutItem.ReminderSet = True xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value Else xOutItem.ReminderSet = False End If xOutItem.Body = xRg.Cells(I, 7).Value xOutItem.Save Set xOutItem = Nothing Next Set xOutApp = Nothing End Sub
Note: In the above code, A2:G2 is the data range that you want to create appointments based on.
4. Press the F5 key or click the Run button to run the code. Then all the appointments with specific fields will be inserted into the calendar of your Outlook at once.
And then, you can go to the Calendar of your outlook to view the results, See screenshot: