Submit pre-addressed email macro

[michshaughnessy]

I'm trying to incorporate a "submit" button within Excel so the spreadsheet is automatically sent as an attachment to a pre-addressed email address. Currently, I can create a macro that attaches the spreadsheet in an email but I am required to address it and send it manually.

Is there a function that removes the final two manual steps of addressing and sending the email?

Thanks

[Lewis]

The following saves the active sheet as a new workbook in the \temp directory. It takes info from a cell and inserts it into the filename. It then emails it as an attachment to a list held on another sheet, skipping blank lines, then kills the temp file.

Sub Mail()

myFile = "Problem_Report_" & Range("B5").Value
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveAs "C:\Windows\temp\" & myFile
ThisWorkbook.Activate
If Not IsNull(Application.MailSession) Then Application.MailLogon
With ThisWorkbook.Worksheets("Sheet6").Cells
For Each c In .Range("D2:D12").Cells
s = c.Value
If s = "" Then GoTo Skip
Workbooks(myFile & ".xls").SendMail Recipients:=s, _
Subject:="Here is an UPDATED Problem Report #" & Range("B5").Value, _
ReturnReceipt:=False

Skip:
Next c
End With

Workbooks(myFile & ".xls").Close False
Kill "C:\Windows\temp\" & myFile & ".xls"

End Sub

Modify to suit

Good Luck

Lewis