Create but don't send an email from Excel

[SimonH]

Hi all,

I've got the code to send an excel file as an attachment to an email which works perfectly. But I've just been asked by the users if I can make Excel create the email with attachment/recipients already in place but allow the user to enter whatever text they want in the body of the email before its sent.

The file being sent is already formatted to be viewed on the screen without needing to scroll left/right/up/down to see the contents so there isn't really any room for a cell to used for the email content. So I need the actual email to remain open and the user to hit the send button after editing the text, or a box to pop up in excel for them to add any text they want included.

Any ideas? I've been searching and trying things but not managed to solve the issue yet....so its time to call in the experts.

Thanks

 

[Ruddles]

Do you do a .Send to send the email?

If so, do .Display instead.

 

[VoG]

If you are using Outlook try changing .Send to .Display.

 

[SimonH]

I use .SendMail

I've tried changing to .Display and .DisplayMail but neither of these seem to do anything. The code I have is as follows, in case this helps narrow down where I'm going wrong :

Sub Mail_Range()
Dim MyCell
MyCell = Range("K13").Value

Sheets("Calculation").Range("AA9").Value = Application.UserName

'Working in 2000-2007
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Set Source = Nothing
On Error Resume Next
Set Source = Range("Print_Area")
On Error GoTo 0
If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected, " & _
"please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)
Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = MyCell & " " & Format(Now, "dd-mmm-yy hmm")
If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If
With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next



.SendMail ("email address is here"), _
MyCell & " Trade Order " & Format(Now, "dd-mmm-yy hmm")
On Error GoTo 0
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

 

[VoG]

I don't think that's an option with the SendMail method. You would need to use code for Outlook - several examples here http://www.rondebruin.nl/sendmail.htm

 

[SimonH]

Thanks Peter, I've now got it working as I need it to.

 

[obiron]

Simon,

Please post your solution for others to adopt.

Thanks