How Get Value From A Cell Range Using VBA

[ajgcook1983]

Guys,

I already have the code I want, but it's late and I'm struggling to put the icing on the cake (as it were).

I have managed to customise the entire thing, but am struggling to get VBA to identify a cell from which to get the email address for the 'To:' line in the email...

 'Create a temporary file in your current directory that uses the name
    ' of the sheet as the filename
    LFileName = LWorkbook.Worksheets(1).Name
    On Error Resume Next
    'Delete the file if it already exists
    Kill LFileName
    On Error GoTo 0
    'Save temporary file
    LWorkbook.SaveAs Filename:=LFileName

    'Create an Outlook object and new mail message
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    'Set mail attributes (uncomment lines to enter attributes)
    ' In this example, only the attachment is being added to the mail message
    With oMail
        .To = G10.Value
        .CC = "Jill.X@MAIL.COM' Alexander.C@MAIL.COM"
        .Subject = "Opposition to your Change Paper - Comments Attached"
        .body = "Dear Sponsor," & vbCrLf & vbCrLf & _
                "I have reviewed your proposed Change and am unable to support it at the present time." & vbCrLf & vbCrLf & _
                "Please see attached for my comments." & vbCrLf & vbCrLf & _
                "Kind regards,"
        .Attachments.Add LWorkbook.FullName
        .Display
    End With

So, your comments are very welcome - all I need is to tell the macro to look in G10 of the sheet it's attaching to find the recipient's email address... argh!

Please help!

Kind regards,

Alex


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA