Send sheet as Outlook attachment

Originally Posted by Greg M

Hi Jessy,

I think I see your problem!

Your original workbook contained the code :

Private Sub CommandButton1_Click()
    SendEmail
End Sub


Public Function SendEmail()
Many lines of code here

End Function

Now, when you try to assign a routine to a button, the FUNCTION will not appear in the list of available macros (because it's not a "Sub" procedure), and the SUB will not appear because it's been declared as "Private".

So, either declare the SUB as a "Public" procedure, or (better!) convert the FUNCTION to a "Public Sub" procedure. Just use:

Public Sub SendEmail()

The "End Function" statement will be converted automatically to "End Sub".

This should allow the procedure to appear in the list of available macros so that you can assign it to whichever object you want.

Please let me know how you get on with this.

Regards,

Greg M

Hi Greg,

Thanks for that, have amended the macro as you advised and this has resolved that issue, however, a slight problem has arisen since.

After the email has been sent, the MsgBox “Your email has been sent” does not display as it did previously; instead there is a flashing Excel tab in the Taskbar and when opened it shows the form and the message, then when I click “OK” it stops flashing. Can we prevent this?

Also, would it be possible to have the ‘date of visit’ from cell E8 displayed next to the “Mail” on the subject line?

Once again many thanks for your time.

J

Hi Jessy,

To add the date from Cell E8 to your Subject line just add one line of code and change one line of code where I've indicated:

Public Function SendEmail()

    Const sDATE_CELL    As String = "E8"
'   ADD THE ABOVE LINE              <<<<<<<<<<<<<<<<<<<<<<<

    Dim objOutlook      As Object
    Dim objEmail        As Object

    Dim vDataRange      As Variant

    Dim sTemporaryFile  As String
    Dim sCopiesTo       As String
    Dim sMailText       As String
    Dim sSubject        As String
    Dim sSendTo         As String

    On Error GoTo ErrorEncountered

    sSubject = "Mail  -  " & Sheets("Sheet1").Range(sDATE_CELL).Value
'   CHANGE THE ABOVE LINE           <<<<<<<<<<<<<<<<<<<<<<<

I'm not sure about the flashing taskbar item - I've encountered it in the past, but the problem wasn't fully reproducible and I was never able to discover either a solution or a workaround.

Did this problem occur with the earlier version of the code? The code is working correctly on my pc at the moment (Windows XP, Office 2010).

Anyway, I hope the addition of the date in the subject line is of some help to you - please continue to let me know how you're getting on.

Regards,

Greg M