Pull a data from excel sheet on vba macro (using excel formula)

[Attrazion]

Hi everyone!

This is the scenario, I want to pull the excel cells data using a excel formula that inside a vba macro code.

Sub SendEmail()

Dim OutApp As Object, OutMail As Object
Dim TestSheet1 As String

'I can pull the data using the excel formula below, but for some reason I need it to put on vba macro code. 

TestSheet1= Worksheets(1).Range("D2").Formula = "=INDEX(B:B,MATCH(TODAY(),A:A,0))"

'I also want to add a condition that if the TestSheet1 Value = blank, then the output should be blank.

Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

With OutMail
        .To = "testTo@gmail.com"
        .Subject = "testsubject@gmail.com"
        .CC = "testemail@gmail.com"
        .Display

        'HTML Body
        .HTMLBody = TestSheet1 & .HTMLBody


    End With

    Set OutApp = Nothing
    Set OutMail = Nothing

End Sub

Output:

Test Name 8

Excel, Sorry I can't download ml2bb since this company asset.

 

[KOKOSEK]

With Application
    TestSheet1 = .WorksheetFunction.Index([B:B], .WorksheetFunction.Match(TODAY(), [A:A], 0))
End With