VBA Reference specific Cell Value from another workbook

[rplohocky]

Hello,
Can someone look at this code and help me with a reference? I am trying to grab an email address from another open workbook and have it insert into the "TO:" location in the email. I'm getting stuck on what I'm doing wrong or what's missing. The email address is located in cell D33 which is in workbook ("6251 Vivint Rental Report.xlsm").

Sub EmailRentalReport6251()

    Dim mess_body As String, StrFile As String, StrPath As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Rng As Range
    Dim WorkRng As Range



   '****************************************************
' THIS IS WHAT I AM USING TO REFERENCE THE OPEN WORKBOOK. 
    Dim src As Workbook
    Set src = Workbooks("6251 Vivint Rental Report.xlsm")
    'Set src = Workbooks("Vivint-Open.csv").Worksheets("OPEN TICKETS").Range("A1")
    
    Dim valueBookA As String
    valueBookA = src.Worksheets("Data").Cells(1, 2)
    Cells(1, 1).Value = valueBookA
  '******************************************************

  
    'ActiveWorkbook.Save
    
'Below will email the workbook as an attachment
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

'~~> Change path here
    'StrPath = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\All Workbooks\6251 Vinvint Rental Report.xlsm" & [D33]

    On Error Resume Next
    With OutMail
        .BodyFormat = olFormatRichText
        .To = valueBookA  '****************TRYING TO HAVE THE EMAIL ADRRESS PLACED HERE******
        .CC = [D34]
        .Subject = [D35] & [D19] & " " & [K19]
        .HTMLBody = [D36] & [D19]
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\Users\RPlohocky\Desktop\Macro Projects\GE Idle List.xlsm")
        

        .Display   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    ActiveWorkbook.Save
    'ActiveWorkbook.Close
    
    End Sub

 

[James_Latimer]

You need to change the line

.BodyFormat = olFormatRichText

to

.BodyFormat = 3

Something to do with the late binding of Outlook.