Importing Tasks from Access on startup

[Grant Bush]

I've got some vba code setup to import tasks from an access table upon
outlook starting, but I need to add code to compare the task items to make
sure that they aren't duplicated. Below is the code that I have. I would
appreciate any help offerred and if there is a better code that should be
used, let me know.

thanks.

Grant Bush

Private Sub Application_Startup(ByVal Item As Object, Cancel As Boolean)

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder

Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)

Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
oltaskitem.Display
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing



End Sub

 

[Sue Mosher [MVP-Outlook]]

You can use the MAPIFolder.Items.Find method to look for an item that matches your specific criteria. (You have to decide what constitutes a match. Have you thought about maintaining a unique ID in the Outlook tasks that match an ID in your Access table?) See http://www.outlookcode.com/d/finddate.htm for tips on searching on date fields.

You don't need to display and close the newly created items. Just use Save.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Grant Bush]

I do have the items in access set up with an unique ID number. That is easy
enough to change the code to add the unique ID and i have it going to the
billing information field in tasks.

I'll take a look at the link and see what I can find for only adding new
items.

Also thanks for the hint on save instead of display.

 

[Grant Bush]

Ok. I've tried to get this work and I must be heading down the wrong path.
here is what I've tried. I have it setup that the unique ID (tracking #)
gets put into the billing information field. Any help would be welcomed.

Grant

Sub startup()

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder
Dim oltaskitems As Outlook.Items
Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)
Set oltaskitems = oltasksfolder.Items


Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing
End Sub

 

[Sue Mosher [MVP-Outlook]]

You didn't say what's not working, and I can't quite figure out what the code is designed to do since it uses the same variable to create a new task, then loop through and look at all the other tasks. You also didn't use MAPIFolder.Items.Find method as I suggested. Is this what you're trying to do? (pseudo code):

1. Get the record from the database
2. Check to see if a matching task exists in Outlook
3. If not, create a new task and set its properties
4. If so, then update any properties that changed.

Step 2 is the one that uses Find:

On Error Resume Next
strFind = "[BillingInformation] = " & _
Chr(34) & objrst.Fields("tracking #") & Chr(34)
Set olTaskItem = oltasksfolder.Items.Find(strFind)
If olTaskItem Is Nothing Then
' create new task
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
' set field values for new task
olTaskItem.Save
Else
' check to see if any fields need updating
' and if so update them
olTaskItem.Save
End If



While Not objrst.EOF
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Grant Bush]

thanks for the help. I went down the wrong road on the code, but what you
gave me help solve the problem. It is now working the way it should be.
this is one of my first attempts at writing vba code for outlook.

You didn't say what's not working, and I can't quite figure out what the code is designed to do since it uses the same variable to create a new task, then loop through and look at all the other tasks. You also didn't use MAPIFolder.Items.Find method as I suggested. Is this what you're trying to do? (pseudo code):

1. Get the record from the database
2. Check to see if a matching task exists in Outlook
3. If not, create a new task and set its properties
4. If so, then update any properties that changed.

Step 2 is the one that uses Find:

On Error Resume Next
strFind = "[BillingInformation] = " & _
Chr(34) & objrst.Fields("tracking #") & Chr(34)
Set olTaskItem = oltasksfolder.Items.Find(strFind)
If olTaskItem Is Nothing Then
' create new task
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
' set field values for new task
olTaskItem.Save
Else
' check to see if any fields need updating
' and if so update them
olTaskItem.Save
End If



While Not objrst.EOF
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Ok. I've tried to get this work and I must be heading down the wrong path.
here is what I've tried. I have it setup that the unique ID (tracking #)
gets put into the billing information field. Any help would be welcomed.

Grant

Sub startup()

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder
Dim oltaskitems As Outlook.Items
Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)
Set oltaskitems = oltasksfolder.Items


Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing
End Sub

 

[Marcus Canales]

I'm having a little bit of trouble with this code. When I use the

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

I get a message saying that UserDefined Type not recognized. I'm using
Outlook 2003. Any help would be appreciated.

thanks for the help. I went down the wrong road on the code, but what you
gave me help solve the problem. It is now working the way it should be.
this is one of my first attempts at writing vba code for outlook.

You didn't say what's not working, and I can't quite figure out what the code is designed to do since it uses the same variable to create a new task, then loop through and look at all the other tasks. You also didn't use MAPIFolder.Items.Find method as I suggested. Is this what you're trying to do? (pseudo code):

1. Get the record from the database
2. Check to see if a matching task exists in Outlook
3. If not, create a new task and set its properties
4. If so, then update any properties that changed.

Step 2 is the one that uses Find:

On Error Resume Next
strFind = "[BillingInformation] = " & _
Chr(34) & objrst.Fields("tracking #") & Chr(34)
Set olTaskItem = oltasksfolder.Items.Find(strFind)
If olTaskItem Is Nothing Then
' create new task
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
' set field values for new task
olTaskItem.Save
Else
' check to see if any fields need updating
' and if so update them
olTaskItem.Save
End If



While Not objrst.EOF
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Ok. I've tried to get this work and I must be heading down the wrong path.
here is what I've tried. I have it setup that the unique ID (tracking #)
gets put into the billing information field. Any help would be welcomed.

Grant

Sub startup()

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder
Dim oltaskitems As Outlook.Items
Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)
Set oltaskitems = oltasksfolder.Items


Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing
End Sub





:

