VB Help for Spreadsheet!

[anonymous]

[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:


Sub SendSummary()
Sheets("Summary").Activate
sendDate = Date
Cells(5, 4) = sendDate
collectDate = Cells(2, 4)
If sendDate < collectDate Then
* * Title = &quot;TRAC Data Gathering System&quot;
* * MessX = &quot;Are you sure you want to send the summary before the end of the quarter&quot;
* * YesNo = MsgBox(MessX, vbYesNo, Title)
* * If YesNo = 6 Then
* * * * sendAnyway = True
* * Else
* * * * sendAnyway = False
* * End If
Else
* * sendAnyway = True
End If
If sendAnyway Then
* * EENA = Cells(3, 4) 'cell D3
* * Quarter = Cells(4, 4)
* * Sheets(&quot;Summary&quot;).Select
* * Sheets(&quot;Summary&quot;).Copy
* * ActiveWorkbook.SaveAs Filename:=EENA & &quot;-04-Q&quot; & Quarter & &quot;.xls&quot;, _
* * * * FileFormat:=xlNormal, Password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, _
* * * * 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(&quot;Teaching&quot;).Select


Can anyone help?

Thanks!

[anonymous]

[Originally posted by Ron Weller]

xlDialogSendMail this dialog has three arguments
recipients, subject, return_receipt
* you place them after the show in the order
displayed above.
Application.Dialogs(xlDialogSendMail).Show "you@what.com","The subject text", "me@where.com"

* All arguments are optional