Solved

Notification to the Attendee

Posted on 2014-10-28
25
150 Views
Last Modified: 2014-11-15
Hello,

I am importing tasks into Outlook from Excel
I have many tasks and do not have any attendee (I assume that is the correct name of the field)

How do I set the Attendee for many tasks at once?  This attendee would need to receive the reminder X days from the due date of the task.  

I have done quite a bit of research and can not figure this out.  There must be a way to set this for many tasks at once.

thank you
0
Comment
Question by:pdvsa
  • 15
  • 10
25 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, pdvsa.

Only meetings have attendees.  Tasks would would be assigned to someone (i.e. assignees).  Is that what you want, to assign a group of tasks to the same person all at once?
0
 

Author Comment

by:pdvsa
Comment Utility
Yes, that is what I need.  It really doesn't matter to me I guess if it's a meeting or a task.  I don't know how I can tell the difference quite frankly but I don't know OL that well either.  

Let me know what is the next step when you get a sec.  Thank you
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Appointments go on an Outlook calendar.  Tasks go in the task list.  They are separate and distinct items.  I'll need to know which it is since the solution differs from one to the other.
0
 

Author Comment

by:pdvsa
Comment Utility
Hi and thanks again for the response.  It is definitely Calendar entries.  I guess they are properly referenced as Appointments then.  

I have attached a screen print.  I believe the "Required Attendees" is what I need to populate unless there is a more appropriate field.   On the import from Excel, i have columns mapped to Required Attendee and Optional Attendee (as email addresses) but there still is no entry in the Required Attendee as shown in the screen pic.  

Let me know what is next.  thank you

Required Attendee Blank
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You didn't mention the version of Outlook you're using, but I'm guessing it's either 2007 or 2010.  If so, then you've run into a known problem.  See this Microsoft KB article for details.  The only solution I know of is to use a script to import the items instead of Outlook's built in import process.  I can put such a script together if you'd like to go that route.  To do that, I'll need to do know the names of all the columns in your spreadsheet.
0
 

Author Comment

by:pdvsa
Comment Utility
Oh geez.  I have touched on something that Microsoft has a known issue with.  Thank you for that very informative article.  

It does say it can import if was in an .ics format.  Do you know if I can simply save the excel file into a .ics format?  I believe I checked the save as types and .ics is not a save as type.

I will let you know the names of the columns.  There are about 8.  When I get to the office I will send your way.

Thank you once again for the help.
0
 

Author Comment

by:pdvsa
Comment Utility
Would you Happen to know it share point has this same issue?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
No, Excel does not have a built-in way of saving in .ics (i.e. internet calendar sharing) format.  If the data in Excel is coming from some other system, then you could check to see if it can save in .ics format rather than .csv.  

I'm a relative novice at SharePoint and can't answer the question.  I don't even know if SharePoint has an facility for importing appointments to a calendar.
0
 

Author Comment

by:pdvsa
Comment Utility
Okay thank you sir. I will get back to you soon with the names of the columns
0
 

Author Comment

by:pdvsa
Comment Utility
I think required attendee will be the field that will be populated with an email address and is the one to receive the notifications.
here are the columns:
Required Attendee, Categories,      Reminder Days,      Reminder Date,      Reminder ON/Off, Reminder Time,      Start Date, End Date,      End Time      Start Time, All Day Event, Priority, Show time as, Description, Location, Timing, Subject,
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Are Start and End each actually broken into two columns: one for the date and another for the time?  That would be unusual and will make this more complicated.  The same for the reminder columns..  In Outlook a reminder is composed of two properties: the number of minutes before the appointment starts to display the reminder and the reminder being turned on or off.  There's no such thing as "reminder days", "reminder date". or "reminder time".  Outlook appointments don't have a "timing" property either.  What sort of values are in the "all day event", "reminder on/off", "priority", and "show time as" columns?  I'll need to know that in order to translate what you see in the spreadsheet into a value Outlook understands.
0
 

Author Comment

by:pdvsa
Comment Utility
Hi, sorry for my tardy reply.  I must have missed the notification.

<Are Start and End each actually broken into two columns: one for the date and another for the time?  That would be <unusual and will make this more complicated.  T
==>I do not need to have the time.  The date is all I really need.

<There's no such thing as "reminder days", "reminder date". or "reminder time".
OK.   I wont need them.  

 <What sort of values are in the "all day event", "reminder on/off", "priority", and "show time as" columns?
