MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA code for different recipients in Outlook

  Asked By: Alesia    Date: Dec 09    Category: MS Office    Views: 2106

I want send an email (Outlook) with different recipients that are in a particular worksheet (Mail)in range a3:a21.
What am I doing wrong ?
This is the code I used (just the part that does not work) :

Dim oApp As Object, OutApp As Object, OutMail As Object
Dim emailRng As Range, cl As Range
Dim sTo As String

Set emailRng = Worksheets("Mail").Range("a3:a21")

For Each cl In emailRng
sTo = sTo & ";" & " " & cl.Value

sTo = Mid(sTo, 2)
' Create the mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = Worksheets("MAIL").Cells(3, 3).Value

On Error Resume Next
With OutMail
.To = sTo
.CC = ""
.BCC = ""
.Subject = "x "
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0

MsgBox "Mail sent.", vbInformation

Set OutMail = Nothing
Set OutApp = Nothing

End Sub



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on VBA code for different recipients in Outlook Or get search suggestion and latest updates.