VBA Macro to Email Last column D Data as Body

[tlaltmey]

Hi All,

I'm attempting to make a quick macro that emails data found in column D of Sheet "Scorecard" with the last set of data. My current issue is that column D actually is full of a formula that has its data determined as a percentage based off of columns C and B, so it is unable to find the actual data I'm looking for.

Sub Submit()

Application.DisplayAlerts = False

Dim path As String
Dim filename1 As String
Dim emailApplication As Object
Dim emailItem As Object

 ChDrive "F"
 ChDir "Filepath"
 filename1 = "Filename"
 ActiveWorkbook.SaveAs Filename:=path & filename1, FileFormat:=52
    
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)
                
                    emailItem.to = "DummyEmail@Email.com"
                    emailItem.Subject = "First Pass Yield this Week"

                    emailItem.Body = "First Pass Yield" & ThisWorkbook.Sheets("Scorecard").Range("D" & Rows.Count).End(xlUp).Value

                    emailItem.Attachments.Add ActiveWorkbook.FullName

                    emailItem.Send
                    
                    Set emailItem = Nothing
                    Set emailApplication = Nothing
                    
                    Application.ScreenUpdating = False
                    Application.DisplayAlerts = False
                    Application.AutomationSecurity = msoAutomationSecurityForceDisable
                    Application.AskToUpdateLinks = False
                    
                    Application.DisplayAlerts = True
                    
                    ActiveWorkbook.Close True

End Sub

The formula found in Column D is the following:

=IFERROR((C2-B2)/C2,"")

The issue is that this formula is implemented throughout all of column D but currently the only cell that has actual data is D2, which will then be D3 next week.

IE: Can I send data from column D based on the last row of data from Column C, as that column will be manually implemented, if this makes sense.

 

[ranman256]

is the body supposed to be 1 cell, or lots of data in many cells?
if 1 cell then
.Body = "message" & range("D1").value

 

[tlaltmey]

is the body supposed to be 1 cell, or lots of data in many cells?
if 1 cell then
.Body = "message" & range("D1").value

The cell is meant to be just one cell but the cell meant to be called out is the last cell in column D that has actual, non error data. D1 will only work once, as next week the data will be D2, then D3, then D4, etc.

Essentially I want to use column C as my "last row" data to call out but I want to call out column D in that row determined by C.

 

[tlaltmey]

Sub Submit()

Application.DisplayAlerts = False

Dim path As String
Dim filename1 As String
Dim emailApplication As Object
Dim emailItem As Object
Dim lastRow As Long
Dim mainWB As Workbook
Dim wsDest As Worksheet
Dim wsCopy As Worksheet

Set mainWB = ActiveWorkbook
Set wsCopy = mainWB.Worksheets("Sheet1")

lastRow = Cells(Rows.Count, 3).End(xlUp).Row

 ChDrive "F"
 ChDir "Path1"
 filename1 = "name"
 ActiveWorkbook.SaveAs Filename:=path & filename1, FileFormat:=52
   
    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)
               
                    emailItem.to = "email@email.com"
                    emailItem.Subject = "subject"

                    emailItem.Body = "Text " & wsCopy.Range("D" & lastRow).Value

                    emailItem.Attachments.Add ActiveWorkbook.FullName

                    emailItem.Send
                   
                    Set emailItem = Nothing
                    Set emailApplication = Nothing
                   
                    Application.ScreenUpdating = False
                    Application.DisplayAlerts = False
                    Application.AutomationSecurity = msoAutomationSecurityForceDisable
                    Application.AskToUpdateLinks = False
                   
                    Application.DisplayAlerts = True
                   
                    ActiveWorkbook.Close True
                   
End Sub

Solved