Email - Subject line change values

[JD5307]

I have a spreadsheet with Project IDs in Column A. Im looking to send individual emails once a value has been added to the corresponding cell in Column C. I want the cell specific information from Column A to be in the Subject line. I can do everything but change the Subject Line value to include the Corresponding Cell information. I always get the cell information from cell A2 in the Subject line - not the cell information from the corresponding cell in that column.

.Subject = "GHG " & Range("A2").Value

So my subject line is always - GHG 0102

And I want it to be whatever the relevent project ID is. i.e. GHG 0103, GHG 0105, etc.

 

[Andrzejek]

You "always get the cell information from cell A2 in the Subject line" because that's what you asked for.

How do you determine which Row you want to 'grab' the info from?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[JD5307]

Sub EmailNotificationSent()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String

Set OutLookApp = CreateObject("OutLook.application")
Set OutLookMailItem = OutLookApp.createitem(0)

With OutLookMailItem
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
If MailDest = "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
MailDest = Cells(iCounter, 4).Value
ElseIf MailDest <> "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
MailDest = MailDest & ";" & Cells(iCounter, 4).Value
End If
Next iCounter

.BCC = MailDest
.Subject = "GHG " & Range("A2").Value
.Body = "Notification that GHG Project Work has been initiated and ready for your review"

.Send
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub

 

[SkipVought]

Maybe this?

Sub EmailNotificationSent()
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim iCounter As Integer
    Dim MailDest As String
    
    Set OutLookApp = CreateObject("OutLook.application")
    Set OutLookMailItem = OutLookApp.createitem(0)
    
    With OutLookMailItem
        MailDest = ""
        For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
            If MailDest = "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
                MailDest = Cells(iCounter, 4).Value
            ElseIf MailDest <> "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
                MailDest = MailDest & ";" & Cells(iCounter, 4).Value
            End If
[b]            
            .BCC = MailDest
            .Subject = "GHG " & Range("A2").Value
            .Body = "Notification that GHG Project Work has been initiated and ready for your review"
[/b]        Next iCounter
        
        .Send
    End With
    
    Set OutLookMailItem = Nothing
    Set OutLookApp = Nothing

End Sub

 

[Andrzejek]

And I assume your [tt]iCounter[/tt] points to Rows, so instead of:
[tt]
.Subject = "GHG " & Range("A2").Value
[/tt]
you may use;
[tt]
.Subject = "GHG " & Cells(iCounter, 1)
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Oops, yes!

 

[JD5307]

Thank you! Andrzejek (Programmer) It Worked!

 

[JD5307]

This isn't working now... Any suggestions?

 

[SkipVought]

Exactly what do you mean by "not working?"

Did your computer blow up, or something a tad bit less dammaging?

 

[JD5307]

Wow - I asked for suggestions - and I get that.

Yeh, that was it my computer blew up - REALLY?????

I'm thinking this forum is for geniune questions. For folks that need assistance - not for, well posts like that.

But since I need the help, I'll appease your obnoxious post and say I entered the code exactly as it was listed and while it worked or I thought it worked once, I am not getting emails when the cell values change for cells other than the "A2".

 

[Andrzejek]

1st - I have never seen any 'obnoxious post' from Skip, and I have seen a LOT of his posts. And all were/are helpful.
2nd - you said: "It Worked!" and then you said: "This isn't working now... Any suggestions?" At TT there are a lot of smart people, but no one here is a mind reader or can see your ‘non working’ problem. So don't get upset if someone asks for more details.

Did you step thru your code to see where you get the values from?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

Wasn’t that the whole problem that you always were getting value from A2, and that’s not what you wanted?
So we (I) assumed that – row 1 is a header row, so your data starts in row 2
When you process data from row 2, you get part of your Subject from A2
When you process data from row 3, you get part of your Subject from A3
When you process data from row 4, you get part of your Subject from A4

Was I wrong?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ck1999]

Just a quick glance and without knowing what "is not working"

shouldn't

MailDest = ""

be inside the loop so it resets after each email.

Chris

 

[ck1999]

I would think this would get closer to the desired outcome

Sub EmailNotificationSent()
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim iCounter As Integer
    Dim MailDest As String

    
    Set OutLookApp = CreateObject("OutLook.application")
       
 For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
    Set OutLookMailItem = OutLookApp.createitem(0)
    
    With OutLookMailItem
        MailDest = ""

            If MailDest = "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
                MailDest = Cells(iCounter, 4).Value
            ElseIf MailDest <> "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
                MailDest = MailDest & ";" & Cells(iCounter, 4).Value
            End If
            
            .BCC = MailDest
            .Subject = "GHG " & Cells(iCounter, 1)
            .Body = "Notification that GHG Project Work has been initiated and ready for your review"

        
        .Send
  Set OutLookMailItem = Nothing
        Next iCounter
    End With
    

    Set OutLookApp = Nothing

End Sub

You code originally only sends 1 email. If this is what you want disregard what I posted. If you want 1 email per row that has "Send Reminder" then you need to send multiple emails.

Chris