OllieHiggins
New Member
- Joined
- Apr 27, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi - I have an action tracker that I want to send out to the action owners. I want to make them aware of their entire actions (even the ones that are not yet due) by attaching the file itself to the email, but i also want to highlight the overdue actions in the body of the email.
I have used the RangetoHTML code from Ron de Bruin's site, but to be honest I don't really follow it, so wonder if it might be something to do with that?
Anyway, my current code is as below. This generates the email, attaches the file and copies the table into the body of the email as it is in the workbook. The missing piece is isolating only the overdue actions in the body.
I had tried to put in an IF formula into this, but it didn't seem to change anything strangely. Still generated the email, but with all rows, regardless of overdue status (i defined Status and StatusCol as Range)
I have used the RangetoHTML code from Ron de Bruin's site, but to be honest I don't really follow it, so wonder if it might be something to do with that?
Anyway, my current code is as below. This generates the email, attaches the file and copies the table into the body of the email as it is in the workbook. The missing piece is isolating only the overdue actions in the body.
VBA Code:
Dim emailApplication As Object
Dim emailItem As Object
Dim str1, str2 As String
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
str1 = "<BODY style = font-size:12pt;font-family:Calibri>" & _
"Hi,<br/><br/> email body. <br>"
str2 = "<br> Thanks, <br>"
Set StatusCol = Range("T7:T200")
On Error Resume Next
With emailItem
.To = Range("P7").Value
.Subject = "Internal Audit Action(s) Follow Up " & Format(Date, "dd/mm/yy")
.Attachments.Add ActiveWorkbook.FullName
.HTMLBody = str1 & RangetoHTML(Range("I6:L200")) & str2 & emailItem.HTMLBody
.Display
End With
On Error GoTo 0
Set emailItem = Nothing
Set emailApplication = NothingI had tried to put in an IF formula into this, but it didn't seem to change anything strangely. Still generated the email, but with all rows, regardless of overdue status (i defined Status and StatusCol as Range)
VBA Code:
With emailItem
.To = Range("P7").Value
.Subject = "Internal Audit Action(s) Follow Up " & Format(Date, "dd/mm/yy")
.Attachments.Add ActiveWorkbook.FullName
End With
For Each Status In StatusCol
If Status = "Overdue" Then
emailItem.HTMLBody = str1 & RangetoHTML(Range("I6:L200")) & str2 & emailItem.HTMLBody
End If
Next Status
emailItem.Display
