Please help with adding a cell ref in an emails subject line from Excel.

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Hello guys I have a macro that will automatically email a spreadsheet when i click the macro button. However I am trying to tweak it to where the subject line references ='Daily Numbers'!A3 (the terminal ID the spreadsheet is for) at the beginning of the subject line. So the end product needs to look like this....

Code:
 TID Proactive Pickup Recap XX/XX to XX/XX

TID = ='Daily Numbers'!A3

Below is my email macro that I currently have. Any help is greatly appreciated!

Code:
Public Sub EmailButton()


    Dim newWorkbookName As String, newWorkbookFullName As String
    Dim oApp As Object
    Dim oMail As Object
   
    With ThisWorkbook
        newWorkbookName = "Proactive Pickup Recap.xls"     'name of the new workbook containing the 2 sheets
        newWorkbookFullName = .Path & "\" & newWorkbookName
        .Sheets(Array("Daily Analysis", "RT - Missed Non-TPRs", "RT - NF Per Driver", "Arrive After Closes")).Copy
        ActiveWorkbook.SaveAs Filename:=newWorkbookFullName, FileFormat:=.FileFormat
        ActiveWorkbook.Close False
    End With
   
    'Create an Outlook object and new mail message
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
   
    With oMail
        .To = ""
        .Subject = "[TID] Proactive Pickup Recap XX/XX to XX/XX"
        .body = ""
        .Attachments.Add newWorkbookFullName
        .Display  'or .Send
    End With


' Delete's created workbook off of computer.


  Kill newWorkbookFullName


    
    Set oMail = Nothing
    Set oApp = Nothing
   
End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Code:
        .Subject = Sheets("Daily Numbers").Range("A3").Value
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top