I would like to whether it is possible to extract the name of the workbook attached on Outlook and add this to the end of the text on the macro code
If so, could someone kindly provide me with the code
I would like the section of code .Subject = to contain the "Summary Sales figures for + the name of the workbook attached or eg if name of workbook attached is BR.xls then the code must appear as
.Subject = "Summary Sales figures for Br.xls
Code
Sub SendFiles()
Dim lCount As Long
Dim vFilenames As Variant
Dim sPath As String
Dim lFilecount As Long
Dim sFullName As String
sPath = "C:\Sales Reports\"
ChDrive sPath
ChDir sPath
vFilenames = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to open", , True)
If TypeName(vFilenames) = "Boolean" Then Exit Sub
For lCount = LBound(vFilenames) To UBound(vFilenames)
Workbooks.Open vFilenames(lCount), UpdateLinks:=False
Application.DisplayAlerts = False
Application.CutCopyMode = False
Sheets(Array("summary")).Copy
ActiveWorkbook.SaveAs Replace(vFilenames(lCount), ".xls", "") & ".summary.xls", FileFormat:=xlNormal
vFilenames(lCount) = ActiveWorkbook.FullName
Application.ScreenUpdating = False
For Each sht In Sheets(Array("summary"))
Sheets(sht.Name).UsedRange.Copy
Sheets(sht.Name).Range("a1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
Application.ScreenUpdating = True
ActiveWorkbook.Close True
Application.DisplayAlerts = True
Application.CutCopyMode = True
Next
Mailfiles "[email protected]", vFilenames
For lCount = LBound(vFilenames) To UBound(vFilenames)
Kill vFilenames(lCount)
Next
ActiveWorkbook.Close False
End Sub
Sub Mailfiles(sMailAddress As String, vFiles As Variant)
Dim oMailItem As Object
Dim oOLapp As Object
Dim lCt As Long
Set oOLapp = CreateObject("Outlook.application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem
.To = sMailAddress
.Subject = "Summary Sales Figures"
.body = "Attached please find Sales report for " & Format(Month(Date) - 1 & " " & Year(Date), "mmm yyyy") & " vs the Prior Year" & vbNewLine & vbNewLine
.body = .body & "Regards" & vbNewLine & vbNewLine
.body = .body & ""I also posted on https://www.mrexcel.com/forum/…l-macro-subject-line.html