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).

'---------------------------------------------------------------------------------------
' Procedure : send_mails_for_workbook
' Author    : Charlize
' Date      : 1/09/2008
' Purpose   : Process every sheet in workbook exept 'Master Sheet' and 'Data'
'---------------------------------------------------------------------------------------
'
Sub send_mails_for_workbook()
'workbook
Dim mywb As Workbook
'worksheet
Dim mysheet As Worksheet
'name of worksheet we look for to get emailaddress
Dim myvalue As Range
'the mailaddress
Dim mymailaddress As String
Set mywb = ActiveWorkbook
'For each sheet in workbook exept Master Sheet and Data
For Each mysheet In mywb.Worksheets
    If mysheet.Name <> "Master Sheet" And _
       mysheet.Name <> "Data" Then
       'Define the range where we need to look for name
        With mywb.Worksheets("Master Sheet").Range("A2:A" & _
             mywb.Worksheets("Master Sheet").Range("A" & _
             Rows.Count).End(xlUp).Row)
            'Search for name
            Set myvalue = .Find(mysheet.Name, LookIn:=xlValues)
                'if name is found
                If Not myvalue Is Nothing Then
                    'store offset value in string
                    mymailaddress = myvalue.Offset(, 1).Value
                    'change this line with your routine for sending mail
                    MsgBox "Send mail for " & mysheet.Name & vbCrLf & _
                           "at " & mymailaddress
                End If
        End With
    End If
'go on with next sheet
Next mysheet
End Sub

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