How to VBA things for weekly email

[cxm687]

Hi all

Learning VBA slowly on my own and I have managed to write a script for me to send multiple emails weekly, however there are 2 things I would like to do….

when referencing a cell in the email body which is a negative value, I would like that text to be red, and a positive value the text to be green.

attach my signature from Outlook to the bottom of each email. Any help with this would be fab!

Current script

Dim Edress As String, subj As String
Dim OutlookOBJ As Object, mItem As Object

Set OutlookOBJ = CreateObject(“Outlook.Application”)
Set mItem = OutlookOBJ.CreateItem(olMailItem)

With mItem

.To
.Cc
.subject
.attachment.add
.Body “text writing here” & sheet(“one”).Range(“A15”).Value & “more text here”
.Send

 

[Domenic]

First build an HTML string to format your value...

'build html string to format value
Dim txt As String
With Sheets("one").Range("A15")
    If .Value > 0 Then
        txt = "<span style='color: #00B050'>" & .Text & "</span>" 'green
    ElseIf .Value < 0 Then
        txt = "<span style='color: #FF0000'>" & .Text & "</span>" 'red
    Else
        txt = .Text
    End If
End With

Then use .HTMLBody instead of .Body...

.HTMLBody = "<p>text writing here " & txt & " more text here.</p>"

Hope this helps!