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

Probably this suffices:

 
Sub Send_Mail()
  sn=ActiveSheet.Buttons(Application.caller).TopLeftCell.Offset(,-2).resize(,2)
  With CreateObject("Outlook.Application").CreateItem(0)
    .To = sn(1,1) & "@email.com"
    .Subject = sn(1,2)
    .Send
  End With
 
  ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(,1).Value = format(Now,"h: mm z")
end sub

check if this is what you want.

msgbox ucase("hello. " & iif([E2]="",[A2],[E2]) & ". " & [C2]&"-"&[D2]&IIf([F2]="",".profit: "&[B2]&"£",,"RUNNING: "&[F2])&". please call the office. exo ops.")

That works very well, thanks! What I am doing at the moment is replacing the absolute cell references with offset references to allow the single piece of code to manage each of the 150 buttons, so this is what I am using:

UCase("HELLO. " & IIf([Range(.TopLeftCell, .BottomRightCell).Offset(0, -12)] = "", [A2], [E2]) & ". " &..........

In the above Ive so far just replaced the first reference [E2] with the range.offset bit...

...but it doesn't like it, so I'm wondering what I'm doing wrong?

thx

***EDIT: Looks like I've managed to solve it myself with

UCase("HELLO. " & IIf(Range(.TopLeftCell, .BottomRightCell).Offset(, -12) = "",

Just had to get rid of the square brackets.
Many, many thanks for your help snb!!!!