I want to send a worksheet to a specific recipient using a userform w/ radio buttons
I have a workbook with multiple pages. I have code to send my target worksheets and a userform3 that allows me to select the recipient of the worksheet and it works fine. I need to have the userform3 work with the following worksheet as well. Right now I click on the EMAIL D.A.O. Worksheet and I get the following message box: Outlook express A program is attempting to send the following e-mail message on your behalf: Subject DAO Worksheet Would you like to send the message? I want to have a user form allowing me to select the area manager I want to send it to. All my other worksheets work fine, it is just this one. Can someone help me. I'm not really good with this stuff. Userform3 allows me to click on a radio button to select the appropriate recipient
Private Sub CommandButton4_Click()
'copy & Send DAO Worksheet
Dim wSheet As Worksheet
Dim FileExtStr As String
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
'
Set Sourcewb = ActiveWorkbook
Sourcewb.worksheets("DAO Worksheet").Activate
'
ActiveSheet.Copy
'
Set wSheet = ActiveWorkbook.worksheets("DAO Worksheet")
wSheet.Activate
With wSheet
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
'
TempFilePath = Environ$("temp") & "\"
TempFileName = "DAO COPY"
FileExtStr = ".xls"
'
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs TempFilePath & TempFileName & FileExtStr
End With
Application.DisplayAlerts = True
'
SendDAO
'
With ActiveWorkbook
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
'
Sheet7.Activate
'
End Sub
Private Sub SendDAO()
On Error Go to NOSEND
ActiveWorkbook.SendMail _
Recipients:="[email protected]", _
Subject:="DAO WkSheet"
'
Exit Sub
'
NOSEND:
MsgBox ("NOT SENT SUCCESSFULLY - PLEASE SEND MANUALLY AS AN ATTACHMENT")
End Sub
Private Sub EMAILDM_Click()
UserForm3.Show
End Sub