jsabo
04-29-2014, 01:55 AM
Hello,
I am creating a tool that exports tasks to Outlook based on fields that are manually filled in an excel file. I plan on having a button for the user to click to automatically achieve this. In case anyone's curious, here's the code so far:
Sub InsuranceNotifier()
'
' InsuranceNotifier Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
' how to go to next sheet in book: Worksheets(ActiveSheet.Index + 1).Select
Dim k As Integer
Dim LastRow As Long
Dim i As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 4 To LastRow
If ActiveSheet.Range("L" & i) <> "" Then
Const olTaskItem = 3
Set objOutlook = CreateObject("Outlook.Application")
Set objTask = objOutlook.CreateItem(olTaskItem)
objTask.Subject = "INSURANCE RENEWAL: " & ActiveSheet.Range("A" & i)
objTask.Body = "Renewal of: " & ActiveSheet.Range("F" & i) & " - Subcontract Administrator: " & ActiveSheet.Range("B" & i)
objTask.ReminderSet = True
objTask.ReminderTime = ActiveSheet.Range("K" & i)
objTask.DueDate = ActiveSheet.Range("J" & i)
objTask.ReminderPlaySound = True
objTask.ReminderSoundFile = "C:\Windows\Media\Ding.wav"
objTask.Save
If ActiveSheet.Range("M" & i) = "" Then
ActiveSheet.Range("M" & i) = "Task Reminder Created on " & Format(Now(), "MMMM dd, yyyy")
Else: End If
Else: End If
Next i
End Sub
I am concerned - if someone is to click the button to export the tasks to Outlook multiple times, it will junk up their task list with multiple duplicated tasks. Is there way to simply overwrite tasks in case of duplicate ones or something? Please help! thanks.
I am creating a tool that exports tasks to Outlook based on fields that are manually filled in an excel file. I plan on having a button for the user to click to automatically achieve this. In case anyone's curious, here's the code so far:
Sub InsuranceNotifier()
'
' InsuranceNotifier Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
' how to go to next sheet in book: Worksheets(ActiveSheet.Index + 1).Select
Dim k As Integer
Dim LastRow As Long
Dim i As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 4 To LastRow
If ActiveSheet.Range("L" & i) <> "" Then
Const olTaskItem = 3
Set objOutlook = CreateObject("Outlook.Application")
Set objTask = objOutlook.CreateItem(olTaskItem)
objTask.Subject = "INSURANCE RENEWAL: " & ActiveSheet.Range("A" & i)
objTask.Body = "Renewal of: " & ActiveSheet.Range("F" & i) & " - Subcontract Administrator: " & ActiveSheet.Range("B" & i)
objTask.ReminderSet = True
objTask.ReminderTime = ActiveSheet.Range("K" & i)
objTask.DueDate = ActiveSheet.Range("J" & i)
objTask.ReminderPlaySound = True
objTask.ReminderSoundFile = "C:\Windows\Media\Ding.wav"
objTask.Save
If ActiveSheet.Range("M" & i) = "" Then
ActiveSheet.Range("M" & i) = "Task Reminder Created on " & Format(Now(), "MMMM dd, yyyy")
Else: End If
Else: End If
Next i
End Sub
I am concerned - if someone is to click the button to export the tasks to Outlook multiple times, it will junk up their task list with multiple duplicated tasks. Is there way to simply overwrite tasks in case of duplicate ones or something? Please help! thanks.