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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,284
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top