Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    emailing a worksheet (E2000)

    I know that this is quite a well worn subject and I have searched and read the various solutions, the one | have tried to adopt is that posted by Andrew Connolly in <post#=279704>post 279704</post#>, however I just cannot seem to get it to work, I have placed a button on my form and then added

    Sub eMailWorkSheet(sheet2)
    With ActiveWorkbook
    .SendMail Recipients:="whoever@whereever.co.uk"
    Subject = "AcceptedQuoteAttached"
    .Saved = True
    .Close
    End With
    End Sub

    but on clicking the button nothing happens.

    Any ideas please

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: emailing a worksheet (E2000)

    The two lines in your code

    .SendMail Recipients:="whoever@whereever.co.uk"
    Subject = "AcceptedQuoteAttached"

    actually form one instruction. You didn't copy them entirely correctly from Andrew's post. There should be a comma after the e-mail address, followed by a space and an underscore to indicate that the instruction is continued on the next line:

    .SendMail Recipients:="whoever@whereever.co.uk", _
    Subject = "AcceptedQuoteAttached"

    or you can put the entire instruction onto one line (still with a comma after the e-mail address):

    .SendMail Recipients:="whoever@whereever.co.uk", Subject = "AcceptedQuoteAttached"

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: emailing a worksheet (E2000)

    Thank you Hans,

    I have amended the code to reflect your suggestion on putting it on 1 line so the code behind my button now reads

    Sub eMailWorkSheet()
    Sheets("Sheet1Send").Copy
    With ActiveWorkbook
    .SendMail Recipients:="whoever@wherever.co.uk", Subject:="Collection Request"
    .Saved = True
    .Close
    End With
    End Sub

    But when I leave design mode and click the button nothing happens, have I missed the plot as usual

    Cheers


    Stephen

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: emailing a worksheet (E2000)

    The code itself is correct. Are you sure that you have assigned the macro to the button? To investigate this, can you tell me how you created the command button: from the Forms toolbar or from the Control Toolbox?

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: emailing a worksheet (E2000)

    Thanks for the quick response,

    This is the first time I have tried to put a button on a form and used the control toolbox to put the button on the form, I then clicked onto the design tool to go into design mode which automatically brings up the VB code page into which I type that piece of code. When the window opens it shows the first line as

    Private Sub CommandButton1_Click()

    If I leave this though it says that there is a compile error and highlights the line si I deleted it as it did not show in the original example I was copying.

    Hyperlinks work fine in the same workbook.

    Thanks

    Steve

    PS. Is Hoofsdorp always that cold, I was freezing on Friday night walking (staggering) back to (crawling) the hotel.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: emailing a worksheet (E2000)

    OK, go into design mode again (make sure the first button on the control toolbox is depressed).
    Double click the button.
    This should re-create the On Click event procedure:

    Private Sub CommandButton1_Click()

    End Sub

    In the blank line between Private Sub and End Sub, press the Tab key, then type eMailWorkSheet.
    Switch back to Excel, and turn off design mode.
    See if the button works then.

    PS We had some nasty weather at the end of last week, it was unseasonally cold. Like in England, the weather in The Netherlands is undependable. It might be sunny and 25

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: emailing a worksheet (E2000)

    Thanks

    I have done this and it says that there is an error and do I want to go unto the de###### which highlights the line Sheets("Sheet1Send").copy, so I have deleted it and it works, however a warning appears that says something on the lines that an unauthorised attempt to start Outlook has occurred and that it strongly recommends not to proceed, ignoring that and clicking yes sends the email, is that normal?

    If I want to have the button send a different page would I change eMailWorkSheet to eMailWorkSheet2 or whatever the worksheet is called?

    Thanks again

    Steve

    Sub eMailWorkSheet()
    Sheets("Sheet1Send").Copy
    With ActiveWorkbook
    .SendMail Recipients:="whoever@wherever.co.uk", Subject:="Collection Request"
    .Saved = True
    .Close
    End With
    End Sub

    Private Sub CommandButton1_Click()
    eMailWorkSheet
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: emailing a worksheet (E2000)

    If you want to send a different worksheet, you do not need to change the name of the procedure (sub), but the name of the sheet in the procedure.

    If the line Sheets("Sheet1Send").Copy raises an error, the most likely cause is that there is no sheet named Sheet1Send. Replace it by the correct name.

    The warning you get is caused by the draconic e-mail security measures introduced by Microsoft a service pack of Office 2000, to make it more difficult for malware/viruses to send e-mail from Outlook. There are several ways to get rid of the warning - see <post#=390751>post 390751</post#>.

Posting Permissions

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