Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morning all

    Usually when I want to email from Excel I defer to Ron de Bruin's excellent examples RonDeBruin_Emails, however I cannot find an example of what I am looking for and do not know if it is even possible.

    I have 4 worksheets

    CustomsInvoice
    NonHazCert
    PackingLists
    Declaration

    I would like to be able to have a button that when pressed would offer me a choice of 1, 2,3 or all of the worksheets for email and once selected add them as an attachment to a blank email

    Does anybody know if this is possible?
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached sample workbook. The code behind the command button uses a slightly modified version of Ron de Bruin's code.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    Wonderful, thank you, except I emailed a load of junk to Ron without thinking

    Another piece of advice if you don't mind. The worksheets may be emailed to different people so I thought I would try to change the part .sendmail to

    With OutMail
    .to = ""
    .CC = ""
    .BCC = ""
    .Subject = "Shipping Documents Attached"
    .Body = "Hi there"

    I copied this from another piece of Ron's code for adding as an attachment but it does not seem to work in this scenario

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It's not as simple as that - you can't just copy bits of code from one procedure and paste them into another and expect them to work.

    The macro you copied from uses Outlook VBA instead of Excel's SendMail command, so you need to copy more than just the bit you quoted.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767536' date='26-Mar-2009 12:18']It's not as simple as that - you can't just copy bits of code from one procedure and paste them into another and expect them to work.

    The macro you copied from uses Outlook VBA instead of Excel's SendMail command, so you need to copy more than just the bit you quoted.[/quote]

    Thanks Hans

    Its all working now, I found that all I needed to do was take out the rondebruin emailaddress and leave the "" and when the button is clicked it opens up MS outlook so that I can manually enter the email addresses

    Thanks again
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, as usual I spoke too soon

    I am now trying to implement this in my Workbook and will place the button etc. on a woksheet called index

    With Sourcewb
    Set TheActiveWindow = ActiveWindow
    Set TempWindow = .NewWindow
    For i = 1 To 4
    If Not Worksheets("Index").Range("B" & i) = "" Then
    n = n + 1
    ReDim Preserve arr(1 To n)
    arr(n) = Worksheets("Index").Range("A" & i)
    End If
    Next i
    End With

    As you can see I have change the sheet name but I can't for the life of me see what the i refers to and how I can point it to where I am starting from which is the names in A21-A24 and the x's to go in B21-B24

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The i is the row number. So if your names and x-es are in rows 21-24, use

    For i = 21 To 24

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767564' date='26-Mar-2009 14:05']The i is the row number. So if your names and x-es are in rows 21-24, use

    For i = 21 To 24[/quote]

    Hi Hans

    I had already done that after I posted but it still bugs out on the same line

    If Not Worksheets("Index").Range("B" & i) = "" Then

    ?

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please clarify "bugs out".

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='767574' date='26-Mar-2009 14:25']Please clarify "bugs out".[/quote]


    Hi Hans

    It stops the code from running, opens the VB and highlights that line in yellow.

    However, not to worry too much because I inserted another page, called it Sheet1 and just copied the code over exactly as you had it and used cells A1:B4 and it works fine.

    It would be nice to know for future projects though if it would work in different locations. I will kepp playing with the copy that I have

    Many thanks as usual
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It should work with any sheet name. I suspect that you didn't spell the name of the worksheet correctly - perhaps there was a space before or after Index in the name of the sheet?

Posting Permissions

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