Create task request from Excel: reminder problem

  • Thread starter Thread starter Gauthier
  • Start date Start date
G

Gauthier

I've written some vba to send task requests from Excel 2003 (after activating
Outlook 2003 library). Everything works fine except the reminder checkbox
which remains empty although activating the ReminderSet property.

Sub SendBackupTaskToCustomer()
Dim StartingRow, RequestCount As Integer
Dim ExtractedDate As Date
Dim ExtractedAsset, ExtractedUser As String
Dim myOlApp As Outlook.Application
Dim myOlTask As Outlook.TaskItem

'verify action before starting to send task requests
If MsgBox("Send task request for " & Selection.Rows.Count & "
customers?", vbOKCancel) = vbCancel Then Exit Sub

Set myOlApp = CreateObject("Outlook.Application")
StartingRow = Selection.Row
For RequestCount = 0 To Selection.Rows.Count - 1
Set myOlTask = myOlApp.CreateItem(olTaskItem)

'get date from column A
ExtractedDate = Worksheets("Sheet1").Cells(StartingRow + RequestCount,
1).Value

'get asset id from column B and user from col D
ExtractedAsset = Worksheets("Sheet1").Cells(StartingRow +
RequestCount, 2).Value
ExtractedUser = Worksheets("Sheet1").Cells(StartingRow + RequestCount,
4).Value
If ExtractedUser = "" Or ExtractedAsset = "" Then
MsgBox ("Selection empty at row " & StartingRow + RequestCount & ".
Process stopped")
Exit Sub
End If

With myOlTask
'.From = "Migration XPSP2"
.Subject = "Migration: run backup.cmd to back up " & ExtractedAsset
& " documents before " & _
(ExtractedDate - 1)
.Body = "It is mandatory for the safety of your files that, the day
before migration, " & _
"you launch the process called Backup.cmd located as
defined below:" & vbCrLf & _
"M:\Sites\Braine\Migration\Tools\Backup.cmd"

.DueDate = ExtractedDate - 1
.Importance = olImportanceHigh
.ReminderSet = True
.ReminderTime = ExtractedDate - 2
.ReminderPlaySound = True
.ReminderSoundFile = "C:\Windows\Media\Ding.wav"

.Recipients.Add (ExtractedUser)
.Assign
.Send
End With
Set myOlTask = Nothing
Next RequestCount
Set myOlApp = Nothing
End Sub

Configuration: Windows XP SP2 + Office 2003 SP2/SP3
 
It appears that the "reminder" feature turns off when you assign the
task. You are no longer the owner so the "new" owner would need to set
a reminder. Even if you added it manually, it would get wiped out
when the delegate updates the status of the assigned task. Maybe a
followup email reminding them to complete the task?

I found a site with more information:
http://www.windowsitpro.com/Windows/Article/ArticleID/38437/38437.html

ps- there are some errors in your code, if you need help fixing please
let me know.


HTH,
JP
 
Thanks a lot for the explanation. As the main (if not the only) purpose of
this code is to create a reminder which will pop up at the right time to
catch user attention, I will then try with a meeting request.
ps- there are some errors in your code, if you need help fixing please let me know.
Click to expand...
If you spot something, suggestion is always welcome :-)
 
A few things.

1. "Dim StartingRow, RequestCount As Integer" - in VBA, this
translates to "Dim StartingRow As Variant, RequestCount As Integer"

If you are trying to declare both variables as integers, try "Dim
StartingRow As Integer, RequestCount As Integer". The same goes for
"Dim ExtractedAsset, ExtractedUser As String" I think you meant "Dim
ExtractedAsset As String, ExtractedUser As String"

In other words you can share the "Dim", but not the declaration part.

2. "Set myOlApp = CreateObject("Outlook.Application")"

You might want to check if you are already running Outlook, then you
can substitute this code to use the existing instance instead of
always starting a new one:

On Error Resume Next
Set myOlApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set myOlApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0

If myOlApp Is Nothing Then
MsgBox "Could not start Outlook!"
Exit Sub
End If

Or, since you have a reference to the Outlook object library, "Set
myOlApp = New Outlook.Application". CreateObject & GetObject are
really for late-bound code. That being said, I use it all the time.

3. "StartingRow = Selection.Row"

You can eliminate this part of your code and fix the rest so that you
could run it in a loop on multiple rows. For example here is the
cleaned up code.

Sub SendBackupTaskToCustomer()
Dim StartingRow As Integer, RequestCount As Integer
Dim ExtractedDate As Date
Dim ExtractedAsset As String, ExtractedUser As String
Dim myOlApp As Outlook.Application
Dim myOlTask As Outlook.TaskItem

'verify action before starting to send task requests
If MsgBox("Send task request for " & ActiveSheet.UsedRange.Rows.count
- 1 & " customers?", vbOKCancel) = vbCancel Then Exit Sub

Set myOlApp = New Outlook.Application

For RequestCount = 2 To ActiveSheet.UsedRange.Rows.count
Set myOlTask = myOlApp.CreateItem(olTaskItem)
ExtractedDate = Worksheets("Sheet1").Cells(RequestCount, 1).Value
ExtractedAsset = Worksheets("Sheet1").Cells(RequestCount, 2).Value
ExtractedUser = Worksheets("Sheet1").Cells(RequestCount, 4).Value

If ExtractedUser = "" Or ExtractedAsset = "" Then
MsgBox ("Selection empty at row " & RequestCount & ". Process
stopped")
GoTo ExitProc
End If

With myOlTask
'.From = "Migration XPSP2"
.Subject = "Migration: run backup.cmd to back up " &
ExtractedAsset & " documents before " & _
(ExtractedDate - 1)
.Body = "It is mandatory for the safety of your files that,
the day before migration, " & _
"you launch the process called Backup.cmd located as
defined below:" & vbCrLf & _
"M:\Sites\Braine\Migration\Tools\Backup.cmd"

.Recipients.Add (ExtractedUser)
.Assign
.DueDate = ExtractedDate - 1
.Importance = olImportanceHigh
'.ReminderSet = True
'.ReminderTime = ExtractedDate - 2
'.ReminderPlaySound = True
'.ReminderSoundFile = "C:\Windows\Media\Ding.wav"
'.Display
.Send
End With
Next RequestCount

ExitProc:
Set myOlTask = Nothing
Set myOlApp = Nothing

End Sub

Now you can create a spreadsheet with your headers in row 1, and
starting in row 2 you can list as many task recipients as you want and
the macro will create an assigned task for each.

HTH,
JP
 
Thanks a lot JP for these helpfull information, you pointed some errors I was
doing from a long long time, I guess I will have to review many of my VBA
modules now. ;-)
 
Back
Top