There's a tl;dr at the bottom that might be easier to read than my paragraphs up here. Just a warning
I've got a list of rows with the following information:
Drafts ordered - Drafts Recieved - Forwarded the Draft
If I've only got a date under draft ordered, and not "Forwarded the Draft", then I'd like excel to make a task in outlook.
If I've got a date under all 3, I'd like it to use the date from "Forwarded the draft".
I'd also like it to keep a count on the number of "drafts" that i'm still waiting to receive, and those I've forwarded. If I've forwarded 4 drafts, I'd like excel to only make 1 task for all 4 done the same day. There will be cases where it takes longer to get a draft copy, so in the same spread sheet I'll have different forwarding dates
.
If I can get excel to make a seperate outlook task item for nonsimilar dates. That would be awesome.
The whole thing has been giving me a headache, and currently, I have a Do loop and a counter that goes row by row. I've made it so if cell.offset(counter+1, 0)=cell.offset(counter,0) then counter = counter + 1.
This avoids having more than 1 task item in Outlook. Problem is, if there's more than 1 row difference between "Drafts Forwarded", it doesn't recognize any other items.
This is a section of my code:
Do Until cell.Offset(counter, 5) = ""
If cell.Offset(counter, 0) = "" And cell.Offset(counter, 10) = "" Then
counter = counter + 1
ElseIf cell.Offset(counter - 1, 10) = cell.Offset(counter, 10) Or cell.Offset(counter - 1, 0) = cell.Offset(counter, 0) Then
counter = counter + 1
Else
If OLApp Is Nothing Then Set OLApp = CreateObject("Outlook.Application")
On Error GoTo 0
If Not OLApp Is Nothing Then
Set OLNS = OLApp.GetNamespace("MAPI")
OLNS.Logon
Set OLTask = OLApp.CreateItem(olTaskItem)
<use outlook code here>
If Not IsEmpty(cell.Offset(counter, 0)) Then
With OLTask
<create task>
End With
Else
With OLTask
<create task>
End If
End If
counter = counter + 1
End If
Loop
This code only creates tasks properly for one date. If I alter or add a new Draft Forwarded date, it does not create a task for that item.
This basically is how excel looks like:
DC Order Date (1st row) |
DC Receive Date (3rd row) |
DC Forward Date (10th row) |
date (may or may not have) |
date (may or may not have) |
date (may or may not have) |
date (may or may not have) |
date (may or may not have) |
date (may or may not have) |
... |
... |
... |
date (may or may not have) |
date (may or may not have) |
date (may or may not have) |
....
Can anyone help me out?
tl;dr: For all rows in columns
• If cell (n, 10) isn't populated, use cell (n, 1)'s date to make an outlook item.
• If cell (n, 10) is populated, use cell (n, 10)'s date.
• if cell (n, 10) or cell (n, 1) date = cell (n, 10) or cell (n, 1) add to count
• Try to count number of cell (n, 10)'s or cell (n, 1)'s with similar dates, create 1 task item for all similar dates.
• If Cell (n, 1) or Cell (n, 10) have unique dates compared to the rest, create separate task item for each unique item.
• if Cell (n, 3) has a value and Cell (n, 10) does not, do not make a task and do not count cell (n, 1) value.
Bookmarks