Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Is there a way to send a mail to some users and what they update in the outlook has to be automatically be updated to the common excel .

Hi,

Is there a way to send a mail to some users and what they update in the outlook has to be automatically be updated to the common excel .
Say.I want to collect some common info from some users.when they enter the data and send the mail it has to reach my inbox and update the

excel sheet.

regards
Sharath

0
bsharath
Asked:
bsharath
  • 21
  • 14
1 Solution
 
William ElliottSr Tech GuruCommented:
there has to be an easier way to do this.

maybe send a lik to the users and have the link conencted to a share on a server that runs a script and their response can update the excel sheet, or write to a text file, then import the text files into excel.

but to answer your question, sure anythign is possible. you can create a custom form to send them within outlook Tools- forms
0
 
bsharathAuthor Commented:
The script part looks easier.Any help on this.

The forms part is also ok if you can give me more details
0
 
William ElliottSr Tech GuruCommented:
either option requires some coding knowledge
this would be easier to give you an answer if i had more details.

what are you asking and what results do you expect?
does the results have to go to excel, can it goto a database?
do you have a local intranet? can you setup asp pages? (this would make the 'prettiest' option)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bsharathAuthor Commented:
Once i receive a mail it has to be uploaded to a excel on the local machine.
0
 
William ElliottSr Tech GuruCommented:
i understand that part,.
here is a script that waits for a new message with "a particular subject" within a folder 'myfolder' under the inbox. it saves the attachment and...

you can then add a script to make the attachmetn do things.. but i still don't quite understand what it is you want it to do.

need specifics.
meaning what are you asking for. do you want their name? i can make a script to do that...


Sub DoStuff()
strsubject = "A Particular subject"
strmyfolder = "my folder"
' Declare variables
    Dim ns As NameSpace
    Dim Inbox As MAPIFolder
    Dim Item As Object
    Dim Atmt As Attachment
    Dim File As String
    Dim i As Integer
    Set ns = GetNamespace("MAPI")
    Set Inbox = ns.GetDefaultFolder(olFolderInbox)
    Set myfolder = Inbox.Folders(strmyfolder) 'if you want a subfolder of inbox
    i = 0
' Check Inbox for messages and exit of none found
    If myfolder.Items.Count = 0 Then
        MsgBox "There are no messages in the Inbox.", vbInformation, _
               "Nothing Found"
        Exit Sub
    End If
' Check each message for attachments
    For Each Item In myfolder.Items 'change to inbox.items to look in inbox
        strresult = strresult & Item & vbCrLf 'item = subject
        If Item = strsubject Then
            For Each Atmt In Item.Attachments
                'save the file to a temp location
                    File = "C:\windows\temp\" & Atmt.FileName
                    Atmt.SaveAsFile File
                    i = i + 1
                    '
                    ' here is where you add
                    ' your attachment manipulation
                    ' codes and whatnot
                    '
                    Next Atmt
        Else
        End If
    Next Item
    MsgBox strresult 'if you want to see a popup of the names of the attachments
    End Sub
0
 
bsharathAuthor Commented:
replies received back with a particular subject has to be updatde to the excel
0
 
William ElliottSr Tech GuruCommented:
is there a particular format they have to be putinto excel? particular rows?particular columns? particular format? what is the name of the excel file? where is it located? is the data a text or a number?
0
 
bsharathAuthor Commented:
The file name is Contact.xls
It is in C:\Contact.xls
I am using this to update user details
The headers are like this.

Name  Age  Title  Team Name   manager Name   and many more.


0
 
William ElliottSr Tech GuruCommented:
i think this will serve you better
http://office.microsoft.com/en-us/outlook/HA012106101033.aspx

create a custom outlok contact form with all the details you want in it

this should be beneficial as well
http://support.microsoft.com/?id=325389

and here
http://www.outlookcode.com/article.aspx?id=21

i could create a script to do this, but the option of creating the form will work as a more permanent solution
0
 
William ElliottSr Tech GuruCommented:
also once they have filled out the form and sent it back to you it will be held within your contacts and you can export it to excel
0
 
