Note: The other languages of the website are Google-translated. Back to English

How to create Outlook reminders from Excel spreadsheet?

This article is talking about creating Outlook reminders based on data of Excel spreadsheet.

Crete Outlook reminders from Excel spreadsheet with VBA code


Crete Outlook reminders from Excel spreadsheet with VBA code

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: 


Kutools for Outlook - Brings 100 Advanced Features to Outlook, and Make Work Much Easier!

  • Auto CC/BCC by rules when sending email; Auto Forward Multiple Emails by custom; Auto Reply without exchange server, and more automatic features...
  • BCC Warning - show message when you try to reply all if your mail address is in the BCC list; Remind When Missing Attachments, and more remind features...
  • Reply (All) With All Attachments in the mail conversation; Reply Many Emails in seconds; Auto Add Greeting when reply; Add Date into subject...
  • Attachment Tools: Manage All Attachments in All Mails, Auto Detach, Compress All, Rename All, Save All... Quick Report, Count Selected Mails...
  • Powerful Junk Emails by custom; Remove Duplicate Mails and Contacts... Enable you to do smarter, faster and better in Outlook.
shot kutools outlook kutools tab 1180x121
shot kutools outlook kutools plus tab 1180x121
 
Comments (53)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi There,

Great formula/code.


What happens that this code generates multiple entries in the outlook calendar if its run more than once by mistake. I also wanted it to be setup as a template so that staff can use it. They can put in the dates and rest of the details are required and when they run it should list the entries in outlook calendar. Any deleted excel data should be deleted from outlook as well and same applies to any changes. When I save the excel spreadsheet it saves in the default excel format, but I want it to be saved with Macros so that it does not lose the code and staff can use it as many times as needed. Can you please advise and make relevant changes to the code? Thanks
This comment was minimized by the moderator on the site
Hi,
For saving the macros for future use in the Workbook, you need to click File > Save As > Browse > select a folder to save the workbook > choose “Excel Macro-enabled Workbook” from the Save as type drop-down > Save.
This comment was minimized by the moderator on the site
Hi,

How can I send the reminders to multiple people? Please help.
This comment was minimized by the moderator on the site
Hello, thank you for the code but I'm struggling with it only putting in the first entry in excel into Outlook. How do i get it to enter in all line items?
Here is my code, I added in a category color


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) + xRg.Cells(I, 8).Value
xOutItem.Duration = xRg.Cells(I, 4).Value
xOutItem.Categories = xRg.Cells(I, 9).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
This comment was minimized by the moderator on the site
I'm having the same issue, the code works great but is only adding the first appointment to the calendar. Here is my code:
<code>
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
</code>
This comment was minimized by the moderator on the site
You need to change Set xRg = Range("A2:G2") to be A":wherever your data ends, so if you have 20 rows it will be A2:G20.
This comment was minimized by the moderator on the site
Hello! Love the idea of this code, but I have a few questions:


1. It won't run because of the "bug" on this line (error 440, Run time error):
xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
2. If I don't necessarily want to put the column headers from A2:G2, can i just simply change it in the code (ex: F3-K3), will it still work?
3. How does it know to connect to my outlook?


Thank you for your response, I'd really appreciate it!
This comment was minimized by the moderator on the site
Hi Alexandra,
The 440 error didn't occur in my case. Which Office version do you use?
The code won't work if you simply change the code to F3:K3. If you need the answer, i will try to figure it out.
This line "Set xOutApp = CreateObject("Outlook.Application")" will help to connect the Excel data to the Outlook application.
Thank you for your comment.
This comment was minimized by the moderator on the site
Hello, the code seems very helpful but has one drawback, If the file is updated continuously it will create a new reminder each time it is run. Can a line be added to delete all previous reminders and then create new ones?
Also can this be exported to multiple outlook accounts?
This comment was minimized by the moderator on the site
Hi Keith,
Sorry can't help you with that. You need to delete all previous reminders manually in Outlook. And the reminders are added to the default Outlook account only. Thank you for your comment.
This comment was minimized by the moderator on the site
This has the potential to be a big help.  Thanks!.  Can you add a marker in a new column that gets turned on when a reminder is generated for that row (either manual or automated), but then skips rows with markers the next time you run the macro? 
This comment was minimized by the moderator on the site
this code works but only for the first entry. How do you adjust it for range: Sheet3!A2:G17 ?

