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
Bookmarks