CODEWITHSUNDEEP
excelvba
TheReddsable
TheReddsable

Reputation: 67

VBA Excel include a IF statement in email form

I can't seem to include a IF formula in my code, in the part where the email is being sent to some people included in column R2 of my Excel file

I would like to exclude from .CC the email [email protected] if this user is already in column R2 (so in .To). How could I write the code? Otherwise, my user [email protected] is receiving the email twice, once because the user is in .To and once because he is in .CC

.To = Worksheets("MyData").Range("R2").Value
.CC = "[email protected]; [email protected]; [email protected]"
.BCC = ""
.Subject = "TEST"
.Body = "Hello"
.Attachments.Add ThisWorkbook.Path & "\ME\" & Check & " " & Format(Now, "dd-mmm-yy") & ".xlsx"
.Display
'.Send

What could be the code to do that?

Upvotes: 1

Views: 341

Answers (4)

Asger
Asger

Reputation: 3877

By this the addresses are trimmed and compared case insensitive:

    .To = Worksheets("MyData").Range("R2").Value
    .CC = ""
    Dim check As Variant
    For Each check In Split("[email protected]; [email protected]; [email protected]", ";")
        If Not InStr(1, .To, Trim(check), vbTextCompare) > 0 Then _
            .CC = .CC & "; " & check
    Next check

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Just use Replace to remove the address in .To from your list in .CC

.To = Worksheets("MyData").Range("R2").Value
.CC = Replace("[email protected]; [email protected]; [email protected]", .To, vbNullString)

Upvotes: 0

Tom
Tom

Reputation: 9878

I wouldn't use an if statement for this. Instead I would remove each recipient from the cc line after setting both

.to = "[email protected]"
.cc = "[email protected]; [email protected]; [email protected]"
For Each addr In Split(.to, ";")
    .cc = Replace(.cc, addr, vbNullString)
Next addr
.BCC = ""
.Subject = "TEST"
.Body = "Hello"
.Attachments.Add ThisWorkbook.Path & "\ME\" & Check & " " & Format(Now, "dd-mmm-yy") & ".xlsx"
.Display

The for loop splits the to line on the semi-colon character ; and then loops through each one removing it from the .cc This will handle both single and multiple recipients in both the .To and .CC line

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

You mean like this:

If Worksheets("MyData").Range("R2").Value2 = "[email protected]" Then
    .CC = "[email protected]; [email protected]"
Else
    .CC = "[email protected]; [email protected]; [email protected]"
End If

Upvotes: 0

Related Questions