William ElliottSr Tech GuruCommented:
but incase you want the other option you can create a share on your server that everyone has write access to,... then send them a link to this script

strwritefile = rnd & "_results.txt"

Dim questions(10)
questions(0) = "What is your Name?"
questions(1) = "What is your Age?"
questions(2) = "What is your Title?"
questions(3) = "What is your Team Name?"
questions(4) = "What is your Manager's Name?"
questions(5) = "What is your Many?"
questions(6) = "What is your Many?"
questions(7) = "What is your More?"
questions(8) = "What is your this?"
questions(9) = "What is your that?"
questions(10) = "What is your whatever?"

Set fso = CreateObject("Scripting.FileSystemObject")
Set wl = fso.CreateTextFile(strwritefile, True)
i = 0
for each Question in questions
      Answer = inputbox(questions(i))
      wl.writeline i & vbtab & answer
i = i + 1
answer = ""
next



0
 
bsharathAuthor Commented:
Is this a vbs script
What are the changes i need to do in the script.
Where should in mention the excel path
0
 
William ElliottSr Tech GuruCommented:
after they have submitted the information, you can then run this script to compile all the information into excel

'On Error Resume Next
Option Explicit

Dim sfilename, tree, fso, fsor, TristateFalse, ary, res, yres, objfolder, strdirectory

strxlloc = ".\Contact.xls"
strdirectory = ".\compiled"

Set fso = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(strDirectory) Then
   Set objFolder = FSO.GetFolder(strDirectory)
Else
   Set objFolder = FSO.CreateFolder(strDirectory)
End If


dim strxlloc, objExcel, i, x

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Displayalerts = false

   objExcel.Cells( 1, 1) = "Name"
   objExcel.Cells( 1, 2) = "Title"
   objExcel.Cells( 1, 3) = "Team Name"
   objExcel.Cells( 1, 4) = "Manager Name"
   objExcel.Cells( 1, 5) = "Many"
   objExcel.Cells( 1, 6) = "Many"
   objExcel.Cells( 1, 7) = "More"
   objExcel.Cells( 1, 8) = "This"
   objExcel.Cells( 1, 9) = "That"
   objExcel.Cells( 1, 10) = "Whatever"
   objExcel.Cells( 1, 11) = ""
   objExcel.Cells( 1, 12) = ""
   objExcel.Cells( 1, 13) = ""
   

x = 2
  Dim folder, files, here, sFolder

      sFolder = "."
  Set folder = fso.GetFolder(sFolder)
  Set here = folder.Files
 
For each sfilename In here
        if right(sfilename, 12) = "_results.txt" then
                  Set fsoR = fso.OpenTextFile(sfilename, 1, TristateFalse)
                  ary = Split(fsoR.ReadAll, vbCrLf)
                  fsoR.close
                  i = 0
                  For Each res In ary
                        yres = trim(mid(res,2))
                        objExcel.Cells( x, i + 1) = yres
                        i = i + 1      
                  next
                  x = x + 1
            fso.movefile sfilename, strdirectory & "\"
            end if
next
objexcel.ActiveWorkbook.SaveAs(strxlloc)
0
 
William ElliottSr Tech GuruCommented:
run the first script on your computer,. lok at the text you want to change in the popups,...
then change that information in the script,..

the first scripts writes to a text file,... basically you will get a lot of text files with the name
some random number then underscore results.txt
example
12341_results.txt

once you have a bunch of those on your share drive then you can run the second script  and it will throw them into an excel sheet. then move the files into a compiled folder showing that they have been imported. ....play around with it and test  it before you use it,. i kind of just put ait all together pretty quickly.

i have not put a lot of extra logic into this as it will take some time (so maybe some others can help)

0
 
William ElliottSr Tech GuruCommented:
yes it is a vbs script
0
 
bsharathAuthor Commented:
Thanks this is also a nice way if you can get me all the boxes in a single window
name  Blank box
Age  Blank box

Etc

0
 
William ElliottSr Tech GuruCommented:
you cannot do this with vbscript. you will either have to design a custom program or go to those links provided above to create a custom form using outlook.

