Automatically Create Outlook Task When Value <50 using adjacent cell as subject/title

[indiegoober]

Hello World!

This is literally my first foray into macros and haven't really been able to find what I'm looking for searching the internet so I was wondering if any of you lovely folks might be able to guide me to a solution.

I have an excel file that contains lots of information but for my purposes here we can just simplify it to a list of product names, product numbers and a few columns later the inventory/number we have of the product.

Is it possible to have a macro that will automatically create an outlook task when the value dips below 50 for a product in the list? I fear that such a program would keep creating new tasks for the products until the value is again above 50. Do you have a clever way of circumventing the issue?

Or to go another more involved but still very helpful way... Is there a way that I could just highlight the row of the product and hit a button and have a task created using just the product name, product number and number in stock (excluding the extra columns)?


Thank you!!! Assume (and feel confident knowing) that I know nothing about writing macros!

[indiegoober]

I found this on another site (http://www.mrexcel.com/forum/excel-q...lications.html) and am wondering if there is a way to modify this to suit my purpose? I am not really sure how to implement this (don't know how to do binding but I think it might be a step in the right direction)

Default Re: Create Task in Outlook using excel vba

The below VBA was created by some input from a few people here. it creates a task in outlook.

every time you run it, it checks to ensure that it is not double posting an entry. I have it working quite nicely on a spreadsheet that I am developing for monitoring contracts for our organization.

At this point, it sets the review time as 12am, which works, but I want to have that at at different time. so working on that. but other than that it works well.

enjoy,


Sub CheckBinding()
Dim olApp As Outlook.Application
Set olApp = New Outlook.Application
MsgBox olApp.Name
End Sub
Sub CreateTask()
Dim olApp As New Outlook.Application
Dim olName As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olTasks As Outlook.Items
Dim olNewTask As Outlook.TaskItem
Dim strSubject As String
Dim strDate As String
Dim strBody As String
Dim reminderdate As String
Dim ws As Worksheet
Dim LR As Long
Dim i As Long
Set ws = Worksheets("sheet1") 'sheet where dates are
Set wg = Worksheets("sheet2") 'sheet where data is calculated
LR = ws.Range("D1").End(xlDown).Row 'get row for last cell in column D with value
Set olName = olApp.GetNamespace("MAPI")
Set olFolder = olName.GetDefaultFolder(olFolderTasks)
Set olTasks = olFolder.Items
For i = 2 To LR 'assuming the rows have headers, so loop starts on row 2
strSubject = ws.Range("D" & i) 'takes subject from column D
strDate = ws.Range("C" & i) 'takes date from column C
strBody = ws.Range("O" & i) 'takes text from column E and adds it as Body
reminderdate = wg.Range("D" & i) 'Takes date from column D and enters it as the reminder date
Set olNewTask = olTasks.Add(olTaskItem)
'delete task if it exists
'an error is generated if task doesn't exist
On Error Resume Next
olTasks.Item (strSubject)
If Err.Number = 0 Then
olTasks.Item(strSubject).Delete
End If
On Error GoTo 0
'create new task
With olNewTask
.Subject = strSubject
.Status = olTaskInProgress
.Importance = olImportanceNormal
.DueDate = DateValue(strDate)
.Body = strBody
.ReminderSet = True
.remindertime = reminderdate
.TotalWork = 40
.ActualWork = 20
.Save
End With
Next i
End Sub