IF statements in VBA

Hello All

A while ago, you guys helped me solve an issue with sending mail from Excel. jaslake came up with a solution that has worked brilliantly since (below), but I want to improve the workbook that it sits in by removing a repetitive formula and putting its equivalent in VBA coding instead. Each row has a button that opens Outlook allowing you to send the email. The email contents are defined by what is written in the other cells on each row, hence the offsets etc below.

Sub Send_Mail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim MyButton As String
    Dim strTo As String
    Dim Subject As String
  
    MyButton = ActiveSheet.Shapes(Application.Caller).Name

    With ActiveSheet.Buttons(MyButton)    'Form Control
        strTo = Range(.TopLeftCell, .BottomRightCell).Offset(0, -2).Value & "@email.com;"
        Subject = Range(.TopLeftCell, .BottomRightCell).Offset(0, -1).Value
                             
        End With
            
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = strTo
        .CC = ""
        .BCC = ""
        .Subject = Subject
        .Body = ""
        .Display   'Send   'or use .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
        
    With ActiveSheet.Buttons(MyButton)
    Range(.TopLeftCell, .BottomRightCell).Offset(0, 1).Value = Now()
        With Range(.TopLeftCell, .BottomRightCell).Offset(0, 1)
        .Value = Time()
        .NumberFormat = ("h: mm" & "z")
        End With
    End With
    
    Exit Sub
            
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

The Subject line

Subject = Range(.TopLeftCell, .BottomRightCell).Offset(0, -1).Value

refers to a cell in the worksheet containing the following formula:

=UPPER("HELLO"&". "&IF(E2>"",E2,A2)&"."&" "&C2&"-"&D2&IF(F2="",". PROFIT:"&B2&"£.",IF(F2>0,"RUNNING:"&F2&"."))&" "&"PLS CALL THE OFFICE."&" EXO OPS")

...and it is this formula that I want to remove from the sheet and put into VBA form, and I'm struggling. I think I can either have the code in a new macro, or I can have the code written in the existing procedure, but I just can't get it to work.

If you guys could help out, it would be brilliant!

cheers

Adam