Pulling email address from specific field and adding it to the .To fiel

[nanders8]

I have a form that I am wanting to send via email. I have the code and it works if I input an email address manually, however, I want to automate the process. I want the macro to pull the recipient email address from the worksheet and apply it to the .To field. '

Here is the code that i am using.....

With OutlookMail    .To =[B]I would like this field to pull data from cell B18.[/B]
    .CC = ""
    .BCC = ""
    .Subject = "Time Additions and Corrections for " & Range("B18")
    .Body = "Please see the attached GSS 27."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
MsgBox "Your email has been sent to " & Range("B19")

Any assistance would be good. I tried the following and none of these worked.

 .To =[B]Range("B18")[/B]

 .To =Cells(18, 2[B])[/B]

None of these worked either.

 

[Joe4]

I have not worked much with sending emails via Excel, but I wonder if your issue is that you are trying to do it within the "With Outlook" block.
Try capturing the cell value in a variable BEFORE the "With Outlook" block, and then reference that variable, i.e.

Dim emlTo as String
emlTo = Sheets("Sheet1").Range("B18")
With Outlook
    .To = emlTo