Voting Buttons & Excel

[Junoon]

Hi,

I have created Voting Buttons for people with 24 Time slots for the 24
hrs of the day...

e.g. 8:30 am; 9:30 am; 10:30 am etc....

When a person clicks on his appropriate choice, i would receive the
message in my Outlook Inbox folder & then from folder, the voted time
should automatically go into the B column on a worksheet named
"ChooseTime", & then A column would should show the sender's email ID
or name...

Col A Col B
Email ID Chosen Time
------------------------------------

How do i collect the votes....

N.B: The Subject of the email sent to all the person's will be
"ChooseTime", i.e the same as the Worksheet name..viz., "ChooseTime"...

PLEASE HELP ASAP!

 

[Eric Legault [MVP - Outlook]]

The easiest way to do this is to create a new View in your Inbox, remove
columns that you don't want, and add the VotingResponse field and any others
you want in the spreadsheet (you should probably create a filter too to
restrict any messages that don't have a VotingResponse value. Then select
all the messages and press CTRL+C. Then paste it into Excel.

 

[Junoon]

hi eric,

Thanks for your reply.

I want to automate the whole process, so that i dont have to open each
mail of 350 persons & then copy-paste into excel, for which email id(
person) has selected which time.

I am new to outlook & have code to :

1] Check if Outlook is open & running...if not, show message to inform
user & then quit function....
2] Select Inbox folder & then create a new folder for delivering voting
messages from persons...
3] An Excel sheet which has the VBA code for the above but,

What i want is VBA code to process the Voting emails in that folder &
extract them to Excel sheet starting at say A2...

Column Cells A1 to Z1 will have.....

Email
ID 8:30 9:30 10:30 11:30 12:30 13:30 14:30 15:30 16:30 17:30 18:30 19:30 20:30 21:30 22:30 23:30 0:30 1:30 2:30 3:30 4:30 5:30 6:30 7:30 8:30

Below them, i want to get the appropriate Yes /No response from the
Processed mails below the chosen time, along with the Email ID (Email
address) of the person who has sent the email....


PLEASE HELP!

Warm Regards,

 

[Eric Legault [MVP - Outlook]]

Actually, you don't have to open each e-mail individually. If you select the
voting messages in the folder, you can simply copy them much like you're
copying a series of rows in Excel. The rows and fields that you see will be
pasted exactly as you look into the spreadsheet. I recommend this approach,
as all you have to do is tailor your spreadsheet to accept the format/layout
of the View you're pasting from (which you can change, as I said in my last
post) and you don't need to write any code at all.

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/

hi eric,

Thanks for your reply.

I want to automate the whole process, so that i dont have to open each
mail of 350 persons & then copy-paste into excel, for which email id(
person) has selected which time.

I am new to outlook & have code to :

1] Check if Outlook is open & running...if not, show message to inform
user & then quit function....
2] Select Inbox folder & then create a new folder for delivering voting
messages from persons...
3] An Excel sheet which has the VBA code for the above but,

What i want is VBA code to process the Voting emails in that folder &
extract them to Excel sheet starting at say A2...

Column Cells A1 to Z1 will have.....

Email
ID 8:30 9:30 10:30 11:30 12:30 13:30 14:30 15:30 16:30 17:30 18:30 19:30 20:30 21:30 22:30 23:30 0:30 1:30 2:30 3:30 4:30 5:30 6:30 7:30 8:30

Below them, i want to get the appropriate Yes /No response from the
Processed mails below the chosen time, along with the Email ID (Email
address) of the person who has sent the email....


PLEASE HELP!

Warm Regards,

 

[Junoon]

Hi Eric,

Thanks for your reply, but i want to know if there is a way to code
this out as there will be different voting options on different
computers & the no of employees is more. the other computers would be
used by people who are not adept at all.

Just a click of button to get the voting data into my computer would
solve the problem.

If you could just give a simple example would really help.

I went thru the Outlook VBa help, & it shows that Mail Items
VotingResponse & VotingOptions, but donot know how to code that to
transfer voting data.

PLEASE HELP!

Warm Rgds,

 

[Eric Legault [MVP - Outlook]]

Okay, you're going to make me work for my supper eh?

Try the macro below. It will collect the votes for all e-mails in your
Inbox. Just change the path to the worksheet file, and ensure that the dates
are in the first row and that the cells are formatted as Text.

Sub CollectVotesToExcel()
Dim objNS As Outlook.NameSpace
Dim objInbox As Outlook.MAPIFolder
Dim objMail As Outlook.MailItem, objItem As Object

