Hi all,

I am creating a spreadsheet that is sent out to individuals, they fill in
details and then using a "submit" button, return it to me.

I am using code I got from Ron DeBruin's site but unfortunately I cannot get
it to work. Excel gets to the point of sending the email and then just
freezes. When I run this from VBA explorer it works ok and the dialog box
saying Excel is trying to send an email appears and the email is sent when I
press yes. The problem is that it won't work if VBA is not open. Any ideas?
The send code is below.

'Save the new workbook/Mail it/Delete it

'TempFilePath = Environ$("temp") & "\"
TempFilePath = Dir

'TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"dd-mmm-yy h-mm-ss")
TempFileName = Filename & " Returned" & ".xls"

MsgBox "Your form is being returned"

With Destwb
.SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum

MsgBox "Saved Temp File"
On Error Resume Next
MsgBox "About to send"

.SendMail ", _
TempFileName
'On Error GoTo 0
MsgBox "Sent"
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Alternative code that I have used in the past is a s follows:

Dim Mail As String
Let Mail = "

' Application.Dialogs(xlDialogSendMail).Show arg1:=Mail


This works but the sendmail option seemed slightly more elegant (and also
removed most opportunities for users to mess up the process.

Hi Joseph

If you run the code from a controltoolbox button you can add this line in
the macro (as first line)

Activecell.Select

Or you can change the takefocusonclick in the button properties

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joseph Fletcher" wrote in message
...
Hi all,

I am creating a spreadsheet that is sent out to individuals, they fill in
details and then using a "submit" button, return it to me.

I am using code I got from Ron DeBruin's site but unfortunately I cannot get
it to work. Excel gets to the point of sending the email and then just
freezes. When I run this from VBA explorer it works ok and the dialog box
saying Excel is trying to send an email appears and the email is sent when I
press yes. The problem is that it won't work if VBA is not open. Any ideas?
The send code is below.

'Save the new workbook/Mail it/Delete it

'TempFilePath = Environ$("temp") & "\"
TempFilePath = Dir

'TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"dd-mmm-yy h-mm-ss")
TempFileName = Filename & " Returned" & ".xls"

MsgBox "Your form is being returned"

With Destwb
.SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum

MsgBox "Saved Temp File"
On Error Resume Next
MsgBox "About to send"

.SendMail ", _
TempFileName
'On Error GoTo 0
MsgBox "Sent"
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Alternative code that I have used in the past is a s follows:

Dim Mail As String
Let Mail = "

' Application.Dialogs(xlDialogSendMail).Show arg1:=Mail


This works but the sendmail option seemed slightly more elegant (and also
removed most opportunities for users to mess up the process.

Sorry Ron, I tried both of these suggestions but neither worked. I have
Msgboxes either side of the command, one saying that it is about ot send and
one saying that it has been sent, the first one appears and then excel stops
responding, I assume as it is trying to send the mail.

"Ron de Bruin" wrote:

Hi Joseph

If you run the code from a controltoolbox button you can add this line in
the macro (as first line)

Activecell.Select

Or you can change the takefocusonclick in the button properties

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joseph Fletcher" wrote in message
...
Hi all,

I am creating a spreadsheet that is sent out to individuals, they fill in
details and then using a "submit" button, return it to me.

I am using code I got from Ron DeBruin's site but unfortunately I cannot get
it to work. Excel gets to the point of sending the email and then just
freezes. When I run this from VBA explorer it works ok and the dialog box
saying Excel is trying to send an email appears and the email is sent when I
press yes. The problem is that it won't work if VBA is not open. Any ideas?
The send code is below.

'Save the new workbook/Mail it/Delete it

'TempFilePath = Environ$("temp") & "\"
TempFilePath = Dir

'TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"dd-mmm-yy h-mm-ss")
TempFileName = Filename & " Returned" & ".xls"

MsgBox "Your form is being returned"

With Destwb
.SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum

MsgBox "Saved Temp File"
On Error Resume Next
MsgBox "About to send"

.SendMail ", _
TempFileName
'On Error GoTo 0
MsgBox "Sent"
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Alternative code that I have used in the past is a s follows:

Dim Mail As String
Let Mail = "

' Application.Dialogs(xlDialogSendMail).Show arg1:=Mail


This works but the sendmail option seemed slightly more elegant (and also
removed most opportunities for users to mess up the process.

Hi Joseph

If you use the example workbook on my site do you have the same problem then
http://www.rondebruin.nl/sendmail.htm

Download the first file (red link) en test the examples



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joseph Fletcher" wrote in message
...
Sorry Ron, I tried both of these suggestions but neither worked. I have
Msgboxes either side of the command, one saying that it is about ot send and
one saying that it has been sent, the first one appears and then excel stops
responding, I assume as it is trying to send the mail.

"Ron de Bruin" wrote:

Hi Joseph

If you run the code from a controltoolbox button you can add this line in
the macro (as first line)

Activecell.Select

Or you can change the takefocusonclick in the button properties

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joseph Fletcher" wrote in message
...
Hi all,

I am creating a spreadsheet that is sent out to individuals, they fill in
details and then using a "submit" button, return it to me.

I am using code I got from Ron DeBruin's site but unfortunately I cannot get
it to work. Excel gets to the point of sending the email and then just
freezes. When I run this from VBA explorer it works ok and the dialog box
saying Excel is trying to send an email appears and the email is sent when I
press yes. The problem is that it won't work if VBA is not open. Any ideas?
The send code is below.

'Save the new workbook/Mail it/Delete it

'TempFilePath = Environ$("temp") & "\"
TempFilePath = Dir

'TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"dd-mmm-yy h-mm-ss")
TempFileName = Filename & " Returned" & ".xls"

MsgBox "Your form is being returned"

With Destwb
.SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum

MsgBox "Saved Temp File"
On Error Resume Next
MsgBox "About to send"

.SendMail ", _
TempFileName
'On Error GoTo 0
MsgBox "Sent"
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Alternative code that I have used in the past is a s follows:

Dim Mail As String
Let Mail = "

' Application.Dialogs(xlDialogSendMail).Show arg1:=Mail


This works but the sendmail option seemed slightly more elegant (and also
removed most opportunities for users to mess up the process.