One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
Go Back  Xtreme Visual Basic Talk > > > > One Excel form VB Macro to XFR data to an exisiting spreadsheet


Reply
 
Thread Tools Display Modes
  #1  
Old 01-08-2009, 05:53 PM
a1point8turbo a1point8turbo is offline
Newcomer
 
Join Date: Jan 2009
Posts: 1
Question One Excel form VB Macro to XFR data to an exisiting spreadsheet


I am trying to automate a telemarketing spreadsheet I made. Usually I am just a reader, but I can't seem to figure this part out by just reading posts and books so I am looking for some help.

Currently I have the first sheet set up for input; # of calls made, contacts made, time started/stopped, ect.

On that sheet the user selects his/her name, and submits the data when done. It then takes the user to the second sheet (labeled "EOD") to review how they did on that day. From there they click a button (linked to a macro is VB) which takes the data on the EOD screen (data = calls made, calls per hour, contacts per hour, time started/topped, ect) and sends it to me as a nicely formated email.

What I am trying to do next is save that data (located on the "EOD" sheet) in that individuals own excel sheet for archiving. I have 7 fields of data that would need to be transfered.

So if say Joe Smith Selected his name on the first page, when he gets to the EOD sheet and emails those 7 fields(boxs) of data I would also like the macro to open "Joe Smiths" archived file, move down to the next available row input the data from the "EOD" fields, save the changes and close the spreadsheet.

This is the code I have for the email portion of the data.


Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
' Get the email address
Email = Sheets("Settings").Range("B2")

' Message subject
Subj = "Telemarketing Results"

' Compose the message
Msg = ""
Msg = Msg & "Dear " & Sheets("Settings").Range("B2") & "," & vbCrLf & vbCrLf
Msg = Msg & "Results for calls made on:" & Sheets("Hidden").Range("B1") & vbCrLf & vbCrLf
Msg = Msg & "The telemarketer was " & Sheets("Sheet1").Range("B26") & vbCrLf & vbCrLf
Msg = Msg & Sheets("Sheet1").Range("B26") & " worked " & Sheets("Hidden").Range("a3") & " hours." & vbCrLf & vbCrLf
Msg = Msg & "Calls Made:" & Sheets("EOD").Range("D2") & vbCrLf & vbCrLf
Msg = Msg & "Contacts Made:" & Sheets("EOD").Range("F2") & vbCrLf & vbCrLf
Msg = Msg & "Quotes Given:" & Sheets("EOD").Range("H2") & vbCrLf & vbCrLf
Msg = Msg & "Policy Count:" & Sheets("EOD").Range("J2") & vbCrLf & vbCrLf
Msg = Msg & "Calls Per Hour:" & Sheets("EOD").Range("D13") & vbCrLf & vbCrLf
' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait one seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%s"
Application.Goto Reference:=Sheets("Sheet1").Range("B26")
End Sub
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
 
One Excel form VB Macro to XFR data to an exisiting spreadsheet
One Excel form VB Macro to XFR data to an exisiting spreadsheet
 
-->