Dim objExcel As Excel.Application
Dim objWks As Excel.Worksheet, objWkb As Excel.Workbook
Dim objTimeRange As Excel.Range, objRange As Excel.Range

Dim intX As Integer

Set objNS = Application.GetNamespace("MAPI")
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

Set objExcel = New Excel.Application
objExcel.Workbooks.Open "C:\MyWorkbook.xls"
objExcel.Visible = True
Set objWks = objExcel.ActiveSheet 'Use default Sheet1
Set objTimeRange = objWks.UsedRange
For Each objItem In objInbox.Items
If objItem.Class = olMail Then
Set objMail = objItem
If objMail.VotingResponse <> "" Then
Set objRange = objTimeRange.Find(objMail.VotingResponse, , ,
xlWhole)
If Not objRange Is Nothing Then
objWks.Cells(2, objRange.Column).Value =
objMail.SenderEmailAddress
End If
End If
End If
Set objMail = Nothing
Set objItem = Nothing
Next

Set objRange = Nothing
Set objTimeRange = Nothing
Set objMail = Nothing
Set objWkb = Nothing
Set objWks = Nothing
Set objExcel = Nothing
Set objNS = Nothing
Set objInbox = Nothing
End Sub

 

[Junoon]

Hi Eric,

1] Just to let you know, i am using Excel 97 at office & so
objMail.SenderEmailAddress does not work.

2] Also, your code creates a new workbook alltogether which i donot
want. i am trying to run the code & get the results in the same
workbook.

3] I tried using objMail.SenderName, instead of
objMail.SenderEmailAddress. But, i get a Security Warning dialog to
Allow Outlook Access with a drop down list box to select time.

4] also, i have to put time in Quotes when creating a voting
mail....i.e. "0:00";0:30"; etc....only then it appears as buttons to
Employees, otherwise not?????

Anyway, just to let you know, the 1st column is the Email ID column
(A1) & the subsequent columns (starting B1) are for the times in text
(as you suggested).
Now , i saw that whenever someone sends a Voting response, i receive
his email with the Subject as the Voting Response.

1] Can i not compare objMail.SenderName with the person's name in say
Column A (Email ID), & if Found, then put a "Yes" on his respective
time chosen ,then Loop similarly through other emails & get other
responses for persons in Column A???

2] Also, If i send a Voting Email with the Time Buttons, Whose Body of
the Email contains information to be filled by employees like :

Body of the Email may contain say:
1. Drop Location ( Address): [Employee enters his address here]
2. Shift Time: [here employee will enter his shift time e.g. 7:30 -
16:30]

What i want to do is make a separate columns (for each one of the
above) on the Excel sheet, say the last columns....get the data entered
by Employees into the columns.
Say Last 2 columns would be :
Drop Location ( Address) | Shift Time |

Obviously, the Employee ( on receiving the Voting email from me) would
be clicking on Edit response, entering the body data & sending the
mail.....

I Hope i am clear & look forward to your valued response. Please help
ASAP a this would clear a lot of workload on my head regarding employee
transport issues, which is a mess right now...This idea is just what i
thought could solve the problem, but the employee count is more than i
can handle.....

PLEASE HELP ASAP

 

[Eric Legault [MVP - Outlook]]

First, you have two sets of numbered lists in your response, so I'll compose
my responses condensed into one set:

1) Right, you can only use the SenderEmailAddress propery with Outlook 2003.
For other methods, see:

To get the sender's address from a Microsoft Outlook mail message (code
samples):
http://www.outlookcode.com/d/code/getsenderaddy.htm

You can most certainly use the SenderName to lookup a value in a column and
insert values in the same row in other columns. Use Excel's Range object and
Cells collection, as well as the Find method I demonstrated, to do all this.

2) My code sample doesn't create a new workbook, it opens an existing one.

You can also parse the contents of the message body if you want, and insert
columns into the spreadsheet. You can do anything you want!

3) See: Microsoft Outlook "Object Model Guard" Security Issues for
Developers:
http://www.outlookcode.com/d/sec.htm

4) If quotes around dates work for Voting buttons, then continue to use it!

Good luck Junoon. A lot of what you want to do is possible, but will
require some effort and learning on your part to write the complete solution.
I can offer some guidance and answer any glaring technical issues, but the
code sample I already provided should give you a good starting point.

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/

Hi Eric,

1] Just to let you know, i am using Excel 97 at office & so
objMail.SenderEmailAddress does not work.

