Link to home
Start Free TrialLog in
Avatar of Daron Brewood
Daron BrewoodFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access output to Outlooks tasks in a given folder.

Guys I'm looking to add items from a shopping list in MS Access into a Task list in Outlook (Office 365) for both. I've bodged together code to do this using the MS Knowledge base. This is as:

Private Sub Command22_Click()

    Process_Tasks2
    
End Sub

Public Sub Process_Tasks2()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
 
    Set db = CurrentDb
 
    Set qdf = db.QueryDefs("Query3 - For Task Generation")
 
    Set rst = qdf.OpenRecordset()
 
    With rst
        Do Until .EOF
            'Send E-Mail for each Record
            Debug.Print ![Shopping_Item]
            Call fnc1AddOutlookTask(![Shopping_Item])
            .MoveNext         'Move to the next Record
        Loop
    End With
 
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Sub


Public Sub fnc1AddOutlookTask(Task_to_add)

    Dim OutlookApp As Outlook.Application
    Dim OutlookTask As Outlook.TaskItem
    Dim olTempFolder As Outlook.MAPIFolder
    
    Dim myApp As Outlook.Application
    Dim myNP As Outlook.NameSpace
    Dim myRecip As Outlook.Recipient
    Dim TaskFolder As Outlook.Folder
    Dim myTask As Outlook.TaskItem
    
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
    
    
    With OutlookTask
        ' .Subject = "This is the subject of my task"
        .Subject = Task_to_add
        '.Body = "This is the body of my task."
        '.ReminderSet = True
        'Remind 2 minutes from now.
        .ReminderTime = DateAdd("n", 2, Now)
        'Due 5 minutes from now.
        .DueDate = DateAdd("n", 5, Now)
        '.ReminderPlaySound = True
        'Modify path.
        '.ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
        .Save
    End With
End Sub

Open in new window




This works and does populate the default Task folder with the tasks. However the problem is I want to populate a defined sub-folder and I can't work out how to specify that folder.

Any assistance appreciated.
ASKER CERTIFIED SOLUTION
Avatar of irudyk
irudyk
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Daron Brewood

ASKER

Thanks for that. When I try running it I get: 'Run-time error '424': Object required', with the error line being : 'Set OutlookNS = objApp.GetNamespace("MAPI")'.

User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You sir are a hero, that fixed my problem, completely!
The only (very weird) issue IO have now is that around 10 minutes after added the tasks into Outlook it decides to delete about a third of them making it useless. The tasks vanish completely. Any ideas on that one?
Are there any Outlook addins or code running? Maybe try opening Outlook in Safe mode to see if still happens.

Also, you've listed Access in your question. Are the tasks being created from Access? If so, are you getting any prompts to allow access to the user's Outlook?

Maybe adding the following to the end of routine may make a difference

	Set OutlookTask = Nothing
	Set OutlookTaskSubFolder = Nothing
	Set OutlookTaskFolder = Nothing
	Set OutlookNS = Nothing
	Set OutlookApp = Nothing

Open in new window

Hi, no outlook code or addins running. The tasks are being created from Access. no prompts are popping up at all. I'll try those changes to the end of the routine tomorrow,