I changed the code to say: Set xRg = Range("Set xRg = Range("A2:G2") Yet it still only picks the first line up in outlook.
This comment was minimized by the moderator on the site
Good day,
Please replace the line Set xRg = Range("A2:G2") with Set xRg = Worksheets("Sheet3").Range("A2:G17").
Thank you for your comment.
This comment was minimized by the moderator on the site
Hi Crystal,
I already replaced the line and it works, but 'body' in second row and so on are not come out in reminder, but only picks first row.
Please advise..
This comment was minimized by the moderator on the site
how to add others attendees as well in this appointment.
This comment was minimized by the moderator on the site
Hi Dharma,
Sorry the probem can't be solved yet. Thank you for your comment.
This comment was minimized by the moderator on the site
Hi Crystal! Great code.

I need to add/invite attendees. Any idea?
This comment was minimized by the moderator on the site
dear webmaster
thank you for the code. I have a problem as it failed to implemented. (run-time error '-21447467263 (80004001)' : Not implemented.
when I press the debug button it highlights the "xOutItem.Save" code line 28.
could you give solution of this issue?
regards
Singgih
This comment was minimized by the moderator on the site
Hi singgih,
Sorry for replying so late. I need to know your Excel version in order to figure out the problem. Thank you for your comment.
This comment was minimized by the moderator on the site
Do you know how i can put a all day event?
This comment was minimized by the moderator on the site
Hi,
Sorry can't help solving the problem yet.
This comment was minimized by the moderator on the site
Hi,

Is it possible to use code to make entry on group calendar in outlook?
This comment was minimized by the moderator on the site
Hi,
Sorry can't help solving the problem yet.
This comment was minimized by the moderator on the site
If I use this in a gantt chart and I update due dates must I re run? Is there an easy way to remove from calendar without deleting each individual one?
This comment was minimized by the moderator on the site
when we use VBA code for reminder then error shows
Compliler error :
User define type is not define
after press ok button
cursor move on DIM xRg as Range

please help...??
This comment was minimized by the moderator on the site
Hi vineet,
Sorry for the inconvenience. Which Excel version are you using?
This comment was minimized by the moderator on the site
Hi..
Shall we run this macro everytime to activate the reminder ? or we only need to click just once ? then its ok tu torn of the computer and close the excel file ?

Thanks.
This comment was minimized by the moderator on the site
Hi Juicy,
Just need one click to run the code, then prepared reminders will be automatically created in the Outlook calendar. After that, you can do anything as you like.
This comment was minimized by the moderator on the site
Hi, Is there a way to automate running this code, eg. when opening outlook?
This comment was minimized by the moderator on the site
Hi chan,
As the code is saved in Excel workbook, we can't automatically run it when opening Outlook. Sorry for that.
This comment was minimized by the moderator on the site
Hello,

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.

Thanks
This comment was minimized by the moderator on the site
Hi Abe,
As the reminder fields have been created in a worksheet, you need to add the code to Microsoft Excel workbook's Module code window and run it in Excel.
This comment was minimized by the moderator on the site
i have some dynamic table in excel to be made available in body of reminder. Can we do that ? please suggest how
This comment was minimized by the moderator on the site
So we have an excel spreadsheet that we enter expiration dates for licenses, and we are wondering how we would do it so that when we add new expiration dates it will automatically add them to our outlook calendar with reminders a month prior and 2 weeks prior. What would that require?
This comment was minimized by the moderator on the site
Hi, is it possible to specify which calendar the reminder will be added to. I would like to add them to a shared mailbox calendar so all in the team get the reminder, rather than my personal email calendar
This comment was minimized by the moderator on the site
Hi! 
Did you find a way to add the reminder(s) to the entire team? I want to include one more person only and I am wondering whether possible and how to do this
This comment was minimized by the moderator on the site
Hi, many thanks for this code. It work beautifully on windows. Do you happen to know how i can implement this on Mac? I've been trying to find a way for a few days now, one way i've found is to create an AppleScript but i have no knowledge of how to do this. Another way was to use a Virtual Machine running Windows 10 however it's not my ideal way of working.
This comment was minimized by the moderator on the site
Error "This object does not support this action" on xOutItem.ReminderMinutesBeforeStart = xRg.Cells(I, 6).Value
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL