• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using IF statements to determine .to in auto email

B

Biglou1221

New Member
So, even though I'm VERY limited on coding knowledge, thanks to user chirayu my auto email is up and running. I reworked parts of it for another project, but have reached another stopping point. What I'm attempting is to use is an If statement to determine the recipient of an auto-email. Is this possible? What I've got looks like:

Code: 
On Error Resume Next
         With OutApp.CreateItem(0)
            If Range("A", ActiveCell.Row).Value = "1" Then
            .to = "user1@mail.com" 
                ElseIf Range("A", ActiveCell.Row).Value = "2" Then
                .to = "user2@mail.com" 
                     Else
                     .to = "user3@mail.com"
                    End If
            .CC = "Who I. Am@mail.com"
            .BCC = ""
            .Subject = "Currency Almost out of Date" 'change this to whatever you want
            .Body = MyText
            .Display   'or use .Send to send the email automatically
        End With
        On Error GoTo 0

All it's entering for .to though is "user1@mail.com". Which leads me to believe it sees the initial .to = "user1@mail.mil" and quits running through conditions. These emails won't change, so I thought it would be easier just to include them in the code. Every other line is working perfectly and it IS creating emails, just not based on the above conditions. I appreciate any help you can provide.
 
Hi Again. You need to do the conditions outside the email bit so you should use a variable. Something like this.

Code: 
Dim MyTo As String

If Range("A", ActiveCell.Row).Value = "1" Then
    MyTo = "user1@mail.com"
ElseIf Range("A", ActiveCell.Row).Value = "2" Then
    MyTo = "user2@mail.com"
Else
    MyTo = "user3@mail.com"
End If

On Error Resume Next
    With OutApp.CreateItem(0)
        .To = MyTo
        .CC = "Who I. Am@mail.com"
        .BCC = ""
        .Subject = "Currency Almost out of Date" 'change this to whatever you want
        .Body = MyText
        .Display  'or use .Send to send the email automatically
    End With
On Error GoTo 0
 
I had previously tried that as well but couldn't get it to work. As usual, you once again set me straight. You sir are a god among men (although I'm sure this is child's play for you). Thanks again!
 
Oh no wait, I just realized my mistake as well lol. I read the code again & know why your code didn't work before. Silly me - I just copy pasted the parts to incorporate the variables from your code without replacing "," with "&"

replace:
Code: 
Range("A", ActiveCell.Row).Value

with:
Code: 
Range("A" & ActiveCell.Row).Value

Because ActiveCell.Row gives the Row Number.
So if you're on cell A1 then:
- Range("A" , ActiveCell.Row).Value will give an error because its not a cell reference. You're essentially saying A,1 rather than A1
 
You must log in or register to reply here.
Back
Top