All Day Event is "No" (I am not sure if this field is important because it might default to No if a start and end date are the same.  Reminder on/Off is True.  Priority is "High" and Show Time as is "Available"

Ok let me know if you have more questions.   thank you for the help.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:pdvsa
Comment Utility
just checking in....I seem to not get all my notices by email for when someone answers.  

thank you
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
pdvsa,

Here's my first cut at this.  Please try it out and let me know if it works the way you want it to.

Follow these instructions to add the script to your computer.

1.  Open Notepad
2.  Copy the code and paste it into Notepad
3.  Save the file.  You may name it anything you want so long as the file extension is .vbs
4.  Move the file to your Send To folder

To use the script

1.  Right-click on any properly formatted Excel spreadsheet and select "Send To"
2.  Select this script

When it finishes, the script will tell you how many items it imported to the calendar.

Const olAppointmentItem = 1

Const olImportanceLow = 0
Const olImportanceNormal = 1
Const olImportanceHigh = 2

Const olFree = 0
Const olTentative = 1
Const olBusy = 2
Const olOutOfOffice = 3
Const olWorkingElsewhere = 4

Const olRequired = 1
Const olOptional = 2

Const olMeeting = 1

Dim excApp, excWkb, excWks
Dim olkApp, olkSes, olkApt, olkRec
Dim lngRow, lngCol, lngCnt
Dim datStartDate, datStartTime, datEndDate, datEndTime, bolAllDay 
Dim arrTmp, varTmp, strFil

strFil = WScript.Arguments(0)

Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Open(strFil)
Set excWks = excWkb.Worksheets.Item(1)

For lngRow = 2 To excWks.usedrange.rows.count
	datStartDate = ""
	datStartTime = ""
	datEndDate = ""
	datEndTime = ""
	bolAllDay = False
	Set olkApt = olkApp.CreateItem(olAppointmentItem)
	olkApt.MeetingStatus = olMeeting
	For lngCol = 1 To excWks.usedrange.columns.count
		Select Case LCase(excWks.cells(1,lngCol).value)
			Case "required attendee"
				arrTmp = Split(excWks.cells(lngRow,lngCol).value, ";")
				For Each varTmp In arrTmp
					Set olkRec = olkApt.Recipients.Add(varTmp)
					olkRec.Type = olRequired
				Next
				Set olkRec = Nothing
			Case "optional attendee"
				arrTmp = Split(excWks.cells(lngRow,lngCol).value, ";")
				For Each varTmp In arrTmp
					Set olkRec = olkApt.Recipients.Add(varTmp)
					olkRec.Type = olOptional
				Next
				Set olkRec = Nothing
			Case "categories"
				olkApt.Categories = excWks.cells(lngRow,lngCol).value
			Case "reminder days"
				'Outlook appointments do not have a property for this
			Case "reminder date"
				'Outlook appointments do not have a property for this
			Case "reminder on/off"
				If LCase(excWks.cells(lngRow,lngCol).value) = "true" Then
					olkApt.ReminderSet = True
				Else
					olkApt.ReminderSet = False
				End If
			Case "reminder time"
				'Outlook appointments do not have a property for this
			case "start date"
				datStartDate = excWks.Cells(lngRow,lngCol).value
			Case "start time"
				datStartTime = excWks.Cells(lngRow,lngCol).value
			Case "end date"
				datEndDate = excWks.Cells(lngRow,lngCol).value
			Case "end time"
				datEndTime = excWks.Cells(lngRow,lngCol).value
			Case "all day event"
				If LCase(excWks.cells(lngRow,lngCol).value) = "yes" Then
					bolAllDay = True
				End If			
			Case "priority"
				Select Case LCase(excWks.Cells(lngRow,lngCol).value)
					Case "high"
						olkApt.Importance = olImportanceHigh
					Case "low"
						olkApt.Importance = olImportanceLow
					Case "normal"
						olkApt.Importance = olImportanceNormal
				End Select
			Case "show time as"
				Select Case LCase(excWks.Cells(lngRow,lngCol).value)
					Case "free"
						olkApt.BusyStatus = olFree
					Case "tentative"
						olkApt.BusyStatus = olTentative
					Case "busy"
						olkApt.BusyStatus = olBusy
					Case "out of office"
						olkApt.BusyStatus = olOutOfOffice
					Case "working elsewhere"
						olkApt.BusyStatus = olWorkingElsewhere
				End Select
			Case "description"
				olkApt.body = excWks.cells(lngRow,lngCol).value
			Case "location"
				olkApt.Location = excWks.cells(lngRow,lngCol).value
			Case "timing"
				'Outlook appointments do not have a property for this
			Case "subject"
				olkApt.subject = excWks.cells(lngRow,lngCol).value
		End Select
	Next
	If IsDate(datStartDate) And IsDate(datStartTime) Then
		olkApt.Start = datStartDate & " " & datStartTime
	Else
		olkApt.Start = datStartDate
	End if
	If IsDate(datEndDate) And IsDate(datEndTime) Then
		olkApt.End = datEndDate & " " & datEndTime
	Else
		olkApt.End = datEndDate
	End If
	olkApt.AllDayEvent = bolAllDay
	olkApt.Recipients.ResolveAll
	olkApt.save
	lngCnt = lngCnt + 1
Next

excWkb.Close False
Set excWks = Nothing
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing

olkSes.Logoff
Set olkApt = Nothing
Set olkSes = Nothing
Set olkApp = Nothing

MsgBox "Imported " & lngCnt & " items to the calendar.", vbInformation+vbOKOnly,"Import Appointments"
WScript.Quit

Open in new window

0
 

Author Comment

by:pdvsa
Comment Utility
wow that looks amazing.

I have a question on #4.  How to make the .vbs script to the Send To folder?  I dont know how  to make it one of the options to send the Excel file to.  I searched online and couldnt find how to do this. Would appreciate your assistance.

thank you
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Just copy the script file to your "Send To" folder.  That's a Windows folder.  The path will be something like

c:\Users\<username>\AppData\Roaming\Microsoft\Windows\SendTo

where <username> is your username.
0
 

Author Comment

by:pdvsa
Comment Utility
Hi, I sent the excel file to outlook through the Send To as instructed.  I did get an error on row 119 char 3 (see attached).  I copied the script to excel and row 119 = olkApt.Start = datStartDate (I guess this is correct...never done it before)

Maybe it is the start date?  I am importing only 2 rows of data to test and the start dates are populated with dates.  
I have other columns in the file that are not being mapped and I guess that does not create an issue.  

also, i think the file gets locked down possibly because it opens the file but doesnt release it.  Its not that important though.  

Let me know what you think....

error
0
 

Author Comment

by:pdvsa
Comment Utility
hello there...just checking in.  If you have an update please let me know.  Appreciate the help...
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Sorry, I don't remember getting an update notice on your last post.  

The error suggests that the solution either didn't find a start date or the start date it found isn't a valid date.  Are you sure the spreadsheet contains a column with a column header of "Start Date"?  If it does, then are the dates in the column valid dates?
0
 

Author Comment

by:pdvsa
Comment Utility
Hi, thank you for getting back time .  I am going to have to check in the morning. It was a long day and I don't have my computer right now. Talk to you tomorrow
0
 

Author Comment

by:pdvsa
Comment Utility
Greetings sir...

OK I have looked at the file and I have verified I have a Start Date field.  I attempted another import with the script and unfortunately I get the same error.  Line 113 as shown in the previous pic above.  

Maybe you can have a peek at the file.  You might see something there I can not.  

thank you
EE-Export-to-OL-script.xls
0
 

Author Comment

by:pdvsa
Comment Utility
oh wait.  It is a different error now.  Line 113.  It is "Subject".  

I might have fixed the LIne 113 "Subject" error but now when I try to export by script it now gives me the same initial error of Start Date line 119.  I assume it is Start Date as I copied the script to Excel and line 119 is Start Date.

Let me know what you think...

please see new file attached
EE-Export-to-OL-script.xls
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
I looked over the spreadsheet and found the problem.  There are actually several problems.  First, I'd set the code to open the first sheet in the workbook because I was only expecting there to be one sheet.  There are actually three sheets and the sheet you want to import from is the second sheet.  I've adjusted the code accordingly.  Next, there were 200+ blank, unused rows in the spreadsheet.  The code uses Excel's built-in UsedRange command to find the last used row in the spreadsheet.  Having blank, unused rows below the actual data in the sheet is a problem.  I deleted all of those rows in the spreadsheet you provided.  You need to do the same at your end.  To do that, select the rows, then right click on the row column and select "Delete".  One of the entries has a start date of 11/12/14 and and end date of 11/5/14.  The end date must be equal to or later than the start date.  Neither of the entries have a start time.  Outlook defaults to a start time of 12:00am when there's no specified start time.  Unless you want the appointments to all start at midnight, then you need to specify a start time.  The text in the Location column doesn't make sense in an appointment.  

Here's the revised code.  

Const olAppointmentItem = 1

Const olImportanceLow = 0
Const olImportanceNormal = 1
Const olImportanceHigh = 2

Const olFree = 0
Const olTentative = 1
Const olBusy = 2
Const olOutOfOffice = 3
Const olWorkingElsewhere = 4

Const olRequired = 1
Const olOptional = 2

Const olMeeting = 1

Dim excApp, excWkb, excWks, excCel
Dim olkApp, olkSes, olkApt, olkRec
Dim lngRow, lngCol, lngCnt
Dim datStartDate, datStartTime, datEndDate, datEndTime, bolAllDay, datStart, datEnd
Dim arrTmp, varTmp, strFil

strFil = WScript.Arguments(0)

Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Open(strFil)
Set excWks = excWkb.Worksheets("Export to Outlook")
For lngRow = 2 To excWks.UsedRange.rows.Count
    datStartDate = ""
    datStartTime = ""
    datEndDate = ""
    datEndTime = ""
    bolAllDay = False
    Set olkApt = olkApp.CreateItem(olAppointmentItem)
    olkApt.MeetingStatus = olMeeting
    For lngCol = 1 To excWks.UsedRange.Columns.Count
        Select Case LCase(excWks.Cells(1, lngCol).Value)
            Case "required attendee"
                arrTmp = Split(excWks.Cells(lngRow, lngCol).Value, ";")
                For Each varTmp In arrTmp
                    Set olkRec = olkApt.Recipients.Add(varTmp)
                    olkRec.Type = olRequired
                Next
                Set olkRec = Nothing
            Case "optional attendee"
                arrTmp = Split(excWks.Cells(lngRow, lngCol).Value, ";")
                For Each varTmp In arrTmp
                    Set olkRec = olkApt.Recipients.Add(varTmp)
                    olkRec.Type = olOptional
                Next
                Set olkRec = Nothing
            Case "categories"
                olkApt.Categories = excWks.Cells(lngRow, lngCol).Value
            Case "reminder days"
                'Outlook appointments do not have a property for this
            Case "reminder date"
                'Outlook appointments do not have a property for this
            Case "reminder on/off"
                If LCase(excWks.Cells(lngRow, lngCol).Value) = "true" Then
                    olkApt.ReminderSet = True
                Else
                    olkApt.ReminderSet = False
                End If
            Case "reminder time"
                'Outlook appointments do not have a property for this
            Case "start date"
                datStartDate = excWks.Cells(lngRow, lngCol).Value
            Case "start time"
                datStartTime = excWks.Cells(lngRow, lngCol).Value
            Case "end date"
                datEndDate = excWks.Cells(lngRow, lngCol).Value
            Case "end time"
                datEndTime = excWks.Cells(lngRow, lngCol).Value
            Case "all day event"
                If LCase(excWks.Cells(lngRow, lngCol).Value) = "yes" Then
                    bolAllDay = True
                End If
            Case "priority"
                Select Case LCase(excWks.Cells(lngRow, lngCol).Value)
                    Case "high"
                        olkApt.Importance = olImportanceHigh
                    Case "low"
                        olkApt.Importance = olImportanceLow
                    Case "normal"
                        olkApt.Importance = olImportanceNormal
                End Select
            Case "show time as"
                Select Case LCase(excWks.Cells(lngRow, lngCol).Value)
                    Case "free", "available"
                        olkApt.BusyStatus = olFree
                    Case "tentative"
                        olkApt.BusyStatus = olTentative
                    Case "busy"
                        olkApt.BusyStatus = olBusy
                    Case "out of office"
                        olkApt.BusyStatus = olOutOfOffice
                    Case "working elsewhere"
                        olkApt.BusyStatus = olWorkingElsewhere
                End Select
            Case "description"
                olkApt.Body = excWks.Cells(lngRow, lngCol).Value
            Case "location"
                olkApt.Location = excWks.Cells(lngRow, lngCol).Value
            Case "timing"
                'Outlook appointments do not have a property for this
            Case "subject"
                olkApt.Subject = excWks.Cells(lngRow, lngCol).Value
        End Select
    Next
    If IsDate(datStartDate) And IsNumeric(datStartTime) Then
        datStart = CDate(datStartDate & " " & FormatDateTime(datStartTime, vbLongTime))
    Else
        datStart = CDate(datStartDate)
    End If
    If IsDate(datEndDate) And IsNumeric(datEndTime) Then
        datEnd = CDate(datEndDate & " " & FormatDateTime(datEndTime, vbLongTime))
    Else
        datEnd = CDate(datEndDate)
    End If
    olkApt.Start = datStart
    olkApt.End = datEnd
    olkApt.AllDayEvent = bolAllDay
    olkApt.Recipients.ResolveAll
    olkApt.Save
    lngCnt = lngCnt + 1
Next

excWkb.Close False
Set excWks = Nothing
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing

olkSes.Logoff
Set olkApt = Nothing
Set olkSes = Nothing
Set olkApp = Nothing

MsgBox "Imported " & lngCnt & " items to the calendar.", vbInformation + vbOKOnly, "Import Appointments"
WScript.Quit

Open in new window

0
 

Author Closing Comment

by:pdvsa
Comment Utility
wow that worked.  Thank you so much for your help and sticking with me.

You obviously have a lot of knowledge.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.  I'm happy I was able to help out.
0

Featured Post

Want to promote your upcoming event?

Is your company attending an event or exhibiting at a trade show soon? Are you speaking at a conference? Spread the word by using a promotional banner in your email signature. This will ensure your organization’s most important contacts are in the know.

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
how to add IIS SMTP to handle application/Scanner relays into office 365.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now