1

I have been using the following macro, which works very well indeed, but it has come to the point where it needs some enhancements:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myMail As String

If Target.Column = 7 Then

    If Target.Value = "option 1" Then
        myMail = "email1"
    ElseIf Target.Value = "option 2" Then
        myMail = "email2"
    ElseIf Target.Value = "option 3" Then
        myMail = "email3"
    ElseIf Target.Value = "option 4" Then
        myMail = "email4"
    ElseIf Target.Value = "option 5" Then Exit Sub

    End If

    With CreateObject("Outlook.Application").CreateItem(0)
        .To = myMail
        .Subject = "Hello"
        .Body = "Hi," & vbNewLine & vbNewLine & "This is a test"
        .Display
    End With
End If
End Sub

The "options" are selected from a drop down list contained in each cell in column 7 (it wouldn't let me use "G"). Upon selecting one of the options, an email is automatically generated.

I would now like to add to the subject line of the email, the value of the cell from column "A" in the same row. eg. If option 3 is selected in cell G15, the value from cell A15 needs to be added to the subject line of the email.

As well as this, a further enhancement is needed:

As the worksheet is likely to grow into 100's of lines I would like the macro to generate a hyperlink which links to the exact cell in column "A" (from where the value added to the email subject line was taken). This hyperlink then needs to be automatically added to the body of the email.

3
  • You have posted working code. It would be prefered to include your attempted changes to this code that adress the needs you have. And tell us what part you got stuck on. That way it's easier to help. Just a simple headsup; Target.Row would tell you the exact (or rather first) line of the changed cell/range.
    – JvdV
    Aug 12, 2019 at 12:26
  • As yet I have not made any attempts to change this code as I wouldn't know where to start.
    – GlynB
    Aug 12, 2019 at 13:32
  • I recommend starting with the Range object and reading about it on websites like this one Put a little effort into understanding what you're reading, put more effort into recognizing patterns, and put and most of your effort into finding, using, and understanding the examples that help you build your project. Switch it up when you feel like you're stuck because you will find solutions to some of your toughest challenges when you're not looking for them . Aug 24, 2019 at 5:40

0

You must log in to answer this question.

Browse other questions tagged .