open multiple blank emails with vba

[mena139]

i am using the below code to open and email and add the recipient based off cell values. I have been working all day to try to find a way to open 7 of the same emails but have them addressed to different people with one macro.

any tips?

thanks in advance!


Sub Rectangle1_Click()
Sub Mail()
Dim OutlookApp As Object
Dim Mess As Object, Recip As String
Recip = [B8].Value
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = [A1]
.Body = [A2]
.Recipients.Add Recip
.display
End With
End Sub

 

[geodekl]

One option would be to add a for-next loop, and include a select case.
You could also use a set of if-then statements instead of the select case.
This example presumes you want to pull email addresses from a list in column B, starting with row 8 and ending with row 14. If your addresses are elsewhere, or you want more/fewer emails, you'll need to modify the code accordingly.

Sub Rectangle1_Click()
Sub Mail()
Dim OutlookApp As Object
Dim Mess As Object, Recip As String, a as integer

for a = 1 to 7

select case a
     case 1: recip = [b8].value
     case 2: Recip =  [b9].value
     case 3: Recip =  [b10].value
     case 4: Recip =  [b11].value
     case 5: Recip =  [b12].value
     case 6: Recip =  [b13].value
     case 7: Recip =  [b14].value
end select

Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = [A1]
.Body = [A2]
.Recipients.Add Recip
.display
End With

next a

end sub

 

[mena139]

Thanks!!!!
any tips on how to add a cell value in as a cc to the same email?

greatly appreciate the tips!

 

[geodekl]

I usually do something like this:

With objMsg
        .Subject = "your subject here"
        .To = Application.Session.CurrentUser.Name
        .CC = "name@email.com"
        .Body = "your message here"
        .Display
    End With

 

[geodekl]

.To = [B8].value
.CC = [C8].value