Automatic e-mail

[Karthikeyan Devan]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I need to automate an e-amid via excel which will go only to the people who are in Cell B21 and B24.<o></o>
<o></o>
Here are what I am looking for:<o></o>
<o></o>
There is a submit Command button in sheet 4. Whenever a person click on the button in sheet 4 an e-mail should go with attaching the active workbook.<o></o>
<o></o>
The e-mail should go to people whose e-mail address is listed in sheet 2, cell B21 and B24. Any help on this would be greatly appreciated.

 

[VoG]

You should find some suitable code here http://www.rondebruin.nl/sendmail.htm

 

[Karthikeyan Devan]

Hi,

Yes, i went through the below hyperlink earlier and i could find only to send the active workbook. Here it is little differen.

When i click on the command button in sheet 4 the active workbook should attach in the e-mail and the recipient name is listed in sheet 1 from Cell Range (B21:B24). Probably i would be overlooking in the hyperlink which you have provided. Could you please assist me on this.

Any help would be greatly appreciated!!

 

[daverunt]

Hi,

This will send to recipients listed in B21 and B24 only.

Sub Mail_Multiple()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
EmailSubject = "Test"
 
EmailSendTo = Worksheets("Sheet2").Range("B21").Value & ";" _
                    & vbCr & Worksheets("Sheet2").Range("B24").Value & " " & vbCr
 
MailBody = "see attached"
 
   Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        On Error Resume Next
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            '.CC = ccTo
            .Body = MailBody
            .Attachments.Add ActiveWorkbook.FullName
            .Display
            '.Send
End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Karthikeyan Devan]

Thanks a lot! It worked perfectly