+ Reply to Thread
Results 1 to 12 of 12

Send sheet as Outlook attachment

  1. #1
    Registered User
    Join Date
    02-01-2009
    Location
    South Wales
    MS-Off Ver
    Excel 2003
    Posts
    25

    Send sheet as Outlook attachment

    Hi,

    Could someone please help me with this? I have compliled a form that staff need to fill out to provide notification of visitors using Excel 2003.

    The idea is that details are entered onto the form and then emailed to the department concerned. Once the email has been sent I need the original details on the worksheet form to be cleared in readiness for the next user.

    I've attempted to put together a macro to do this and tried various ways to get it to do what I want, but to no avail - please see attached file.

    Any help or advice would be greatly appreciated.

    Thanks
    Attached Files Attached Files
    Last edited by Jessy01; 03-01-2011 at 10:20 AM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,524

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Hi there,

    Try the following code and see if it does what you need:

    Please Login or Register  to view this content.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Last edited by Greg M; 02-21-2011 at 12:52 PM. Reason: Minor change to code

  3. #3
    Registered User
    Join Date
    02-01-2009
    Location
    South Wales
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Quote Originally Posted by Greg M View Post
    Hi there,

    Try the following code and see if it does what you need:

    Please Login or Register  to view this content.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Greg,

    Thanks very much for your quick reply its really appreciated. However, I seem to have a slight problem as when I run it, I get the "Error Encountered" message.

    I think it may be because I haven't correctly completed the part where you state "Enter a valid location for the temporary file and then save a copy of this to file to it".

    I dont quite understand exactly what I need to do in this respect as I am still on a learning curve, not sure what is meant by the temp file?

    Would it be possible for you to explain step by step what I need to do in this respect please.

    Thanks for your time

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,524

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Hi again,

    The "permanent" file is the original workbook which you've created, and which normally doesn't store the visitor's details. The "temporary" file is just a saved copy of this "permanent" file in which the current visitor's details have been entered.

    The "temporary" file is appended to the email as an attachment, and is then deleted once the email has been sent. In a relatively simple situation such as this you could in fact send the "permanent" file as an attachment, and dispense with the "temporary" file, but the method I posted is one which I used in a more complex situation.

    Now - let's try to find out where the problem is occurring!

    Go into the VBA Editor, and place the cursor anywhere within the code of the SendEmail function. Now press key F8. You'll see that the first (i.e. Function Declaration) line of the routine is highlighted.

    Press the F8 key once again and you'll see that the first executable line (i.e. On Error GoTo ErrorEncountered) of the routine is highlighted. Continue to press the F8 key, and note the line where pressing this key causes the program execution to jump to the "MsgBox "An error was encountered", vbCritical" statement - this is the line which is causing the problem.

    When you've identified the "problem line", have a look at its code and see if there's anything obviously wrong with it - e.g. a reference to a non-existent filepath etc. If you can identify and solve the problem yourself, that's great - otherwise let me know which line is involved and I'll see what I can do.

    Regards,

    Greg M

  5. #5
    Registered User
    Join Date
    02-01-2009
    Location
    South Wales
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Quote Originally Posted by Greg M View Post
    Hi again,

    The "permanent" file is the original workbook which you've created, and which normally doesn't store the visitor's details. The "temporary" file is just a saved copy of this "permanent" file in which the current visitor's details have been entered.

    The "temporary" file is appended to the email as an attachment, and is then deleted once the email has been sent. In a relatively simple situation such as this you could in fact send the "permanent" file as an attachment, and dispense with the "temporary" file, but the method I posted is one which I used in a more complex situation.

    Now - let's try to find out where the problem is occurring!

    Go into the VBA Editor, and place the cursor anywhere within the code of the SendEmail function. Now press key F8. You'll see that the first (i.e. Function Declaration) line of the routine is highlighted.

    Press the F8 key once again and you'll see that the first executable line (i.e. On Error GoTo ErrorEncountered) of the routine is highlighted. Continue to press the F8 key, and note the line where pressing this key causes the program execution to jump to the "MsgBox "An error was encountered", vbCritical" statement - this is the line which is causing the problem.

    When you've identified the "problem line", have a look at its code and see if there's anything obviously wrong with it - e.g. a reference to a non-existent filepath etc. If you can identify and solve the problem yourself, that's great - otherwise let me know which line is involved and I'll see what I can do.

    Regards,

    Greg M
    Hi Greg,

    Thanks for your patience and clear advice, it runs and works perfect, I didn't have the correct file location on the first attempt which caused the error.

    I now have just one slight problem though, I can't seem to get the macro to work from the Send button; not sure if I am doing something wrong. I've even tried to 'assign macro' to an alternative button from Drawing but when I look in the available macros box there is no macro showing for some reason. I do not normally have a problem in assigning a macro to a button.

    Would it be possible to explain how to connect the macro to the button please.

    Thanks very much for your time

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,524

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Hi Jessy,

    I think I see your problem!

    Your original workbook contained the code :

    Please Login or Register  to view this content.

    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:
    Please Login or Register  to view this content.

    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

  7. #7
    Registered User
    Join Date
    02-01-2009
    Location
    South Wales
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Quote Originally Posted by Greg M View Post
    Hi Jessy,

    I think I see your problem!

    Your original workbook contained the code :

    Please Login or Register  to view this content.

    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:
    Please Login or Register  to view this content.

    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

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,524

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    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:

    Please Login or Register  to view this content.

    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

  9. #9
    Registered User
    Join Date
    02-01-2009
    Location
    South Wales
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Hi Greg,

    Thanks for Date inclusion code advice, it works fine.

    Regarding the flashing Msg Box in the Taskbar which has to be clicked to obtain the message; it's strange that when I run it from the button - it flashes, however when I run it from within the code it doesn't for some reason and the Msg Box appears in the middle of the screen as normal (on both versions).

    If as you say there is no way around this peculiarity then I don't want you to waste your time unduly on it. You have been most helpful with your advice and I am very grateful.

    Cheers,

    J

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,524

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Hi Jessy,

    Many thanks for your feedback. I'm glad you found my suggestions helpful.

    Regarding your comment:
    If as you say there is no way around this peculiarity ...
    Please note that I'm not saying there IS no way around it, just that I'm not aware of any - big difference!

    Best regards,

    Greg M

  11. #11
    Registered User
    Join Date
    02-01-2009
    Location
    South Wales
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Quote Originally Posted by Greg M View Post
    Hi Jessy,

    Many thanks for your feedback. I'm glad you found my suggestions helpful.

    Regarding your comment:


    Please note that I'm not saying there IS no way around it, just that I'm not aware of any - big difference!

    Best regards,

    Greg M
    Hi Greg,

    Just to let you know that I've solved the problem regarding the MsgBox flashing in the taskbar instead of on the form. It seems that it was caused by me using a Control Box button instead of a Forms button. It works fine now that I've changed it.

    Best Regards,

    J

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,524

    Re: Code for sending w/sheet as attachment in outlook and clear w/sheet entries after

    Hi Jessy,

    Many thanks for taking the trouble to send me that feedback - I'll make a careful note of it for future reference!

    I'm glad I was able to help.

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1