a third option would be to create as ASP page with a form in it. if you have an ASP server, then i could assist with the creation of that instead.
0
 
bsharathAuthor Commented:
Yes i have a ASP server.Can you help please.
0
 
William ElliottSr Tech GuruCommented:
why not go all out and import to sql as well.. do you have a sql server to write to?
0
 
bsharathAuthor Commented:
Yes i have a sql server too.But you need to tell me in detail how to do it.

0
 
William ElliottSr Tech GuruCommented:
save this as "userdata.sql"

use master
go
create database Userdata
go
use Userdata
go
use master
go
sp_addlogin 'userdata_ID1','userdata_PW1','userdata'
go
use userdata
go
sp_adduser userdata_id
go
sp_addrolemember 'db_datareader','userdata_ID1'
go
sp_addrolemember 'db_datawriter','userdata_ID1'
go

Create Table Users(
Name varchar(256),
age varchar(256),
title varchar(256),
team_name varchar(256),
manager_name varchar(256),
many1 varchar(256),
many2 varchar(256),
more varchar(256),
this varchar(256),
that varchar(256),
whatever varchar(256))
go
0
 
William ElliottSr Tech GuruCommented:
save this as user.asp

<%@ Language = "VBScript" %>
<% Response.Buffer = true %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%
strupdate = Request.QueryString("updater")
Select Case Trim(strupdate)
      Case Trim("1")
            ' -- Repeat Visit, Update the Data
            UpdateData
      Case Else
            ' -- First Time Visit, display HTML Form
            DisplayHTMLForm
End Select
%>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<TITLE>Get User info</TITLE>
</HEAD>
<HTML>

<%
Sub DisplayHTMLForm()
%>
<form method="post" action="user.asp?updater=1">
<input type="text" name="Name"/> "What is your Name?"<br />
<input type="text" name="age" />"What is your Age?"      <br />
<input type="text" name="title" />"What is your Title?"<br />
<input type="text" name="team_name" />"What is your Team Name?"<br />
<input type="text" name="manager_name" />"What is your Manager's Name?"<br />
<input type="text" name="many1" />"What is your Many?"<br />
<input type="text" name="many2" />"What is your Many?"<br />
<input type="text" name="more" />"What is your More?"<br />
<input type="text" name="this" />"What is your this?"<br />
<input type="text" name="that" />"What is your that?"<br />
<input type="text" name="whatever" />"What is your whatever?"<br />
<input type="submit" value="   Update   " />
</form>
<%
end Sub
%>


<%
Sub UpdateData()
strName                  = Request.form("Name")
strage                  = Request.form("age")
strtitle            = Request.form("title")
strteam_name      = Request.form("team_name")
strmanager_name      = Request.form("manager_name")
strmany1            = Request.form("many1")
strmany2            = Request.form("many2")
strmore                  = Request.form("more")
strthis                  = Request.form("this")
strthat                  = Request.form("that")
strwhatever            = Request.form("whatever")

query = "Update Users"             &_
            "Set Name ="             & checkstring(strName, ",") & _
            "age = "                   & checkstring(strage, ",") & _
            "title = "                   & checkstring(strtitle, ",") & _
            "team_name = "            & checkstring(strteam_name, ",") & _
            "manager_name = "      & checkstring(strmanager_name, ",") & _
            "many1 = "                   & checkstring(strmany1, ",") & _
            "many2 = "                  & checkstring(strmany2, ",") & _
            "more = "                  & checkstring(strmore, ",") & _
            "this = "                  & checkstring(strthis, ",") & _
            "that = "                  & checkstring(strthat, ",") & _
            "whatever = "            & checkstring(strwhatever, ",") & _
            "Where name = '"       & strname & "' "

sqlserver = "192.168.1.100" 'name or IP of the sql server
strcon = "Driver={SQL Server};Server=" & sqlserver & ";Database=userdata;Uid=userdata_ID1;Pwd=userdata_PW1;"
Session.Timeout = 480
set connection=server.createObject("ADODB.Connection")
connection.OPEN strcon
'set rs = server.createobject("ADODB.Recordset")
'rs.cursortype = adopenstatic

