Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Help required for Excel to email

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location

    Help required for Excel to email

    Hi

    first can I say hello to everyone ,I am new to this site. and am hopimg that there are loads of friendly users that will be able to help me with my skill development.


    anyway I am wondering if it's possible for my sheet to sends emails out automatically to the right person depending on the area.

    Hospital & Wards tab

    for instance Beatson-Ward B3 (REG) Oncol would send the data from sheet 1 (which is the data from pivot) to the email address that matches Beatson-Ward B3 (REG) Oncol in the contact tab

    if you can help this would save me loads of time at work

    Thanks
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi Dougers1
    Depending on what you are using to transport the e mail "Outlook" or ?
    This website will get you started
    http://www.rondebruin.nl/

  3. #3
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Rob Outlook 2010

    I have little knowledge of vba - I learned a little pascal a long time ago

  4. #4
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi Dougers1
    Save duplicating all the code
    Ron de Bruin website has got all the nec vba for sending emails from outlook well worth a visit
    Take a look you can always come back if you need anymore help ok
    Rob

  5. #5
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Thanks Rob- will give it ago

  6. #6
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi Doug
    They have moved your Post to here ???
    I Have created a user form on the Contacts Sheet for you to select location and this will automatically bring in the e mail address
    See attached have tried it works ok, couple of points you need to change the file path as below and also if you want to change the subject & text on email you will have to update that part of code, have marked the area as such ok
    The code can be found in Send E Mail button

    '===================================================
    '   User needs to change temp file path name !!!!!!
    '===================================================
        TempFilePath = "C:\DOUG\"
    E mail subject & text
    .Subject = "Hospital Ward Updates"                                  '   This text can be amended as required
    .Body = "Please find attached the Revised Ward Updates"   '   This text can be amended as required
    Rob
    Attached Files Attached Files

  7. #7
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Rob

    it gives me this error(see attached) , I have added my path to where I save the excel sheet is this correct
    Attached Files Attached Files

  8. #8
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi David
    Have made some changes to the routine
    There is no need now to specify file location as it will create a copy to temp file then after e mailing it, it will delete it keeps system tidy
    Have attached new file ok give it a try
    Rob
    Attached Files Attached Files

  9. #9
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Hi Rob

    this looks good and I can now send the email , only problem I have is that no matter what contact(ward) I choose it sends the same email sheet 1 (beatson).if you can fix this that would be exactly what im looking for.

    If one ward has more than one row of information will both pieces of data be emailed , reason I'm asking is I see that on the contacts tab there are duplicates wards

    thanks again
    David
    Last edited by Dougers1; 03-09-2019 at 04:45 AM.

  10. #10
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi David
    I thought you only wanted sheet1 sending
    you will have to be exact on what sheet you want where for what dept, then I will know which one to select or ask the question so you can select which sheet
    So you need to do me a list by dept and the sheet you want sending
    Rob

  11. #11
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Hi Rob

    sorry im not the best at getting things across, what i am looking for is if i select a specific ward from the location option on the userform (say gri ward 18/19) it will populate the data behind the pivot for this ward andemail it to the contact

    i have found a way to for this to work - if i delete sheet1 and click on the pivot total for the sprcific ward it will create new sheet with the correct data, if i rename the sheet sheet1 it works

    just wondering if there wasanything you could do to cut this step out and it auto select the specic ward selected to sheet 1

    hope this makes sense

    David

  12. #12
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    David
    If the pivot tables never changed then there is a way to do that but the problem is a soon as you filter any item then the calc's for the cell or range reference goes out of the window they are never static
    for example Beatson total sits in C5 on the pivot if I filter it with another loc it then doesn't show on the pivot !!!!

    There is another way that is to look at the raw data & pull the information from there and put it into sheet1, you would have to split Data1 & Data2 so the time is separate column
    Have a think and let me know ?
    Rob

  13. #13
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Hi Ron

    the data would change, every week I run a report that gives me around 300 rows, I only set up the pivot as it was easier for my self to double click on the totals and create new sheet with the data inserted.

    if you can do it from the raw data then that's brill, the columns data 1 and data 2 can be split , if I do a text to column in data 1 and spilt so it has the date in data 1 and start time in data 2 and insert new column for end time would this work.

    Thanks
    David

  14. #14
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi David
    We'll extract the data from the raw data then if you can modify the rawdata sheet to a finished version then repost the file that way we don't have to mod it again.
    Persuming you want all the fields to show the same as raw data layout?
    Doing it this way, you wont need the pivot tables, is it only you that inputs data into that sheet?
    Rob

  15. #15
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Hi Rob

    attached is what the raw data will look like, yeah it is just me who inputs the data

    Thanks
    Attached Files Attached Files

  16. #16
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi David
    Have done the revamp, some points worth noting:

    1 Have changed sheet names
    2 The contacts must match what's on the Database because that's what we are searching for
    3 You Need to update the email address's
    4 DO NOT FILTER ANYTHING ON THE MAIN DATABASE

    What I have done is to copy main database to EMData sheet
    then it will delete anything that does not match your selection from the dropdown on Contacts
    Then it e mails the data what is left, if there are any duplicate names on the database it will include these as well

    Let me know
    Rob
    Attached Files Attached Files

  17. #17
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    on sheet Contacts it has the location of the roster this should be the substantive post column J


    it's the substantive post that I would email the data too.
    looks really good though
    Last edited by Dougers1; 03-10-2019 at 02:29 PM.

  18. #18
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi David
    Have changed contacts to same as col J on database
    & updated routine so it looks at col J to search ok
    Rob
    Attached Files Attached Files

  19. #19
    VBAX Regular
    Joined
    Mar 2019
    Posts
    11
    Location
    Thanks Rob

    This is brilliant, i tried to alter the Body of the email txt but it wont work as its several lines i guess. i have sent you a PM

    thanks again
    David

  20. #20
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Hi David
    Have added text but cannot send file back by PM
    Have input all XX for phone number you just need to change the X to correct no
    when you have corrected them then go to debug and compile and then save the file ok
    Rob
    Attached Files Attached Files

Posting Permissions

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