Results 1 to 13 of 13

Thread: Checking Tasks

  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: Repeative Outlook Tasks

    Folks,

    I am having trouble with tasks from Access to Outlook. I have it setup fine now that my program will make a new task but it makes the same task when it runs again. I want to have the task only created if it does not exsist already. What is the best way to accomplish this? Any links would help.


    Thank you.


    Do While Not Rec.EOF

    'Task Reminder Set
    Set Task = Outlook.CreateItem(olTaskItem)

    NowDate = DateTime.Date
    DateEnd = Nz(Rec("End_Date"))
    TimeLeft = (DateEnd) - (NowDate)

    If TimeLeft <= 60 And TimeLeft > 0 Then

    Task.Subject = Rec("Company") & " " & " " & "Contract Expires in:" & " " & TimeLeft & " " & "days"
    Task.Body = Rec("Company") & " " & vbNewLine & "Contract Expires in:" & " " & TimeLeft & " " & "days" & _
    vbNewLine & "Contract Link:" & " " & Rec("Contract")
    Task.DueDate = DateAdd("d", TimeLeft, Now)
    Task.ReminderTime = DateAdd("ww", 1, "01/19/2004 8:00AM")
    Task.Importance = olImportanceHigh
    Task.GetRecurrencePattern = (1)
    Task.ReminderSet = True
    Task.Save

    Rec.MoveNext

    Else

    Rec.MoveNext

    End If

    Loop

    Rec.close


    Billy
    Last edited by bwood415; 01-22-04 at 12:18.

  2. #2
    Join Date
    Oct 2003
    Posts
    43

    Re: Checking Tasks

    Gotta move this up a bit. Just cause I am stuck on it.

  3. #3
    Join Date
    Oct 2003
    Posts
    43

    Re: Checking Tasks

    Updated code to included a check subject of task but it still does not work. Any help is appreciated:

    Do While Not Rec.EOF

    'Task Reminder Set
    Set Task = Outlook.CreateItem(olTaskItem)

    NowDate = DateTime.Date
    DateEnd = Nz(Rec("End_Date"))
    TimeLeft = (DateEnd) - (NowDate)

    Dim TaskName As NameSpace
    Dim TaskFolder As MAPIFolder
    Dim TF As Boolean
    Dim TS As String
    Set TaskName = Outlook.GetNamespace("MAPI")
    Set TaskFolder = TaskName.GetDefaultFolder(olFolderTasks)



    If Task.Subject = Task.Subject Then
    TF = True
    Else
    TF = False
    End If
    If TimeLeft <= 60 And TimeLeft > 0 And TF = False Then

    Task.Subject = Rec("Company") & " " & " " & "Contract Expires in:" & " " & TimeLeft & " " & "days"
    Task.Body = Rec("Company") & " " & vbNewLine & "Contract Expires in:" & " " & TimeLeft & " " & "days" & _
    vbNewLine & "Contract Link:" & " " & Rec("Contract")
    Task.DueDate = DateAdd("d", TimeLeft, Now)
    Task.ReminderTime = DateAdd("ww", 1, "01/19/2004 8:00AM")
    Task.Importance = olImportanceHigh
    Task.GetRecurrencePattern = (1)
    Task.ReminderSet = True
    Task.Status = olTaskInProgress
    Task.Save

    Rec.MoveNext

    Else

    Rec.MoveNext

    End If

    Loop
    Rec.close

    BillyErrors:

    If Err Then

    MsgBox Err.Description, vbOKOnly, "Error"

    End If
    End Function

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    bwood, wish i could help. However, I don't go near Outlook with a 10 meter pole. I have enough bad hair, I mean bad Microsoft days without the virus vulnerabilities inherent in Outlook. Good luck however!

    Mark

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't know about tasks, but
    Code:
    If Task.Subject = Task.Subject Then 'is always true
      TF = True
    Else
      TF = False
    End If
    
    If TimeLeft <= 60 And TimeLeft > 0 And TF = False Then 'never happens
    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and what is Rec?

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Oct 2003
    Posts
    43
    Originally posted by izyrider
    ...and what is Rec?

    izy
    Rec is the records of the table that I am pulling the end dates from. The whole process goes through the table record by record. I need to as said before check the tasks when I add tasks based on date when they do NOT already exsist only.


    Billy

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and if you do:

    rec.movelast
    msgbox rec.recordcount

    do you get the number of records you expect?

    izy



    PS you also have to fix the
    if 1=1 then
    this
    else
    that
    endif

    if that..... 'which never happens

    problem
    currently using SS 2008R2

  9. #9
    Join Date
    Oct 2003
    Posts
    43
    [QUOTE][SIZE=1]Originally posted by izyrider
    and if you do:

    rec.movelast
    msgbox rec.recordcount

    do you get the number of records you expect?

    izy


    Yes I do get my number of records fine.

    Also

    if that..... 'which never happens[/i]

    I have tried this with true and still does not work.

  10. #10
    Join Date
    Oct 2003
    Posts
    43

    Whole Code

    See below for whole code. Thank you izy for your help.



    Function ContractCheck()

    'Start of Error traper: Like we would have errors
    'On Error GoTo BillyErrors:

    Dim NowDate As Date
    Dim DateEnd As Date
    Dim TimeLeft As Single
    Dim db As Database
    Dim Rec As Recordset
    Dim Message As Outlook.MailItem
    Dim Task As Outlook.TaskItem
    Dim fsokey As Object
    Dim fsopress As Object
    Dim Shell As Object

    Set db = CurrentDb()

    Set Rec = db.OpenRecordset("Contacts")

    'Create a Mail Item
    Set Message = Outlook.CreateItem(olMailItem)


    'Set a Windows Shell session
    Set Shell = CreateObject("Wscript.Shell")

    'Set the FSO As an object to be able to run and make the VBS script
    Set fsokey = CreateObject("Scripting.FileSystemObject")

    'create the vbs script file
    Set fsopress = fsokey.createTextFile("Pass.vbs")

    'Create/Open Windows Scrpit
    fsopress.WriteLine "Set fsokey=CreateObject(""WScript.Shell"")"

    fsopress.WriteLine "While fsokey.AppActivate (""Microsoft Outlook"")=TRUE"

    'End of looping for the file
    fsopress.WriteLine "Wend"

    'Send Keys action which sends the command in to do Ctrl(%) and S which will click the Send button automaticly
    fsopress.WriteLine "fsokey.SendKeys ""%S"", True"

    'End of VBS file
    fsopress.close


    'To Email Address
    'Message.To = "Tony.Pisa@wegmans.com"
    Message.To = "William.Woodhams@wegmans.com"

    DoCmd.OutputTo acOutputReport, "ContractRenewals", "RichTextFormat", "C:\ContractRenewals.rtf", False

    'Body of Email
    Message.Body = "Attached is a report of contracts that expire within the next 60 days." & vbNewLine & vbNewLine & vbNewLine

    'Subject of Email
    Message.Subject = "Contracts"

    'Attach Report of Contracts that are going to expire in 60 Days
    Message.Attachments.Add ("C:\ContractRenewals.rtf")

    'Sets Importance to High
    Message.Importance = olImportanceHigh

    'Display message for getting around the security patch
    Message.Display

    'Runs Shell script of Pass.vbs
    Shell.Run ("Pass.vbs")

    Do While Not Rec.EOF

    'Task Reminder Set
    Set Task = Outlook.CreateItem(olTaskItem)

    NowDate = DateTime.Date
    DateEnd = Nz(Rec("End_Date"))
    TimeLeft = (DateEnd) - (NowDate)

    Dim TaskName As NameSpace
    Dim TaskFolder As MAPIFolder
    Dim TF As Boolean
    Dim TS As String
    Set TaskName = Outlook.GetNamespace("MAPI")
    Set TaskFolder = TaskName.GetDefaultFolder(olFolderTasks)
    For Each Task In TaskFolder.Items

    If Task.Subject = Task.Subject Then
    TF = False
    Else
    TF = True

    If TimeLeft <= 60 And TimeLeft > 0 and TF=False Then

    Task.Subject = Rec("Company") & " " & " " & "Contract Expires in:" & " " & TimeLeft & " " & "days"
    Task.Body = Rec("Company") & " " & vbNewLine & "Contract Expires in:" & " " & TimeLeft & " " & "days" & _
    vbNewLine & "Contract Link:" & " " & Rec("Contract")
    Task.DueDate = DateAdd("d", TimeLeft, Now)
    Task.ReminderTime = DateAdd("ww", 1, "01/19/2004 8:00AM")
    Task.Importance = olImportanceHigh
    Task.GetRecurrencePattern = (1)
    Task.ReminderSet = True
    Task.Status = olTaskInProgress
    Task.Save

    Rec.MoveNext

    Else

    Rec.MoveNext

    End If
    End If
    Next
    Loop
    Rec.close

    BillyErrors:

    If Err Then

    MsgBox Err.Description, vbOKOnly, "Error"

    End If
    End Function

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    still browsing your code, but i see one thing i don't like:
    DateEnd = Nz(Rec("End_Date"))
    if the Rec there is the same Rec we discussed before,
    consider replacing with:
    DateEnd = Nz(Rec!End_Date)
    so you are at least looking at the recordset.

    (....and your original line should have thrown an error. suggestion: comment out the
    on error goto .....
    line while you are developing this routine so that the code stops.

    last thought: i know it's common to use nz() without specifying what happens if it is null - i don't admire the habit!

    maybe at least for debugging purposes, consider putting in a value as in:
    DateEnd = Nz(Rec!End_Date, 180)
    which should be somewhere in the middle of the year 100.

    //a later edit// i don't know what date range outlook understands, maybe it's safer to set the NULL date as 700000 which is somewhere in the 1980's

    izy
    Last edited by izyrider; 01-23-04 at 12:12.
    currently using SS 2008R2

  12. #12
    Join Date
    Oct 2003
    Posts
    43
    OK, couple things.

    1. The Error trapper is commented out. Just for testing purposes.
    2. DateEnd = Nz(Rec("End_Date")) Works the same as with the bang.
    3. I am going to get rid of the NZ because I fixed my table so that its not possible to have no End Date.
    4. the Date Range works great due to it works for the email itself. So it should work fine for the task.

    Thank you again for your help.

    Billy

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry: never saw the rec("[blah]") syntax before.


    meanwhile: with the trapper out, do you get an error?
    yes: what/where?

    if not, slap a breakpoint as early in your code as you can:
    Set db = CurrentDb() 'will do

    and F8 thru it, checking each of your
    Task.*
    to make sure they ARE what you want them to be.

    if you can't track it from there, then it looks like a task syntax problem and i'm afraid you are on your own my friend: tasks are a mystery to me.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •