Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail
I receive via e-mail 25 spreadsheets every week to consolidate into a group
report. I have constant problems with idiots forgetting to attach the file, attaching the wrong file, attaching the shortcut. I have been experimenting with automating the sending of these files to me. However,having read and tried many of the suggestions on e-mailing with zero success, am I asking too much to devise code that would work on all these different PC's with different mailers and very varied versions of windows & office. -- Roger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail
You could do it very easily.
set up a folder on your network that your users have write access to. In this folder you may want sub-folders whose names are datestamps...eg 20050315 This is useful as they are easy to sort & check. Each work book can now have some very simple code to generate a text file which is saved in the datestamped folder This code in the user's workbook...its not sophisticated since we're using various versions of Office and/or Excel...so kept simple: Sub Submit_Data() Dim sFileName As String Dim text As String, vText As Variant Dim rw As Long Dim cl As Long Const FileName As String = "H:\MyData\DATESTAMP\USER_Data.txt" sFileName = Replace(FileName, "USER", Environ$("username")) sFileName = Replace(sFileName, "DATESTAMP", Format$(Date, "YYYYMMDD")) Open sFileName For Output As #1 For rw = 1 To 30 text = "" For cl = 1 To 5 text = text & "," & Cells(rw, cl) Next text = Mid(text, 2) Print #1, text Next Close End Sub you'll need to set the rows/columns etc but this tested OK Your own workbook will need to read in all the data...follows... "Roger" wrote: I receive via e-mail 25 spreadsheets every week to consolidate into a group report. I have constant problems with idiots forgetting to attach the file, attaching the wrong file, attaching the shortcut. I have been experimenting with automating the sending of these files to me. However,having read and tried many of the suggestions on e-mailing with zero success, am I asking too much to devise code that would work on all these different PC's with different mailers and very varied versions of windows & office. -- Roger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail
part 2
This is th esimple code that you'd need to read in data from all the files in the dated folder. As with the previous code, the assumption is that there are 5 columns. The import puts the file name in the first column, so you know "who" sent it Option Explicit Sub MergeData() Const FolderName As String = "H:\MyData\DATESTAMP\" Dim sFolderName As String Dim rw As Long Dim fn As String Dim ws As Worksheet Set ws = Worksheets.Add Dim text As String sFolderName = Replace(FolderName, "DATESTAMP", Format$(Date, "yyyymmdd")) fn = Dir(sFolderName) Do While fn < "" Open sFolderName & fn For Input As #1 Do Until EOF(1) text = "" Line Input #1, text rw = rw + 1 With ws .Cells(rw, 1) = fn .Range(.Cells(rw, 2), .Cells(rw, 6)) = Split(text, ",") End With Loop Close fn = Dir() Loop End Sub Not the use of good old DIR() ! we could have done something classy with Scripting FileSystemObject, but this isn't really necessary. "Patrick Molloy" wrote: You could do it very easily. set up a folder on your network that your users have write access to. In this folder you may want sub-folders whose names are datestamps...eg 20050315 This is useful as they are easy to sort & check. Each work book can now have some very simple code to generate a text file which is saved in the datestamped folder This code in the user's workbook...its not sophisticated since we're using various versions of Office and/or Excel...so kept simple: Sub Submit_Data() Dim sFileName As String Dim text As String, vText As Variant Dim rw As Long Dim cl As Long Const FileName As String = "H:\MyData\DATESTAMP\USER_Data.txt" sFileName = Replace(FileName, "USER", Environ$("username")) sFileName = Replace(sFileName, "DATESTAMP", Format$(Date, "YYYYMMDD")) Open sFileName For Output As #1 For rw = 1 To 30 text = "" For cl = 1 To 5 text = text & "," & Cells(rw, cl) Next text = Mid(text, 2) Print #1, text Next Close End Sub you'll need to set the rows/columns etc but this tested OK Your own workbook will need to read in all the data...follows... "Roger" wrote: I receive via e-mail 25 spreadsheets every week to consolidate into a group report. I have constant problems with idiots forgetting to attach the file, attaching the wrong file, attaching the shortcut. I have been experimenting with automating the sending of these files to me. However,having read and tried many of the suggestions on e-mailing with zero success, am I asking too much to devise code that would work on all these different PC's with different mailers and very varied versions of windows & office. -- Roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge multiple lines in an e-mail | Excel Discussion (Misc queries) | |||
E-Mail attachment to same e-mail address in Outlook | Excel Discussion (Misc queries) | |||
E-mail to every e-mail address in an Excel column? | Excel Discussion (Misc queries) | |||
Error: cannot load the mail service. Check your mail installation. | Excel Discussion (Misc queries) | |||
General mail failure when sending e-mail from Excel | Excel Discussion (Misc queries) |