2] Also, your code creates a new workbook alltogether which i donot
want. i am trying to run the code & get the results in the same
workbook.

3] I tried using objMail.SenderName, instead of
objMail.SenderEmailAddress. But, i get a Security Warning dialog to
Allow Outlook Access with a drop down list box to select time.

4] also, i have to put time in Quotes when creating a voting
mail....i.e. "0:00";0:30"; etc....only then it appears as buttons to
Employees, otherwise not?????

Anyway, just to let you know, the 1st column is the Email ID column
(A1) & the subsequent columns (starting B1) are for the times in text
(as you suggested).
Now , i saw that whenever someone sends a Voting response, i receive
his email with the Subject as the Voting Response.

1] Can i not compare objMail.SenderName with the person's name in say
Column A (Email ID), & if Found, then put a "Yes" on his respective
time chosen ,then Loop similarly through other emails & get other
responses for persons in Column A???

2] Also, If i send a Voting Email with the Time Buttons, Whose Body of
the Email contains information to be filled by employees like :

Body of the Email may contain say:
1. Drop Location ( Address): [Employee enters his address here]
2. Shift Time: [here employee will enter his shift time e.g. 7:30 -
16:30]

What i want to do is make a separate columns (for each one of the
above) on the Excel sheet, say the last columns....get the data entered
by Employees into the columns.
Say Last 2 columns would be :
Drop Location ( Address) | Shift Time |

Obviously, the Employee ( on receiving the Voting email from me) would
be clicking on Edit response, entering the body data & sending the
mail.....

I Hope i am clear & look forward to your valued response. Please help
ASAP a this would clear a lot of workload on my head regarding employee
transport issues, which is a mess right now...This idea is just what i
thought could solve the problem, but the employee count is more than i
can handle.....

PLEASE HELP ASAP

 

[Junoon]

Thanks for your reply Eric.

1] I want to know is it possible to totally bypass the security box in
Outlook 97?

2] If i use FIND to search & compare the SenderName, would i still get
the security dialog. ....the reason i am asking is bcos it will slow
the process & i would have to click Yes for each email that is
processed.

3] is it possible to dump the processed mails to a automatically
created new folder, so that they cannot get processed again when i run
the code the next time? How to do it?

Warm Regards,

 

[Junoon]

Hi Eric,

This is What i have made....
************************************
Function CreateInboxFolder(oInbox, Fldr) As Object

Dim oFold As Object

'Look for archive folder and create if doesn't exist, create it
On Error Resume Next 'ignore error
Set oFold = oInbox.Folders(Fldr)
If Err.Number <> 0 Then Err.Clear

If oFold Is Nothing Then
Set oFold = oInbox.Folders.Add(Fldr, olFolderInbox)
End If

Set CreateInboxFolder = oFold

End Function
Function GetOutlook() As Object

Dim olApp As Object

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
MsgBox "Outlook is not running: please open the application first"
End If

Set GetOutlook = olApp

End Function

Sub CollectVotes()

Dim objNS As Outlook.NameSpace
Dim objInbox As Outlook.MAPIFolder
Dim objMail As Outlook.MailItem
Dim objItem As Object
Dim olApp As Outlook.Application

If olApp Is Nothing Then
Set olApp = GetOutlook()
End If


Dim objWks As Excel.Worksheet
Dim objTimeRange As Excel.Range, objRange As Excel.Range
Dim iRow
Dim FolderName As Object

On Error Resume Next
Set objNS = olApp.GetNamespace("MAPI")
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

Set objWks = ThisWorkbook.Worksheets(ActiveSheet.Name) 'Use default
Sheet1
With objWks
iRow = objWks.Cells(objWks.Rows.Count, 1).End(xlUp).Row + 1
End With

Set objTimeRange = objWks.UsedRange

For Each objItem In objInbox.Items
If objItem.Class = olMail Then
Set objMail = objItem
If objItem.VotingResponse <> "" Then
Set objRange =
objTimeRange.Find(objMail.VotingResponse, , , xlWhole)
If Not objRange Is Nothing Then
objWks.Cells(iRow, 1).Value =
objMail.SenderName
objWks.Cells(iRow, objRange.Column).Value = "Y"
objWks.Cells(iRow, 50).Value =
Trim(objMail.Body)
End If
Set FolderName = CreateInboxFolder(objInbox, "Votes" &
"-" & Date)
objMail.Move FolderName
iRow = iRow + 1
End If
End If
Next

