Creating a Survey in Outlook for Compilation in Excel

[loosenut]

Hi,

Not sure if this is possible without Herculean effort, but:

I have a survey I'd like to send out in Outlook asking people to geuss who the next president will be. Is it possible to send this out and when people reply it automatically adds to an excel sheet their email address, their geuss and the date on which the geuss was received?

Thanks!

Loosenut

 

[Joe Was]

Yes, but the code will depend on what you mean by "Send Out!"

 

[loosenut]

Hi Joe,
Thanks for your query. Ideally the questionaire would be directly in the text of the email itself so people don't have to open any attachments. But totally open to suggestions so if there is another way that would make it easier that would be fine too.

Thanks!

Loosenut

 

[Joe Was]

That is what I was afraid of, E-Mail is the most difficult way to do it. If it was on a network server or the internet it would be less difficult. I any case all take tweaks that must be done by someone with advanced code skills. You can search this board, I and others have answerd shared workbook or web querries questions before and have supplyed the code. These posts may get you started.


This is some code that works with Outlook.

Option Explicit

Sub SendRange()

'Sends a specified range in an Outlook message and retains Excel formatting

'Code written by Daniel Klann 2002

'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime


'Dimension variables
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String


'Select the range to be sent
On Error Resume Next
Set rngeSend = Application.InputBox("Please select range you wish to send.", , , , , , , 8)
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0

'Now create the HTML file
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:tempsht.htm", rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True


'Create an instance of Outlook (or use existing instance if it already exists
Set olApp = CreateObject("Outlook.Application")

'Create a mail item
Set olMail = olApp.CreateItem(olMailItem)

'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile("C:tempsht.htm", ForReading)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = TStream.ReadAll

olMail.HTMLBody = strHTMLBody

olMail.Display
End Sub

 

[loosenut]

Hmmmm..... Looks like too much horsepower for loosenuts capabilities at the moment. Cut and paste will be the interim solution (I'm not a coder) but I appreciate the suggestion.

Thanks,

Loosenut