I do have the items in access set up with an unique ID number. That is easy
enough to change the code to add the unique ID and i have it going to the
billing information field in tasks.

I'll take a look at the link and see what I can find for only adding new
items.

Also thanks for the hint on save instead of display.



:

You can use the MAPIFolder.Items.Find method to look for an item that matches your specific criteria. (You have to decide what constitutes a match. Have you thought about maintaining a unique ID in the Outlook tasks that match an ID in your Access table?) See http://www.outlookcode.com/d/finddate.htm for tips on searching on date fields.

You don't need to display and close the newly created items. Just use Save.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



I've got some vba code setup to import tasks from an access table upon
outlook starting, but I need to add code to compare the task items to make
sure that they aren't duplicated. Below is the code that I have. I would
appreciate any help offerred and if there is a better code that should be
used, let me know.

thanks.

Grant Bush

Private Sub Application_Startup(ByVal Item As Object, Cancel As Boolean)

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder

Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)

Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
oltaskitem.Display
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing



End Sub

 

[Sue Mosher [MVP-Outlook]]

Do you have a reference to the ADO library in your code project?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

I'm having a little bit of trouble with this code. When I use the

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

I get a message saying that UserDefined Type not recognized. I'm using
Outlook 2003. Any help would be appreciated.

thanks for the help. I went down the wrong road on the code, but what you
gave me help solve the problem. It is now working the way it should be.
this is one of my first attempts at writing vba code for outlook.

You didn't say what's not working, and I can't quite figure out what the code is designed to do since it uses the same variable to create a new task, then loop through and look at all the other tasks. You also didn't use MAPIFolder.Items.Find method as I suggested. Is this what you're trying to do? (pseudo code):

1. Get the record from the database
2. Check to see if a matching task exists in Outlook
3. If not, create a new task and set its properties
4. If so, then update any properties that changed.

Step 2 is the one that uses Find:

On Error Resume Next
strFind = "[BillingInformation] = " & _
Chr(34) & objrst.Fields("tracking #") & Chr(34)
Set olTaskItem = oltasksfolder.Items.Find(strFind)
If olTaskItem Is Nothing Then
' create new task
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
' set field values for new task
olTaskItem.Save
Else
' check to see if any fields need updating
' and if so update them
olTaskItem.Save
End If



While Not objrst.EOF
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Ok. I've tried to get this work and I must be heading down the wrong path.
here is what I've tried. I have it setup that the unique ID (tracking #)
gets put into the billing information field. Any help would be welcomed.

Grant

Sub startup()

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder
Dim oltaskitems As Outlook.Items
Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)
Set oltaskitems = oltasksfolder.Items


Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
For Each oltaskitem In oltaskitems
If oltaskitem.BillingInformation <> objrst.Fields("tracking #") Then
oltaskitem.Save
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
oltaskitem.BillingInformation = objrst.Fields("tracking #")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
End If
Next
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing
End Sub





:

I do have the items in access set up with an unique ID number. That is easy
enough to change the code to add the unique ID and i have it going to the
billing information field in tasks.

I'll take a look at the link and see what I can find for only adding new
items.

Also thanks for the hint on save instead of display.



:

You can use the MAPIFolder.Items.Find method to look for an item that matches your specific criteria. (You have to decide what constitutes a match. Have you thought about maintaining a unique ID in the Outlook tasks that match an ID in your Access table?) See http://www.outlookcode.com/d/finddate.htm for tips on searching on date fields.

You don't need to display and close the newly created items. Just use Save.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



I've got some vba code setup to import tasks from an access table upon
outlook starting, but I need to add code to compare the task items to make
sure that they aren't duplicated. Below is the code that I have. I would
appreciate any help offerred and if there is a better code that should be
used, let me know.

thanks.

Grant Bush

Private Sub Application_Startup(ByVal Item As Object, Cancel As Boolean)

Dim olns As Outlook.NameSpace
Dim oltaskfolder As Outlook.MAPIFolder

Dim oltaskitem As Outlook.TaskItem

Dim objConn As ADODB.Connection
Dim objrst As ADODB.Recordset
Dim objfld As ADODB.field
Dim strsql As String

Set olns = Application.GetNamespace("MAPI")
Set oltasksfolder = olns.GetDefaultFolder(olFolderTasks)

Set objConn = CreateObject("ADODB.Connection")
Set objrst = CreateObject("ADODB.Recordset")

strsql = "SELECT * From Gbdd;"

objConn.provider = "Microsoft.jet.oledb.4.0"
objConn.Open "I:\bush\rockford ehs.mdb"

objrst.Open strsql, objConn, adopenforwardonly, adlockoptimistic

objrst.movefirst

While Not objrst.EOF
Set oltaskitem = oltasksfolder.Items.Add("IPM.task")
oltaskitem.Display
oltaskitem.Subject = objrst.Fields("Description")
oltaskitem.DueDate = objrst.Fields("compliance due date")
oltaskitem.ReminderTime = objrst.Fields("reminder date")
' oltaskitem.Notes = objrst.Fields("plant affected")
' oltaskitem.Status = objrst.Fields("status")
objrst.movenext
oltaskitem.Close olSave
Wend

objrst.Close
objConn.Close

Set objrst = Nothing
Set objConn = Nothing
Set olctitem = Nothing
Set objprospectfolder = Nothing
Set objctfolder = Nothing
Set objns = Nothing



End Sub