Importing Tasks from Access on startup

G

Guest

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
 
S

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
 
G

Guest

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.
 
G

Guest

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
 
S

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
 
G

Guest

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.



Sue Mosher said:
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 said:
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
Click to expand...
Click to expand...
 
M

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.


Grant said:
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.



Sue Mosher said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

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



Marcus Canales said:
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.


Grant said:
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.



Sue Mosher said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add Records to a Table 2
ADO OpenDatabaseConnection 2
Add new records to table 3
ADODB HELP 7
Very lost 5
Need code assistance to update pivot table from an ADO recordset 4
Access 2007 Recordsets? 1
Working with Outlook Contacts from Access 13

Top