Emailing a workbook via VBA (subject line question)

[zombiemaster]

I have a report that I'm developing and want to email it automatically when it's ready. I know how to do most of it, but I'm having trouble with the Subject line within the email. I want the subject line to be the file name. Below is the code that I have right now, that works fine, that includes a "hard coded" subject line of "This Month's Report" but I want it to show the file name of "FREEZE CODES - MARCH 2019".

' Declare and establish the Object variables for Outlook:
    
    Dim objOutlook As Object
    Dim objNameSpace As Object
    Dim objInbox As Object
    Dim objMailItem As Object
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNameSpace = objOutlook.GetNamespace("MAPI")
    Set objInbox = objNameSpace.Folders(1)
    Set objMailItem = objOutlook.CreateItem(0)
    
' An email will generate with the file as an attachment
    
    With objMailItem
    .To = "zombiemaster@noname.com"
    .CC = ""
    .Subject = "This month's report"
    .Body = _
    "Attached is the latest Freeze Code report." & Chr(10) & Chr(10) & _
    "If you have any questions, let me know." & Chr(10) & Chr(10)
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With

' Release object variables from system memory
    
    Set objOutlook = Nothing
    Set objNameSpace = Nothing
    Set objInbox = Nothing
    Set objMailItem = Nothing

A bit earlier in the code, I have already declared the filename, and thought I might be able to use it here, but it didn't work.

' Saves the file with the correct month and year in the file name:
    Dim Path As String
    Dim filename As String
    Path = "\\Server\2019 testing\"
    filename = Range("F1")
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"

I tried changing the code to show this, but it didn't work and the Subject line in the email just ended up empty:

    .Subject = filename

Thanks for any tips - it's probably just something simple, as usual...
~ZM~

 

[shg]

.Subject = filename

 

[Logit]

Option Explicit


Sub mailme()
' Declare and establish the Object variables for Outlook:
    
    Dim objOutlook As Object
    Dim objNameSpace As Object
    Dim objInbox As Object
    Dim objMailItem As Object
    Dim Filename As String
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNameSpace = objOutlook.GetNamespace("MAPI")
    Set objInbox = objNameSpace.Folders(1)
    Set objMailItem = objOutlook.CreateItem(0)
    
    Filename = ActiveWorkbook.Name
        If InStr(Filename, ".") > 0 Then
           Filename = Left(Filename, InStr(Filename, ".") - 1)
        End If
' An email will generate with the file as an attachment
    
    With objMailItem
    .To = "zombiemaster@noname.com"
    .CC = ""
    .Subject = Filename
    .Body = _
    "Attached is the latest Freeze Code report." & Chr(10) & Chr(10) & _
    "If you have any questions, let me know." & Chr(10) & Chr(10)
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With


' Release object variables from system memory
    
    Set objOutlook = Nothing
    Set objNameSpace = Nothing
    Set objInbox = Nothing
    Set objMailItem = Nothing
End Sub

 

[zombiemaster]

I had already tried .Subject = filename but it just came up as a blank subject line in the email... but when I kept playing with it I discovered an alternative that works as I want it to!

Earlier in the code I used an input box to designate what the file name was going to be based on user input. I was able to re-use the myValue entry from that earlier piece:

    Dim myValue As Variant
    myValue = InputBox("Enter the Month and Year of the report as 'Month - Year'", "Report Month & Year needed", "Month - Year")
    Range("E1").Value = myValue

And I came up with this:

    With objMailItem
    .To = "zombiemaster@noname.com"
    .CC = "" 
    .Subject = "PALLM Freeze Code report - " & myValue
    .Body = _
    "Attached is the " & myValue & " Freeze Code report." & Chr(10) & Chr(10) & _
    "If you have any questions, let me know." & Chr(10) & Chr(10)
    .Attachments.Add ActiveWorkbook.FullName
    .Display 'Change to Send if you want to just send it.
    End With

I was even able to incorporate it into the email body, which was an added bonus.

Mystery solved, thanks for taking the time to check it out and respond!
~ZM~

 

[Logit]

.
Glad you found something that works for your needs.

There was more to my macro than just the Subject = Filename.


Above that are these lines of code as well :

Filename = ActiveWorkbook.Name
        If InStr(Filename, ".") > 0 Then
           Filename = Left(Filename, InStr(Filename, ".") - 1)
        End If

That code strips the file extension from the filename, leaving only the filename to paste into the Subject field.


Your method with the textbox allows you to use anything as the Subject.


Cheers.