send sheet as an email based on the sheet name.

Hi,
same question posted here http://www.mrexcel.com/forum/showthread.php?t=338495

Hi,

I have about 500 sheets in excel. each sheet is named with the managers name. John, Jim, Etc, each sheet has to be emailed as an attachment to the respective manager, That is sheet John has to be email to [email protected], Sheet Jim has to be emailed to [email protected] etc.,

I have all the sheet names and their email address in one master sheet.
So some kind of vlookup has to be done between the sheet names and the email address.

When I run a macro 500 emails has to be sent according to the sheet names.
or
A userform will help me.
there should be 2 list boxes one should take all the sheet names from mastersheet, and another list box should take all the email addresses.
So I can select manually the sheet which i wanted to email as an attachment and the email address.

example:
list box one will have, SheetJohn, SheetJim, SheetMercy etc., etc(Data from mastersheet Column A)
List box two will have, [email protected], [email protected], [email protected] etc( data from Mastersheet Column B)
and there should be one command button send mail.
If I select SheetJohn from list box 1 & Select
[email protected] in list box 2 and then I click Send, only that sheet has to be sent as an attachment to that email address.

the subject is one line : "Outstandnig Invoice"
The body of the message is : Hi,

"Test test test test test test "

I dont know if this is possible... But Appreciate your helps.

Not sure why you want two listboxes. One with the sheetnames and one with the e-mailadresses. If every sheet has it's own address, why do you want to choose the address.

I wouldn't use a listbox for mailing a reminder mail. Why not a button that will mail the activesheet. The button will need to do the following :

1. look for mailaddress based on sheetname (offset)
2. save the activesheet as a new workbook
3. create a new mail with the address from no 1
4. attach the workbook
5. send the mail to receiver
6. send the mail to yourself

Charlize

Hi

Thanks for your reply,

It is not necessary that i need a list boxes. I just need to send all the sheets to their respective email address when i click the button.
I found the macro for sending the active sheet in http://www.rondebruin.nl/mail/folder2/mail2.htm
but i need to type the email address for each email in the macro for each sheet.

As you said.

1. look for mailaddress based on sheetname (offset)
2. save the activesheet as a new workbook
3. create a new mail with the address from no 1
4. attach the workbook
5. send the mail to receiver


I have attached a sample file for the rerefence.

Hopefully this will give you some ideas. The coding for sending mails have to be implemented yet (instead of my messagebox -- you could use a call to the sendmail routine and pass the email address to this function).

Please Login or Register  to view this content.

Charlize

Hi,

Thanks Charlie for the help, but I dont understand
The coding for sending mails have to be implemented yet (instead of my messagebox -- you could use a call to the sendmail routine and pass the email address to this function).
I am completely new to the VBA coding, I know just to make changes like sheet names, ranges etc., I am reallly sorry if I am troubling you much.

I am using outlook 2003.
The subject line and body of the message remains same for every email.

I would really appreciate your help me on this..

I am completely new ...

Who wrote that other piece of code to process the data sheet into separate sheets using the 'advanced filter' coding ?

Have you tried stepping through my coding using F8 and have you read my comments in between to see what the code actually does ?

Charlize

Thanks for your reply,

actually i did not write that code it was written by a board member, the thread is here http://www.mrexcel.com/forum/showthread.php?t=338885

I have gone through the coding which was written by you, i can understand the logic, but do not know how the code works.. If I know to write the code i could have written before posting it...

I can understand the logic, but really do not know to write the code..
I always get help the forum, and make changes on the code if I know to make...

I am really sorry if I am troubling you much....

Here the complete coding for your problem. You may use it for all your future projects but I kindly ask you to leave the comments and credits between the coding lines intact.

I also noticed that you are moving around forums since 2006. Try adapting code you found (most with explanations) to suite your needs. This is the best way to learn vba (on a back-up copy off course).

Anyway, enjoy the routine and see you around.

Charlize

Please Login or Register  to view this content.

Hello Charlie...

Its perfect...thanks a lot for your help...

A quick question on this again...

the code .Body = "Dear " & Split(myworksheetname, ",")(0) & _
splits the sheet name based on the first name. example, Burden, Mr. Matt

the code is splitting the name to Burden, this is fine, but actually Matt is the first name. when I am extracting a report from a database, the sheet names are interchanged.

So is it possible to split the last name of the sheet in to the code
.Body = "Dear " & Split(myworksheetname, ",")(0) & _
so the output looks like this
Hi Matt,

thanks once again for your help..

I believe this will work for you. Hit F1 when you are standing on the word Split and take a little time to read the info.

Please Login or Register  to view this content.

Charlize

Hi,

Thanks charlie.. this is perfect...