VBA string to stop running macro if condition is met

[Aspland]

I have a VBA written that if the macro sees in my spreadsheet that a due date has Today's date ("Removal Date") it will send an automatic email alerting our team and then place a day/time stamp in the "Auto Email Sent" column. My problem is that when a team member then opens the spreadsheet it reruns the macro and duplicate emails get sent to the team. How do i modify the macro that if there is data in the auto email sent cell then don't run the macro? Sorry, i tried to add the worksheet but the addin wouldn't install for me.

 

[Joe4]

Welcome to the Board!

Posting images of your code is very restricting, as we cannot copy/paste/edit the code for you.
Please copy/paste your VBA code here, using the Code Tags, as instructed here: How to Post Your VBA Code

Also, can you tell us exacty which column this text to check is going into?
Your image does not show the Excel column header letters, so we cannot see which column it is.

 

[Artik]

Add one more condition after the line:

If Cells(i, 3).Value = Date Then
If Len(Cells(i, “N”).Value = 0 Then
...

Artik

 

[Aspland]

Welcome to the Board!

Posting images of your code is very restricting, as we cannot copy/paste/edit the code for you.
Please copy/paste your VBA code here, using the Code Tags, as instructed here: How to Post Your VBA Code

Also, can you tell us exacty which column this text to check is going into?
Your image does not show the Excel column header letters, so we cannot see which column it is.

Thanks for the instructions!
When the spreadsheet is opened, currently it looks to column C which lists the date the Quality Alert is to be removed. If the date matches today, then it will look to column O for the primary email address and column P for the CC Team email addresses and then column K to ID the work center the Quality alert is located at and then send an automated email alerting everyone that the Quality alert is due to be removed.. Once the email is sent it will then place a delivery stamp (Date and time) into column N. I'd like the macro to first verify that the date in column C matches today and then verify that column N is blank. If it's blank then run the macro. If there's a date stamp in column N, then don't run the macro.

Private Sub Workbook_Open()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList, CCList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp, OutMail


With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With


Sheets("LOG").Select
lRow = Cells(Rows.Count, 1).End(xlUp).Row

Set OutApp = CreateObject("Outlook.Application")

For i = 1 To lRow
  If Cells(i, 3).Value = Date Then
     Set OutMail = OutApp.CreateItem(0)


        toList = Cells(i, 15)
        CCList = Cells(i, 16)
        eSubject = "Quality Alert " & Cells(i, 1)
        eBody = "This is an auto-generated email ~ Quality Alert " & Cells(i, 1) & " at " & Cells(i, 11) & " is due for removal today."
       
        On Error Resume Next
        With OutMail
        .To = toList
        .CC = CCList
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        .bodyformat = 1
        .Display
        .Send
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
 Cells(i, 14) = "Mail Sent " & Date + Time
End If
Next i

Set OutApp = Nothing

ActiveWorkbook.Save


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
Sheets("LOG").Range("A1").Select
End Sub

 

[Aspland]

Thanks for the instructions!
When the spreadsheet is opened, currently it looks to column C which lists the date the Quality Alert is to be removed. If the date matches today, then it will look to column O for the primary email address and column P for the CC Team email addresses and then column K to ID the work center the Quality alert is located at and then send an automated email alerting everyone that the Quality alert is due to be removed.. Once the email is sent it will then place a delivery stamp (Date and time) into column N. I'd like the macro to first verify that the date in column C matches today and then verify that column N is blank. If it's blank then run the macro. If there's a date stamp in column N, then don't run the macro.

[CODE=vba]Private Sub Workbook_Open()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList, CCList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp, OutMail


With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With


Sheets("LOG").Select
lRow = Cells(Rows.Count, 1).End(xlUp).Row

Set OutApp = CreateObject("Outlook.Application")

For i = 1 To lRow
  If Cells(i, 3).Value = Date Then
     Set OutMail = OutApp.CreateItem(0)


        toList = Cells(i, 15)
        CCList = Cells(i, 16)
        eSubject = "Quality Alert " & Cells(i, 1)
        eBody = "This is an auto-generated email ~ Quality Alert " & Cells(i, 1) & " at " & Cells(i, 11) & " is due for removal today."
       
        On Error Resume Next
        With OutMail
        .To = toList
        .CC = CCList
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        .bodyformat = 1
        .Display
        .Send
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
 Cells(i, 14) = "Mail Sent " & Date + Time
End If
Next i

Set OutApp = Nothing

ActiveWorkbook.Save


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
Sheets("LOG").Range("A1").Select
End Sub

[/CODE]

Sorry, I forgot about column A. In the email the macro will also look at Column A which lists the Quality Alert number so the team knows which alert it's referring too.

 

[Joe4]

To amend your code to not only look at the date in column C, but to also look for no entry in column N, change this line:

  If Cells(i, 3).Value = Date Then

to this:

  If (Cells(i, 3).Value = Date) And (Cells(i, 14).Value = "") Then

or use the similiar suggestion that Artik recommended.

 

[Aspland]

To amend your code to not only look at the date in column C, but to also look for no entry in column N, change this line:

  If Cells(i, 3).Value = Date Then

to this:

  If (Cells(i, 3).Value = Date) And (Cells(i, 14).Value = "") Then

or use the similiar suggestion that Artik recommended.

THANK YOU BOTH SOO MUCH! It works perfect.

 

[Joe4]

You are welcome.
Glad we were able to help!