Help required for Excel to email

[Dougers1]

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

[Rob342]

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/

[Dougers1]

Rob Outlook 2010

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

[Rob342]

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

[Dougers1]

Thanks Rob- will give it ago

[Rob342]

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

[Dougers1]

Rob

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

[Rob342]

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

[Dougers1]

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

[Rob342]

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

[Dougers1]

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

[Rob342]

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

[Dougers1]

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

[Rob342]

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

[Dougers1]

Hi Rob

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

Thanks

[Rob342]

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

[Dougers1]

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 :thumb

[Rob342]

Hi David
Have changed contacts to same as col J on database
& updated routine so it looks at col J to search ok
Rob

[Dougers1]

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

[Rob342]

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

[Dougers1]

Rob

This is perfect Thanks:thumb

[Rob342]

Glad to help
Don't forget to mark the post as solved
Rob