Email Won't Attach File Unless File saved

[Jaye7]

I am having trouble with the email script in excel, if the active workbook isn't saved then it will not attach the file, many times I don't want to save the file as it is just a meaningless report (to me) that someone wants and if I need it in the future then I can retrieve it via my sent items etc...

I could save the file using a script and then delete it once finished, just thought that someone mights have a better solution.

my script follows

Private Sub CommandButton1_Click()
If TextBox24.Value = "" Then
MsgBox "You MUST Enter A Valid Email Address," & vbLf & vbLf & "Enter The Email Address And Then Click On The Send Email Button Again"
Exit Sub
End If
If TextBox22.Value = "" Then
MsgBox "You MUST Enter A Valid Subject," & vbLf & vbLf & "Enter Your Subject And Then Click On The Send Email Button Again"
Exit Sub
End If
Email2.Show vbModeless
YesNoCancel = MsgBox("Click Yes To Attach File" & vbLf & vbLf & "Click No For No Attachment" & vbLf & vbLf & "Click Cancel To Exit" & vbLf & vbLf, vbYesNoCancel + vbCritical, "Caution")
Select Case YesNoCancel
Case vbYes
Call attachYes
 
Case vbNo
Call attachNo
Case vbCancel
Exit Sub
End Select
End Sub
Sub attachNo()
Dim OutApp As Object
    Dim OutMail As Object
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = TextBox24.Value
        .CC = TextBox10.Value
        .BCC = TextBox11.Value
        .Subject = TextBox22.Value
        .body = TextBox20.Value
        '.Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        .Send   'or use .Display
 
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
Application.DisplayAlerts = False
 
'For Each wb In Application.Workbooks
    ' Select Case wb.Name
             'Case ThisWorkbook.Name
                 ' do nothing
             'Case Else
                   'wb.Close
     'End Select
'Next wb
Application.DisplayAlerts = True
On Error Resume Next
Email2.Hide
Worksheets("Contacts").Visible = False
End With
End Sub
Sub attachYes()
Dim OutApp As Object
    Dim OutMail As Object
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = TextBox24.Value
        .CC = TextBox10.Value
        .BCC = TextBox11.Value
        .Subject = TextBox22.Value
        .body = TextBox20.Value
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        .Send   'or use .Display
 
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
Application.DisplayAlerts = False
 
'For Each wb In Application.Workbooks
    ' Select Case wb.Name
             'Case ThisWorkbook.Name
                 ' do nothing
             'Case Else
                   'wb.Close
     'End Select
'Next wb
Application.DisplayAlerts = True
On Error Resume Next
Email2.Hide
Worksheets("Contacts").Visible = False
End With
 
End Sub

Also, it's really annoying as whenever I email this way outlook pops up with a virus message that I have to confirm that it is not a virus, does anyone know a way around this.

Thanks

 

[John_w]

I think you have to save the workbook before you can attach it, because .Attachments.Add is expecting an existing file. As you say, just Save As and then delete it - do these steps with the Macro Recorder to generate the VBA code.

For the Outlook virus warning, see http://www.rondebruin.nl/mail/prevent.htm

 

[Jaye7]

Thanks John,

My work blocks us from installing any programs, even printers on their super special system, fortunately I installed the clickyes software on my normal pc and just copied the folder into the other operating system and it still works (many don't), the message still pops up but it over-rides it so that you don't have to click yes or no etc... so the macro can continue to do other processes.

Fortunately there are many good softwares like this that I can use to get around their blocks, the best one is winmacro recorder it has saved me 1000's of hours of meaningless keying of data.

thanks again.