Send Copy To Email VBA

[pure vito]

Hi All,

I found the below code here, and it works as It should very well,

I was hoping someone might be able to modify this if possible please, I have applied this macro to a button press, but I would like the sender to be able to edit the body of the email text, via a message box or something?

And then have the option to send or cancel,

I'd really appreciate any help,

Thanks,

Sub Mail_workbook_Outlook_2()
'Working in Excel 2000-2016
'Mail a copy of the ActiveWorkbook with another file name
'For Tips see: https://jkp-ads.com/rdb/win/winmail/Outlook/tips.htm
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "Joe@live.com"
        .CC = ""
        .BCC = ""
        .Subject = "BEV TT Pick Request"
        .Body = "Please find our request attached"
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

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

 

[ranman256]

comment out: .Send
UNcomment: .Display

 

[pure vito]

comment out: .Send
UNcomment: .Display

Appreciate the response, I'm not actually sure what to do with this if you can help further please

 

[Logit]

'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display

Change the above section of code so it looks like this :

'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        '.'Send   'or use .Display
        .Display

 

[pure vito]

Thanks @Logit,

I should have just had a better understanding of the comments

I made this change and now I get the email display so I can make changes, however for some reason when I hit send it doesn't actually come through but if i change it back to .send then it will push both emails through? so at the moment it does what I want but I'm not receiving the email when hitting send

 

[Logit]

I have experienced the need to have Outlook running in the background when sending emails via Excel vba.

 

[pure vito]

I have experienced the need to have Outlook running in the background when sending emails via Excel vba.

I always have mine online, I'll certainly try different thing, thanks for getting back to me and thank you for solving this issue

 

[pure vito]

Thanks @Logit after a lot of head scratching I realised having outlook open on google was the issue, if I open the app it works just fine, I'll just have to make sure anyone using it has the app open otherwise it won't go through,

Thanks again for your help

 

[Logit]

You can add some code that opens Outlook when you open the workbook.

 

[pure vito]

Great idea, I'll see if I can get that in, Thanks