Email macro hangs

Kdilas

Board Regular
Joined
Jul 11, 2002
Messages
100
Hello all.

I've got an email macro that first saves selected sheets to a new workbook then emails that workbook to whomever i select. the problem is it hangs after i select who to send it to and will not let me hit the send button unless I do a Ctrl,Alt,Del and then cancel. I also recieve an extra screen that i would like to avoid. Any suggestions? Complete code is listed below.




Private Sub CommandButton5_Click()
'E-Mail Selected Sheets
Dim txtmsg As String, txttitle As String
Dim txtresult As String, txtdefault As String
Dim JJ As String
MsgBox "File Will Be Saved In Directory Specified"
JJ = Application.GetSaveAsFilename("My Sheets", "Microsoft Excel Workbook (*.xls), *.xls", , "Hi-Tech Traverse City")
If JJ = "False" Then GoTo LastLine
Dim i As Integer, n() As String, f As Integer
Dim cnt1 As Integer, cnt2 As Integer
cnt1 = 0
For f = 0 To Me.ListBox1.ListCount
On Error GoTo 1
If Me.ListBox1.Selected(f) Then _
cnt1 = cnt1 + 1
1: Next f
On Error GoTo Line2:
ReDim n(1 To cnt1)
cnt2 = 0
For i = 0 To (Me.ListBox1.ListCount - 1)
On Error Resume Next
If Me.ListBox1.Selected(i) Then
cnt2 = cnt2 + 1
n(cnt2) = Me.ListBox1.List(i)
End If
Next i
On Error GoTo 0
Sheets(n).Copy
Sheets(n).Select
ActiveWorkbook.SaveAs Filename:=(JJ), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks.Open Filename:=JJ
Application.Dialogs(xlDialogSendMail).Show
ActiveWindow.Close
GoTo LastLine:
Line2:
MsgBox "You Must Select Sheets Before Executing Command!"
LastLine:
UserForm3.Hide
Unload UserForm3
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try unloading the UserForm before sending the EMail.

Also you can mail the workbook using the SendMail method, but you will need to know the recipients.
 

Forum statistics

Threads
1,144,223
Messages
5,723,120
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top