Solved Adding If statement for different strbody

September 6, 2016 at 08:28:45
Specs: Windows 7
Hello programming community, need help with adding an if statement
that changes my strbody based on the value of a column of the current
row.

Here is the logic (I used a simple value because I just need to know
how to code the if part):

if column P of current row = "PURCHASE" than strbody = "Hello"

otherwise strbody = "Goodbye"

The complete code is below:

Sub PipelineEmailProcessor()
ActiveSheet.Unprotect
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim Signature As String
Dim Surname As String

Surname = ActiveSheet.Range("E" & ActiveCell.Row).Value
Surname = VBA.Left(Surname, VBA.InStr(1, Surname, ",", vbBinaryCompare) - 1)

Set rng = Nothing
' Only send the visible cells in the selection.
Set rng = Range("h2")

If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected. " & _
           vbNewLine & "Please correct and try again.", vbOKOnly
    Exit Sub
End If

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
   .Display
End With
Signature = OutMail.HTMLBody
strbody = "Loan Type = " & ActiveSheet.Range("P" & ActiveCell.Row).Value & "," & " Status = " & ActiveSheet.Range("G" & ActiveCell.Row).Value & "," & " C&I Date =" & ActiveSheet.Range("Y" & ActiveCell.Row).Value & "," & " Queue =" & ActiveSheet.Range("H" & ActiveCell.Row).Value & "," & " UW = " & ActiveSheet.Range("C" & ActiveCell.Row).Value & "," & " Proc = " & ActiveSheet.Range("B" & ActiveCell.Row).Value & "<br />" & "<br />"

With OutMail
    .to = ActiveSheet.Range("B" & ActiveCell.Row).Value
    .Subject = ActiveSheet.Range("D" & ActiveCell.Row).Value & " " & Surname & " ASD " & ActiveSheet.Range("M" & ActiveCell.Row).Value
    .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & "</p>" & strbody & Signature '& RangetoHTML(rng)
    .Display
End With

On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Protect AllowFiltering:=True
End Sub


See More: Adding If statement for different strbody

Reply ↓  Report •


#1
September 6, 2016 at 11:19:48
re: if column P of current row

What do you mean by "current row"? Do you mean the ActiveRow?

I'm also a little confused by this snippet:

Set rng = Nothing
' Only send the visible cells in the selection.
Set rng = Range("h2") '<--------------------------

If rng Is Nothing Then '<--------------------------
    MsgBox "The selection is not a range or the sheet is protected. " & _
           vbNewLine & "Please correct and try again.", vbOKOnly
    Exit Sub
End If

If you are setting the rng to "H2" within the code, when will the "If rng Is Nothing" statement be true?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
September 6, 2016 at 12:25:15
DerbyDad03 thanks for the quick response, I copied this code from a forum but honestly I don't understand it all the way so it would not surprise me if that piece of code does nothing. I have modified for it to work with what I need and it does.

Yes I mean current row, that is what this piece of code references "ActiveCell.Row". So my strbody code grabs the value form different columns but using the current row. The code works perfectly for me, I just need it to display something different when column P = "PURCHASE" of the current row.

message edited by mecerrato


Reply ↓  Report •

#3
September 7, 2016 at 05:54:25
✔ Best Answer
It's almost as simple as you wrote it. There are a number of options for the syntax/layout, but they all do the same thing. It really just depends on what
you want your code to look like.


if column P of current row = "PURCHASE" than strbody = "Hello"
otherwise strbody = "Goodbye"


   If Range("P" & Activecell.Row) = "PURCHASE" Then strbody = "Hello" _
     Else strbody = "Goodbye"

or

   If Cells(ActiveCell.Row, "P") = "PURCHASE" Then strbody = "Hello" _
     Else strbody = "Goodbye"

or

   If Cells(ActiveCell.Row, 16) = "PURCHASE" Then strbody = "Hello" _
     Else strbody = "Goodbye"

or

   If Range("P" & ActiveCell.Row) = "PURCHASE" Then
     strbody = "Hello"
   Else: strbody = "Goodbye"
   End If

or

   If Range("P" & ActiveCell.Row) = "PURCHASE" Then
     strbody = "Hello"
   Else
     strbody = "Goodbye"
   End If
   

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
September 7, 2016 at 06:06:32
re: I copied this code from a forum but honestly I don't understand it all the way so it would not surprise me if that piece of code does nothing. I have modified for it to work with what I need and it does."

I know I really don't have a say in any of this, but I do have an opinion.

I find it somewhat disconcerting that someone would use copied VBA code to run a business without understanding what the code does. I'm not saying that one needs to know the inner workings of code that is supplied by a vendor based on stated requirements, but when the person in charge of actually supplying that code doesn't even understand how it works, that troubles me.

If someone doesn't understand how their "own" code works, how can they be expected to support it and/or modify it when things go wrong or requirements change?

message edited by DerbyDad03


Reply ↓  Report •

#5
September 7, 2016 at 16:06:24
thank you that worked

Reply ↓  Report •


Ask Question