Pulling an email address from a cell using Offset

[mnmhenry]

Greetings everyone,

I'm hoping someone can help me here. I'm trying to send an email from excel but I want to pull the email out of a cell that is 6 columns to the right of the active cell.

I can make it run ok when I'm using range (.to = Sheets("Quote Register").Range("J11")) but having issues with offset. (Coloured blue)

Sub Sent_Test_Email()
On Error GoTo ErrHandler

' SET Outlook APPLICATION OBJECT.

Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
'.to = "slave@resolve.net.au"
'.to = Sheets("Quote Register").Range("J11")

.to = Sheets("Quote Register").ActiveCell.Offset(7, 0)
.Subject = Sheets("Quote Register").Range("K11")
.Body = Sheets("Quote Register").Range("L11")
.Display ' Display the message in Outlook.
End With

' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing

ErrHandler:
'
End Sub

Thanks for any help.

 

[StephenCrump]

ActiveCell is not a Sheet property, so your syntax is not correct.

Try:

.to = ActiveCell.Offset(, 6).Value