CODEWITHSUNDEEP
excelvbamacos
Anthony Herbert
Anthony Herbert

Reputation: 1

Sending Emails in Excel using VBA when a specific value of a cell is selected

I'm trying to send an email to recipients when a specific value of a cell is selected. If the value is 'new' then it gets sent to a predefined email. if the value is 'pending' or otherwise then it gets sent to a user entered email in another cell. I'm using this currently but i'm not sure how to modify it to look at the other cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myToAdd As String
If Target.Column = 6 Then
    If Target.Value = "New " Then
        myToAdd = "[email protected];"
    ElseIf Target.Value = "Pending review" Then
        myToAdd = 'I want this to look at another cell for the email
    End If

    With CreateObject("Outlook.Application").createitem(0) '0 will create a new email item
        .To = myToAdd
        .Subject = "A change request/project support has been request"
        .Body = "Dear User," & vbNewLine & vbNewLine & "This is my email body "
        .Display
    End With
End If

Upvotes: 0

Views: 291

Answers (2)

Cullen
Cullen

Reputation: 54

You can use Cells([row],[col]) to reference other cells in the active worksheet.

On the other hand, if the worksheet is not active you can use Sheets([sheetname]).Cells([row],[col]).

Upvotes: 1

Sancarn
Sancarn

Reputation: 2824

@Cullen gave a good answer, also you can use:

SomeSheet.Range(<<cellreference)

e.g.

Sheets("CoolSheet").Range("A6")

Upvotes: 0

Related Questions