connection.Execute (query)
connection.close

Response.Redirect "user.asp?updater=0"

Response.Write("strName")
Response.Write("strage")
Response.Write("strtitle")
Response.Write("strteam_name")
Response.Write("strmanager_name")
Response.Write("strmany1")
Response.Write("strmany2")
Response.Write("strmore")
Response.Write("strthis")
Response.Write("strthat")
Response.Write("strwhatever")
End Sub

Function CheckString(byval strText, byval strEndChar)
    strText = Replace(strText, "'", "''")
    If strText = "" Then
        CheckString = "Null" & strEndChar
    Else
        CheckString = "'" & strText & "'" & strEndChar
    End if
End Function
%>
0
 
William ElliottSr Tech GuruCommented:
do you know anything about SQL or ASP?

the sql script will crate the database, userlogin for the database, password, and all the required fields that are used within the ASP page..

look through it and notice the coorelating names

Name, age, title, team_name, manager_name, many1, many2, more, this, that, whatever

since you didn't give me exact details, i used what information you did give me..  you shoudl be able to edit the  scripts,.. by changing similar datate for the above data to match what you want.

ask your questions... remember google is also your friend to help you understand the scripts better,.
0
 
bsharathAuthor Commented:
wellio,

Thanks i have searched the google for a long time but still have some douts.

What are the softwares required to run this.
Any thing i need to edit. How should i execute this
0
 
William ElliottSr Tech GuruCommented:
userdata.sql is run within sql,.. it creates a database, a userid, a password, and then the tables within the database

user.asp is loaded into IIS(your webserver) most likely under c:\inetpub\wwwroot\users\user.asp

and accessed via http://webservername/users/user.asp
0
 
bsharathAuthor Commented:
I have copied the asp file to the path mentioned and use this address to connect

http://dev-chen-mrd100/users/user.asp

But get page cannot be found
0
 
bsharathAuthor Commented:
Is there any thing i need to edit in this file

C:\Inetpub\wwwroot\Users\User.asp
0
 
William ElliottSr Tech GuruCommented:
Create Table Users(                we are creating a table called users
Name varchar(256),                  this is a field named "Name" that can be 256                         charactors long
age varchar(256),                  this is fieldname age
title varchar(256),                  this is fieldname title
team_name varchar(256),            this is fieldname team_name
manager_name varchar(256),            this is fieldname Manager_name
many1 varchar(256),            this is fieldname many1
many2 varchar(256),            this is fieldname many2
more varchar(256),                  this is fieldname more
this varchar(256),                  this is fieldname this
that varchar(256),                  this is fieldname that
whatever varchar(256))            this is fieldname whatever



now notice the corresponding information in the asp page..

each of these is listed 3 times.

we will look at just the first one "Name"

1) <input type="text" name="Name"/> "What is your Name?"<br />
this is the first instance of name, used as a variable when asking "what is your name"

2) strName = Request.form("Name")
the response to the question is placed into a second variable strname

3) "Set Name =" & checkstring(strName, ",") & _
then strname is placed into a sql query to update the database.
0
 
William ElliottSr Tech GuruCommented:
i still say the outlook form will be easier for you t o figure out, especially if you don't know iis or sql,. and the instructions are up above in a link
0
 
bsharathAuthor Commented:
I think we have done with 80% of the job.Little more help please....
0
 
William ElliottSr Tech GuruCommented:
have you created the database within SQL and have you verified it exists and the table structure exists how you want it?


within IIS you need to set the security to allow anonymouse access (or a specific group) depending on how you want it.

this article has a basic explanation of how to setup iis security within a domain.


once we have it working for the current configuration, you can then set it up how you actually want it,.
e.g. changing the questions
0
 
William ElliottSr Tech GuruCommented:
0
 
bsharathAuthor Commented:
Thanks a lot weellio.You made a lot of change to my work....
0
 
William ElliottSr Tech GuruCommented:
did you get it working?
0
 
bsharathAuthor Commented:
weellio

I have managed to get this working and this works great.

ID: 19605925
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 21
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now