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
Display More
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.