If not working

[owen4512]

Hi all,

I am having some difficulty with my if function in the below code. I have highlighted in red the part that doesn't seem to be working. The problem that i'm having is the highlighted piece of script should check the value in "E" for Status "Approved" and the body of my email should read something like;

Dear John,

I'm contacting you in regards to....

But instead the body is blank. I bet it's something small i'm missing but would appreciate any help on this one?

Please see https://imgur.com/69DPhQ0

Sub Send_email()

   Dim sh As Worksheet
   Set sh = ThisWorkbook.Sheets("email")
    
   Dim Outlook As Object
   Dim msg As Object
   Dim pic As Picture
   Dim picloc As String
    
   Set Outlook = CreateObject("outlook.application")
   
   Dim i As Integer
   Dim last_row As Integer
   Dim p As String
    
   last_row = Application.WorksheetFunction.CountA(sh.Range("B:B"))
    
   For i = 2 To last_row
   Set msg = Outlook.createitem(0)
    If sh.Range("E" & i) = "" Then
    
   msg.To = sh.Range("B" & i).Value
   msg.bcc = sh.Range("C" & i).Value
   msg.Subject = "Request Status"
    If sh.Range("D" & i) = "Declined" Then
        If sh.Range("G" & i) = "Brand1" Then
        msg.body = "Thank you for contacting Brand1" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & i).Value
        Else
        If sh.Range("G" & i) = "Brand2" Then
        msg.body = "Thank you for contacting Brand2" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & i).Value
        Else
        If sh.Range("G" & i) = "Brand3" Then
        msg.body = "Thank you for contacting Brand3" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & i).Value
[COLOR=#ff0000]    Else[/COLOR]
[COLOR=#ff0000]    If sh.Range("D" & i) = "Approved" Then[/COLOR]
[COLOR=#ff0000]        If sh.Range("G" & i) = "Brand1" Then[/COLOR]
[COLOR=#ff0000]        msg.body = "Dear " & sh.Range("A" & i).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & i).Value[/COLOR]
[COLOR=#ff0000]        Else[/COLOR]
[COLOR=#ff0000]        If sh.Range("G" & i) = "Brand2" Then[/COLOR]
[COLOR=#ff0000]        msg.body = "Dear " & sh.Range("A" & i).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & i).Value[/COLOR]
[COLOR=#ff0000]        Else[/COLOR]
[COLOR=#ff0000]        If sh.Range("G" & i) = "Brand3" Then[/COLOR]
[COLOR=#ff0000]        msg.body = "Dear " & sh.Range("A" & i).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & i).Value[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    End If[/COLOR]
    
   msg.display
   If sh.Range("E" & i).Value = "Sent" Then
    Else
    sh.Range("E" & i) = "Sent"
    End If
    End If
    
Next i


MsgBox "Email has been sent"

 

[Fluff]

Try it like

If sh.Range("D" & I) = "Declined" Then
   If sh.Range("G" & I) = "Brand1" Then
      msg.body = "Thank you for contacting Brand1" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
   ElseIf sh.Range("G" & I) = "Brand2" Then
      msg.body = "Thank you for contacting Brand2" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
   ElseIf sh.Range("G" & I) = "Brand3" Then
      msg.body = "Thank you for contacting Brand3" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
   End If
ElseIf sh.Range("D" & I) = "Approved" Then
   If sh.Range("G" & I) = "Brand1" Then
      msg.body = "Dear " & sh.Range("A" & I).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
   ElseIf sh.Range("G" & I) = "Brand2" Then
      msg.body = "Dear " & sh.Range("A" & I).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
   ElseIf sh.Range("G" & I) = "Brand3" Then
      msg.body = "Dear " & sh.Range("A" & I).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
   End If
End If

 

[owen4512]

Awesome thank you!

 

[Fluff]

You're welcome & thanks for the feedback