Macro for Excel / Outlook
 

Hi,

I am a very new user of VBA but I am trying to do the following:

I am try to develop a macro which will generate task items wit
reminders for each of the records held on an excel file. ie I need t
undertake specific tasks on a regular frequency so would like outloo
to remind me.

The records contain a location (column A) which should form th
subject, a date (col B) and a frequency (col C) which added togethe
will form the reminder date for the task. There are over 100 records s
I am looking to produce a macro which will produce the equivalent task
(all with the same catergory in outlook "Inspections").

A second macro will then do the same thing but only for highlighte
records.

Anyone have any suggestions ? Is this a complex macro ? :

--
Message posted from http://www.ExcelForum.com

Macro for Excel / Outlook
 

Cromorin

Fairly complex, but not to worry. I can walk you through it and once you're
done it will seem easy. Start here

http://www.dicks-clicks.com/excel/olTask.htm

That will give you the basics of how to create a task in Outlook. You'll
need to loop through all the cells that you want to make into a task. It
will look something like this

Dim cell As Range
Dim olRec as Outlook.RecurrancePattern

For Each cell in Range("A1",Range("A1").End(xlDown)).Cells
With olTask
.Subject = cell.Value
.DueDate = cell.Offset(0,1).Value
Set olRec = .GetRecurrancePattern
etc...
End With
Next cell

See what you can come up with. Then, if you want, send me your workbook and
I'll help you write the macro.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Cormorin " wrote in message
...
Hi,

I am a very new user of VBA but I am trying to do the following:

I am try to develop a macro which will generate task items with
reminders for each of the records held on an excel file. ie I need to
undertake specific tasks on a regular frequency so would like outlook
to remind me.

The records contain a location (column A) which should form the
subject, a date (col B) and a frequency (col C) which added together
will form the reminder date for the task. There are over 100 records so
I am looking to produce a macro which will produce the equivalent tasks
(all with the same catergory in outlook "Inspections").

A second macro will then do the same thing but only for highlighted
records.

Anyone have any suggestions ? Is this a complex macro ? :(


---
Message posted from http://www.ExcelForum.com/

Macro for Excel / Outlook
 

HI

I have my first attempt see below:

Sub SidewearTask()

Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim cell As Range
'Dim olRec As Outlook.RecurrancePattern

Set olApp = New Outlook.Application
Set olTsk = olApp.CreateItem(olTaskItem)

With olTsk
.Body = Range("B6") & " " & Range("C6") & " " & Range("M6") &
" & Range("E6") & "M" & Range("F6") & "CH" & " " & Range("G6") & "M"
Range("H6") & "CH"
.Subject = "Sidewear Inspection"
.DueDate = Range("K6")
'Set olRec = Range("J6")
'.Set olRec = .GetRecurrancePattern
.Save
End With

Set olTsk = Nothing
Set olApp = Nothing

End Sub


Two main issues:
1) Not sure how to set the recurrance as I do not understand the
olrec line. Also the column with the recurrance (col. J) is as follow
24M for 2yearly 12M for yearly etc..

2) If I have to create the above for each record it will take nearly a
long as creating all the tasks manually. Is there a way to do it in
loop to same repaet until record in column B is blank ie the end of th
work sheet ?

Thanks

Mike

Oh and a third

3) the subject box should read the same as the tab (sheet name

--
Message posted from http://www.ExcelForum.com

Macro for Excel / Outlook
 

Cormorin

See comments in line

Sub SidewearTask()

Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim cell As Range
'Dim olRec As Outlook.RecurrancePattern

Set olApp = New Outlook.Application
Set olTsk = olApp.CreateItem(olTaskItem)

With olTsk
Body = Range("B6") & " " & Range("C6") & " " & Range("M6") & "
" & Range("E6") & "M" & Range("F6") & "CH" & " " & Range("G6") & "M" &
Range("H6") & "CH"
Subject = "Sidewear Inspection"
DueDate = Range("K6")
'Set olRec = Range("J6")
'.Set olRec = .GetRecurrancePattern
Save
End With

Set olTsk = Nothing
Set olApp = Nothing

End Sub


About how many rows are there? I'm trying to understand what you have and
what you want. Do you want to make a task for every row on a sheet? Does
each task recur, or is the whole sheet one task and each row shows when the
one task recurs?


Two main issues:
1) Not sure how to set the recurrance as I do not understand the .
olrec line. Also the column with the recurrance (col. J) is as follows
24M for 2yearly 12M for yearly etc..

If you want a task to recur, you use GetRecurrancePattern to create a new,
empty recurrance pattern object. Then you can work with olRec to set it up
the way you want

With olTsk
Set olRec = .GetRecurrancePattern
With olRec
.RecurranceType = olRecursMonthly
.Duration = 1
End With
End With

This sets the task to recur on the same day every 1 months. If the DueDate
was today, it would recur on the 6th day of every month.

Post a small sample of what kind of data is in your columns and how that
data relates to how often the task should recur.


2) If I have to create the above for each record it will take nearly as
long as creating all the tasks manually. Is there a way to do it in a
loop to same repaet until record in column B is blank ie the end of the
work sheet ?

Everything you have so far and anything you add can go in a loop. I would
use a For Each loop to loop through the cells in column B. Then use the
Offset property to get to the column you want to use.

Dim Rng As Range
Dim cell As Range

Set Rng = Sheet1.Range("B6",Sheet1.Range("B6").End(xlDown))

For Each Cell in Rng.Cells
Set olTsk = olApp.CreateItem(olTaskItem)
With olTsk
'Set task properties
.Body = cell.Value & " " & cell.Offset(0,1).Value & " " &
cell.Offset(0,11).Value
.Subject = cell.Parent.Name
'etc...
End With
Next Cell

The Set Rng line uses the End property to determine the range. It would be
like selecting B6 and using Shift+End+Down arrow to extend the range to the
next blank line.


3) the subject box should read the same as the tab (sheet name)

This is why asked if each row was a separate task or the whole sheet is one
task. If the the former, do you want all the tasks to have the same
subject? In the above code fragment, I used cell.Parent.Name to get the
name of the sheet. Once we clear up what each row on the sheet represents
and how the data in each row relates to the recurrance pattern, it should be
straight forward as to how to do this automatically, that is, so the macro
is considerably easier than doing it manually.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com