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:
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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.
 

Forum statistics

Threads
1,141,151
Messages
5,704,579
Members
421,357
Latest member
luke_feeney

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top