Microsoft Excel - Adding reminders to Outlook from Excel

Asked By Naveen on 26-Sep-12 05:46 AM

Hello


Iam trying to add reminder to Outlook from Excel.


I have a piece of coding that adds the reminder to Outlook. The date and subject mentioned on A6(date) and B6(subject) are getting added to outlook succesfully, but I have multiple dates to be added to outlook, where Iam struck as I dont know how to refer a range of cells as dates. Below is the code-


Dim olApp As Outlook.Application

Dim olAppItem As Outlook.AppointmentItem

Set olApp = GetObject("", "Outlook.Application")

Set olAppItem = olApp.CreateItem(olAppointmentItem)

With olAppItem

.Start = Cells(6, 1).Value

.Subject = Cells(6, 2).Value

.Duration = 1

.ReminderSet = True

.Save

End With
MsgBox "Reminder added to Outlook", vbInformation, "Reminder"



wally eye replied to Naveen on 26-Sep-12 04:50 PM
I guess it would depend on how the dates are arranged in your spreadsheet.  Typically, I would just loop through and each olAppItem, something like this:

dim rngDates  as excel.range
Dim olApp As Outlook.Application

Dim olAppItem As Outlook.AppointmentItem

dim arrDates    as variant

dim intCol     as integer
dim lngLastRow   as long
dim lngRow  as long

set rngdates = [A6]

intcol = rngdates.column
lnglastrow = rngdates.parent.columns(intcol).find(What:="*", _
After:=rngdates.parent.cells(1,intcol), _
SearchOrder:=xlbyrows, SearchDirection:=xlprevious, _
LookAt:=xlpart, LookIn:=xlvalues).row

arrdates = rngdates.resize(lnglastrow - rngdates.row + 1,2)

Set olApp = GetObject("", "Outlook.Application")
for lngrow = lbound(arrdates) to ubound(arrdates)

    Set olAppItem = olApp.CreateItem(olAppointmentItem)

    With olAppItem

    .Start = arrdates(lngrow, 1)

    .Subject = arrdates(lngrow, 2)

    .Duration = 1

    .ReminderSet = True

    .Save

    End With
next lngrow

set olappitem = nothing
set olapp = nothing
set rngdates = nothing

This allows you to set the starting cell and identifies the last used cell in the column, copies all the values into arrdates, then loops through arrdates to set up the reminders.

Naveen replied to wally eye on 27-Sep-12 04:44 AM
macro.zip

I hv attached the sheet and all the details will be in the same order as mentioned in the spreadsheet.

Please help!
wally eye replied to Naveen on 27-Sep-12 10:19 AM
The code worked ok, here it is in your spreadsheet:

OutlookReminder.zip
Naveen replied to wally eye on 08-Oct-12 09:18 AM
Its working fine..thank u v much!!
wally eye replied to Naveen on 09-Oct-12 06:57 PM
Cool!  Glad to be of help.