Set objItem = Nothing
Set objRange = Nothing
Set objTimeRange = Nothing
Set objMail = Nothing
Set objWks = Nothing
Set objExcel = Nothing
Set objNS = Nothing
Set objInbox = Nothing
Set olApp = Nothing

End Sub
**********************************

But if you look at the above code, ......objWks.Cells(iRow, 50).Value =
Trim(objMail.Body) is "Location" Column, & i want to just get the
Location from the Body of the mail.

The Body of the Voting Mail would be having 3 entries each on a line:
Location:
EmpID:
Shift Time:

1] If i make extra Columns after last time column (23:30) for the Shift
time, for each of the above parameters, viz., Location, EmpID, Shift
Time......How do i extract their data from the Body of the
Messages......

2] Also, if instead i have a Column of names in the 1st column & i am
not dumping any sender Names using code, how do i compare the names in
1st column & just have a "Y" reflecting under their chosen
time......Comparison would be faster.....
I just have an idea, that i would be able to extract the data using
Compare or Instr function.......

Please give me an example as i am really stuck at the last moment......


PLEASE HELP!

Cheers!

 

[Eric Legault [MVP - Outlook]]

To do any parsing of the message body would require careful use of string
manipulations functions (InStr, Left, Right, Mid, etc.).

--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/

Hi Eric,

This is What i have made....
************************************
Function CreateInboxFolder(oInbox, Fldr) As Object

Dim oFold As Object

'Look for archive folder and create if doesn't exist, create it
On Error Resume Next 'ignore error
Set oFold = oInbox.Folders(Fldr)
If Err.Number <> 0 Then Err.Clear

If oFold Is Nothing Then
Set oFold = oInbox.Folders.Add(Fldr, olFolderInbox)
End If

Set CreateInboxFolder = oFold

End Function
Function GetOutlook() As Object

Dim olApp As Object

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
MsgBox "Outlook is not running: please open the application first"
End If

Set GetOutlook = olApp

End Function

Sub CollectVotes()

Dim objNS As Outlook.NameSpace
Dim objInbox As Outlook.MAPIFolder
Dim objMail As Outlook.MailItem
Dim objItem As Object
Dim olApp As Outlook.Application

If olApp Is Nothing Then
Set olApp = GetOutlook()
End If


Dim objWks As Excel.Worksheet
Dim objTimeRange As Excel.Range, objRange As Excel.Range
Dim iRow
Dim FolderName As Object

On Error Resume Next
Set objNS = olApp.GetNamespace("MAPI")
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

Set objWks = ThisWorkbook.Worksheets(ActiveSheet.Name) 'Use default
Sheet1
With objWks
iRow = objWks.Cells(objWks.Rows.Count, 1).End(xlUp).Row + 1
End With

Set objTimeRange = objWks.UsedRange

For Each objItem In objInbox.Items
If objItem.Class = olMail Then
Set objMail = objItem
If objItem.VotingResponse <> "" Then
Set objRange =
objTimeRange.Find(objMail.VotingResponse, , , xlWhole)
If Not objRange Is Nothing Then
objWks.Cells(iRow, 1).Value =
objMail.SenderName
objWks.Cells(iRow, objRange.Column).Value = "Y"
objWks.Cells(iRow, 50).Value =
Trim(objMail.Body)
End If
Set FolderName = CreateInboxFolder(objInbox, "Votes" &
"-" & Date)
objMail.Move FolderName
iRow = iRow + 1
End If
End If
Next

Set objItem = Nothing
Set objRange = Nothing
Set objTimeRange = Nothing
Set objMail = Nothing
Set objWks = Nothing
Set objExcel = Nothing
Set objNS = Nothing
Set objInbox = Nothing
Set olApp = Nothing

End Sub
**********************************

But if you look at the above code, ......objWks.Cells(iRow, 50).Value =
Trim(objMail.Body) is "Location" Column, & i want to just get the
Location from the Body of the mail.

The Body of the Voting Mail would be having 3 entries each on a line:
Location:
EmpID:
Shift Time:

1] If i make extra Columns after last time column (23:30) for the Shift
time, for each of the above parameters, viz., Location, EmpID, Shift
Time......How do i extract their data from the Body of the
Messages......

2] Also, if instead i have a Column of names in the 1st column & i am
not dumping any sender Names using code, how do i compare the names in
1st column & just have a "Y" reflecting under their chosen
time......Comparison would be faster.....
I just have an idea, that i would be able to extract the data using
Compare or Instr function.......

Please give me an example as i am really stuck at the last moment......


PLEASE HELP!

Cheers!