Hi,

I am using the below code to generate an email from excel in outlook and add in some recipient details from the currently activate column. The email will be generated after clicking on a shape on top of the column and the recipients data should be taken from several rows in that column:

Sub Mail_ThankYouNote()
'Working in 2000-2010
    Dim OutApp As Object
    Dim outMail As Object
    Dim rng As Range

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

    ActiveWorkbook.ActiveSheet.Select
    
    Set rng = Sheets("ThankYouNote_Format").Range("A1:B28").SpecialCells(xlCellTypeVisible)
    
    Set OutApp = CreateObject("Outlook.Application")
    Set outMail = OutApp.CreateItem(0)

        With outMail
            .To = ActiveSheet.Columns(ActiveCell.Column).Rows("12")
            .CC = ""
            .BCC = ""
            .Subject = "Your stay with us"
            .body = "Dear " & ActiveSheet.Columns(ActiveCell.Column).Rows("8") & " " & ActiveSheet.Columns(ActiveCell.Column).Rows("10") & ","
            .HTMLBody = RangetoHTML(rng)
            '.Attachments.Add ("C:\test.txt")           'Add attachments via direct path
            .Display                                    'Either use ".Send" for immediate mailing or ".Display" to show mail
        End With

    ' Clean up
    Set outMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub
Now my issue is following:
1. When clicking on the shape, the cell is not activated, therefore excel doesnt know in which column I am and which rows it should take the data from.
2. The body message is taken from my "ThankYouNote_Format" sheet and is then converted to HTML. The message body is generic, but I would need the first line of the body to be a dynamic "Dear Mr. / Ms. XXX," with the data from the column's rows.

Suggestions are welcome
A2k