User form and send mail
 

Hi guys, I'm having some problems with Ron de Bruins send mail script o
a form command. I think I know where the problem is but I can’t fix it


What I’m looking for is

1 Command button clicked
2 Current form to be emailed
3 Form data to be cleared
4 Form to be saved and then closed

It’s number two which is causing the problems. I’m using the followin
code;


Code
-------------------
Private Sub cmdemail_Click()
If MsgBox("Please note this you only be sent at the end of the day. Are you sure you have finished for the day?", vbYesNo Or vbQuestion _
Or vbDefaultButton2) = vbYes Then
Unload Me
Call Mail_workbook_Outlook
End If
End Su
-------------------



Code
-------------------
Sub Mail_workbook_Outlook()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
wbname = "H:/" & wb1.Name & " " & _
Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
wb1.SaveCopyAs wbname
Set wb2 = Workbooks.Open(wbname)
With wb2
.SendMail ", _
"todays work"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Su
-------------------


The program just hangs at the save temporary file part until you clic
on the close cross on the form so it looks like it will not save unles
the form is closed but my efforts with Unload Me or Unload (Formname
have been in vain.

Could anyone give me some pointers to where I have gone wrong?

-N

--
wizard535
-----------------------------------------------------------------------
wizard5353's Profile: http://www.excelforum.com/member.php...fo&userid=2369
View this thread: http://www.excelforum.com/showthread.php?threadid=47107

User form and send mail
 

Hi wizard5353

I test it and it is working for me.
You can send me your test workbook private then I test it for you.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"wizard5353" wrote in message
...

Hi guys, I'm having some problems with Ron de Bruins send mail script on
a form command. I think I know where the problem is but I can't fix it.


What I'm looking for is

1 Command button clicked
2 Current form to be emailed
3 Form data to be cleared
4 Form to be saved and then closed

It's number two which is causing the problems. I'm using the following
code;


Code:
--------------------
Private Sub cmdemail_Click()
If MsgBox("Please note this you only be sent at the end of the day. Are you sure you have finished for the day?", vbYesNo Or
vbQuestion _
Or vbDefaultButton2) = vbYes Then
Unload Me
Call Mail_workbook_Outlook
End If
End Sub
--------------------



Code:
--------------------
Sub Mail_workbook_Outlook()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
wbname = "H:/" & wb1.Name & " " & _
Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
wb1.SaveCopyAs wbname
Set wb2 = Workbooks.Open(wbname)
With wb2
.SendMail ", _
"todays work"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
--------------------


The program just hangs at the save temporary file part until you click
on the close cross on the form so it looks like it will not save unless
the form is closed but my efforts with Unload Me or Unload (Formname)
have been in vain.

Could anyone give me some pointers to where I have gone wrong?

-NJ


--
wizard5353
------------------------------------------------------------------------
wizard5353's Profile: http://www.excelforum.com/member.php...o&userid=23692
View this thread: http://www.excelforum.com/showthread...hreadid=471070