Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Paste Excel into Outlook | Excel Discussion (Misc queries) | |||
Excel to Outlook Calendar Macro | Excel Discussion (Misc queries) | |||
macro tts from excel to outlook | Excel Programming | |||
Macro from excel to outlook for tts | Excel Programming | |||
Getting rid of Outlook warnings from excel macro | Excel Programming |