PDA

View Full Version : Excel to Outlook help



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.

snb
04-29-2014, 02:03 AM
You might have a look over here:

http://www.snb-vba.eu/VBA_Outlook_external_en.html#H14