CODEWITHSUNDEEP
vbaexcelmacros
Matt Taylor
Matt Taylor

Reputation: 671

Excel apply timestamp when macro was ran

I have a email list with To, CC and BCC columns. When the checkbox is TRUE then it will add the email to that specific line in Outlook.

What I would like to do is when the Macro is ran then apply a timestamp to a cell for only the TRUE checkboxes in column "J".

This is what I am working with.

Sub SendEmail()

' Set up outlook objects for emailing

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

' Body text for the email
Dim strbody As String
strbody = ""

' Strings to contain the email addresses
Dim sendTo As String
sendTo = ""
Dim sendCC As String
sendCC = ""
Dim sendBCC As String
sendBCC = ""

' The cell containing the email address (loop variable)
Dim emailCell As Range

With ActiveSheet

    ' Cycle through email addresses, from B3 to one before next blank cell in column
    For Each emailCell In .Range("B3", .Range("B3").End(xlDown))

        ' Check each TRUE/FALSE column in same row, add email addresses accordingly

        If .Cells(emailCell.Row, "E").Text = "TRUE" Then

            sendTo = sendTo & "; " & emailCell.Text

        End If

        If .Cells(emailCell.Row, "G").Text = "TRUE" Then

            sendCC = sendCC & "; " & emailCell.Text

        End If

        If .Cells(emailCell.Row, "I").Text = "TRUE" Then

            sendBCC = sendBCC & "; " & emailCell.Text

        End If

    Next emailCell

End With

' Generate email in outlook objects defined above
On Error Resume Next
With OutMail
    .To = sendTo
    .CC = sendCC
    .BCC = sendBCC
    .Subject = ""
    .HTMLBody = strbody
    .Display
    ' If you don't want to display the email before sending it,
    ' simply use .Send instead of .Display
End With
On Error GoTo 0
End Sub

Upvotes: 0

Views: 561

Answers (3)

Matt Taylor
Matt Taylor

Reputation: 671

If .Cells(emailCell.Row, "D").Text = "R" Or .Cells(emailCell.Row, "E").Text = "R" Or .Cells(emailCell.Row, "F").Text = "R" Then
   .Cells(emailCell.Row, "G").Value = Format(Now(), "dd-mm-yyyy hh:mm AM/PM")
End If

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27478

This adds a timestamp if any of the three columns E,G or I are TRUE for that row. The timestamp is in descending order from year to second. You can change that by altering the format.

If you want the timestamps to be text then format the column first (or in your code). If column J isn't formatted as text then the timestamp will be an actual DateTime and will appear in whatever format Excel thinks it should.

If .Cells(emailCell.Row, "E") OR .Cells(emailCell.Row, "G") OR .Cells(emailCell.Row, "I") Then
   .Cells(emailCell.row, "J").Value = Format(Now(), "yyyy-mm-dd hh:mm:ss")
End If

Upvotes: 2

scott
scott

Reputation: 153

try

If .Cells(emailCell.Row, "J").Text = "TRUE" Then
    sendTo = sendTo & "; " & emailCell.Text
   .cells(emailcell.row, column).value = hour(now) & ":" & minute(now)
End If

Upvotes: 0

Related Questions