Using VBA If In HTML Body

[TigerTom]

Hi All,

As part of a bigger macro, I'm trying to get the macro to write and e-mail and I want the e-mail content to depend on a cell value in a worksheet.

I.e If cell is greater than 5, "no need to re-order", if cell is less than 5, "please place an order.

This is my test code below. I can get it to compile, probably a silly syntax error somewhere.

Any help gratefully received.

Sub IfTest()

Windows("Test.xlsx").Activate


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")
'
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'
olMail.To = "test@test.com;"
olMail.Subject = "Test"
olMail.HTMLBody = "****** style=font-size:11pt;font-family:Calibri>" & _
"<p>If test below</p>" & _
"<p></p>" & _
If Range("A23") < 5 Then
("<p>Some e-mail text.</p>")
Else
("<p>Some other e-mail text.</p>")
End If
"</BODY>"


End Sub

Thanks,

Tiger

 

[TigerTom]

Sorry hope this is a bit clearer

Sub IfTest()


Windows("Test.xlsx").Activate


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")
'
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'
olMail.To = "test@test.com;"
olMail.Subject = "Test"
olMail.HTMLBody = "****** style=font-size:11pt;font-family:Calibri>" & _
"<p>If test below</p>" & _
"<p></p>" & _
If Range("A23") < 5 Then
("<p>Some e-mail text.</p>")
Else
("<p>Some other e-mail text.</p>")
End If
"</BODY>"


End Sub

 

[Norie]

Perhaps.

olMail.HTMLBody = "****** style=font-size:11pt;font-family:Calibri>" & _
    "HTML stuff " & _
    IIf(Range("A23") < 5, "Some e-mail text.", "Some other e-mail text.") & _
    "More HTML Stuff"

 

[TigerTom]

Thank you Norie, that would work.?

If I wanted to make a list of these can I have a go to next if the result is false?

Or should I just put the false part in double "" to leave it blank.

Thanks again,

Tiger.

 

[Norie]

A list of what?

 

[TigerTom]

Sorry didn't explain that we'll.

I have 15 cells I need to perform the same if test on, flagging up any true results in the email.

Was wondering what was cleanest way to have false result empty and move onto next if statement.

Tiger.

 

[Norie]

Do IF formulas in adjacent cells and pull the result?

=IF(A23<5, "Some e-mail text", "Some other e-mail text")