View Single Post
  #4  
Old January 15th 08, 03:35 PM posted to microsoft.public.outlook.program_vba
JP[_3_]
external usenet poster
 
Posts: 201
Default Create task request from Excel: reminder problem

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




On Jan 15, 5:09*am, Gauthier
wrote:
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.


If you spot something, suggestion is always welcome :-)


Ads