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

[Dexter1759]

If it's an Add-In you're creating, which it sounds like, some like the following should do the trick:

ActiveWorkbook.Worksheets("SheetName").Range("G10").Value

[ajgcook1983]

Thank you so much Dexter - so simple and yet I was totally incapable on Friday evening.

Brilliant - thanks!!

[ajgcook1983]

Hi guys, I need to temporarily resurrect this thread.

Thanks to your help this code has done me proud for the past couple of weeks, but I now need to add a .BCC line and tell excel to retrieve the address from a given cell.

Above we're using G11.

What I now need is to add a .BCC line and tell it to find the address from cell G12. How can I do this? I've tried to use the code from the 'To' line (but substituting G11 for the relevant cell[G12]), however, VBA doesn't like it.

As ever, I'm sure it's a really easy fix, but I can't fathom it!

Please help!

Sub Email_Support_Sheet()

    Dim oApp As Object
    Dim oMail As Object
    Dim LWorkbook As Workbook
    Dim LFileName As String

    'Turn off screen updating
    Application.ScreenUpdating = False

    'Copy the active worksheet and save to a temporary workbook
    ActiveSheet.Copy
    Set LWorkbook = ActiveWorkbook

    '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 = ActiveWorkbook.Worksheets("CapReviewPaper").Range("G11").Value
        .CC = "XYZ@hotmail.com"
        .BCC = ActiveWorkbook.Worksheets("CapReviewPaper").Range("12").Value
        .Subject = "Support to your Change Paper - Comments Attached"
        .body = "Dear Sponsor," & vbCrLf & vbCrLf & _
                "I have reviewed your proposed Change and am happy to support it." & vbCrLf & vbCrLf & _
                "Please see attached." & vbCrLf & vbCrLf & _
                "Kind regards,"
        .Attachments.Add LWorkbook.FullName
        .Display
    End With

    'Delete the temporary file and close temporary Workbook
    LWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Kill LWorkbook.FullName
    LWorkbook.Close SaveChanges:=False

    'Turn back on screen updating
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    
End Sub

[Admin]

Hi

You are missing the 'G' in the code. It should be

.BCC = ActiveWorkbook.Worksheets("CapReviewPaper").Range("G12").Value

[ajgcook1983]

Haha - you spotted my errr.. deliberate error!

That was a copy-error on my behalf, though by revisiting this I have established the mistake.

I was referencing a cell (G12) which was returning an error (not an email address). Consequently VBA was giving error messages. D'Oh!

Either way, 'we' got there in the end.

Thanks guys - but sorry for wasting your time!!