VB Help for Spreadsheet!
[Originally posted by Lyn]
I am trying to compile a spreadsheet which has to be sent out to 500 employees - for them to fill in and then send it back to a mail box which has been set up especially for them in Outlook.* The problem is we want the macro to be set up to send the sheet automatically without the 'Outlook mail message' coming up.* We also need our mail box to be in the 'To' box (of the email) and then for them to click 'Send' and then the sheet goes automatically.
This is the code we have so far:
sendDate = Date
Cells(5, 4) = sendDate
collectDate = Cells(2, 4)
If sendDate < collectDate Then
* * Title = "TRAC Data Gathering System"
* * MessX = "Are you sure you want to send the summary before the end of the quarter"
* * YesNo = MsgBox(MessX, vbYesNo, Title)
* * If YesNo = 6 Then
* * * * sendAnyway = True
* * Else
* * * * sendAnyway = False
* * End If
* * sendAnyway = True
If sendAnyway Then
* * EENA = Cells(3, 4) 'cell D3
* * Quarter = Cells(4, 4)
* * Sheets("Summary").Select
* * Sheets("Summary").Copy
* * ActiveWorkbook.SaveAs Filename:=EENA & "-04-Q" & Quarter & ".xls", _
* * * * FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
* * * * ReadOnlyRecommended:=False, CreateBackup:=False
* * Application.Dialogs(xlDialogSendMail).Show
* * ' now need to automatically create a mail message that send the sheet EENA-04-Qn.xls
* * ' to some standard email address that has been set up to collect all this data
* * ActiveWorkbook.Close
* * Sheets("Teaching").Select
Can anyone help?
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL