Excel ".SendMail" to multiple recipients

CompQueen

New Member
Joined
Aug 2, 2006
Messages
13
I have built a VBA that sends reports to a bunch of people. Each report is unique and goes to four people. I have figured out how to do everything but get the VBA to allow multiple e-mails. Below are some codes that I have used for this project:

(The special e-mails are pulled using a VLookup and placed in cells F1, F2, F3, F4)

--VBA --
MANAGER1 = Worksheets("MASTER_SHEET").Range("F1").Value
MANAGER2 = Worksheets("MASTER_SHEET").Range("F2").Value
MANAGER3 = Worksheets("MASTER_SHEET").Range("F3").Value
TOP_MANAGER = Worksheets("MASTER_SHEET").Range("F4").Value

....

(This part keeps erroring out)


With wb2
.SendMail MANAGER1; MANAGER2; MANAGER3; TOP_MANAGER, _
"Performance Management"

Please help me rewrite the ".SendMail" line - I think this is the problem.

It works if I put .SendMail MANAGER1, _

I will be forever greatful if you can give me the correct code.

Thank you,

Bonnie :eek:
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

CompQueen

New Member
Joined
Aug 2, 2006
Messages
13
I have already been there. I will repost the question. Please allow someone to reply with an answer
 

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
Code:
Sub Send_EMail()

'Microsoft Outlook Object Library XX.0 Must be selected.

    Dim Outlook_Application As Variant
    Dim BodyOfTheEMail As Variant
    Dim Mng1
    Dim Mng2
    Dim Mng3
    Dim TMng
    Dim Tos As String
    Dim Seperator As String
    Dim Outlook_EMail As Variant
    
        Mng1 = [F1]
        Mng2 = [F2]
        Mng3 = [F3]
        TMng = [F4]
        
        Seperator = ";"
        Tos = Mng1 & Seperator & " " & Mng2 & Seperator & " " & Mng3 & Seperator & " " & TMng
        
        Set Outlook_Application = CreateObject("Outlook.Application")
        Set Outlook_EMail = Outlook_Application.CreateItem(0)
        BodyOfTheEMail = "Your Message" & vbNewLine & vbNewLine & "Row 1" & vbNewLine & "Row 2" & vbNewLine & "Row 3"
        
            On Error Resume Next
                
                With Outlook_EMail
                    .To = Tos
                    .CC = ""
                    .BCC = ""
                    .Subject = "Subject Of The E-Mail"
                    .Body = BodyOfTheEMail
                    .Display
                    .Send
                End With
                
            On Error GoTo 0
        Set Outlook_EMail = Nothing
    Set Outlook_Application = Nothing
        
End Sub
 

tstevens

New Member
Joined
Sep 1, 2006
Messages
29
I can never get outlookEmail to work for me but to send to multiple recipients using sendmail I create an array like this

Dim list(2) as string
list(0) = "xxx@mail.com"
list(1) = "xxx@mail.com"
list(2) = "xxxx@mail.com"
activeWorkbook.sendMail list,

However I don't know how to add to the body of the email using sendmail.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,908
Messages
5,541,529
Members
410,547
Latest member
htran4
Top