VBA Send Email based off cell value

[nivster]

Hi everyone!

I am not familiar with VBA at all, so I'll appreciate all responses

I am trying to send an email address based off the cell value in my excel sheet. The body of the email will change depending on what the value is. If the value is under $0, the body of the email will be different than if it is over $0.

I tried looking for if then examples, and see if those would work but I'm not sure if this is the best solution.

I'd appreciate any information or steps that would help me get this

 

[Michael M]

Maybe like this

Sub MM1()
Dim OutApp As Object, OutMail As Object, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
     If Cells(1, 1) < 0 Then ' looks at cell A1
         strbody = "cell value is less than $0 "
        Else
        strbody = "cell value is greater than $0 "
    End If
With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = strbody
    .Display   'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[nivster]

Maybe like this

Sub MM1()
Dim OutApp As Object, OutMail As Object, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
     If Cells(1, 1) < 0 Then ' looks at cell A1
         strbody = "cell value is less than $0 "
        Else
        strbody = "cell value is greater than $0 "
    End If
With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = strbody
    .Display   'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Do you know how I can input the actual cell value into the email body? So, for example, if it was $100 in cell A1, how can the email body state "There were $100 gains today"

 

[Michael M]

This

Sub MM1()
Dim OutApp As Object, OutMail As Object, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
     If Cells(1, 1) < 0 Then ' looks at cell A1
         strbody = "Your current balance is $" & Cells(1, 1).Value
        Else
        strbody = "Your current balance is $" & Cells(1, 1).Value
    End If
With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = strbody
    .Display   'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[nivster]

Thank you so much! This is going to make work so much easier. Truly appreciate it :D

 

[Michael M]

Glad to help...

 

[Sha]

So sorry i am new at VB coding too and i have question....the solution above only reads value from 1 cell.
what if you want to retrieve a certain value from a range of cells?

 

[Michael M]

what values and what range of cells.
What are the required conditions?

 

[Sha]

Hi Michael M,

I actually managed to find the answer to my earlier question.

thank you

 

[Michael M]

Ok, glad you got it worked out...