VBA Coding + Emailing Attachment via Outlook

[msharpes90]

So I am fairly new to VBA Coding. Basically what I am trying to do is send my current workbook as an attachment via Outlook and I am wanting to have the subject line of the email be a specified cell range like I want it to be E3+D9. And also when sending the workbook as attachment is there a way to change the filename when sending it? Thanks for anyone that can help!

 

[lrobbo314]

Welcome to the forum. Something like this? I didn't know if you wanted the subject to be E3+E9, as in addition, or just concatenating 2 strings. I've commented the code.

Sub SendMail()
Application.ScreenUpdating = False
Dim oApp As Object:     Set oApp = CreateObject("Outlook.Application")
Dim oMail As Object:    Set oMail = oApp.createitem(0)
Dim oPath As String:    oPath = ActiveWorkbook.FullName
Dim tmp As String:      tmp = "C:\temp\"
Dim newName As String:  newName = "Copied Workbook.xlsm"
Dim newPath As String:  newPath = tmp & newName
'dim subj as String:    subj = evaluate("=E3+E9") 'Addition
Dim subj As String:     subj = [E3] + " " + [E9]

ActiveWorkbook.SaveAs newPath, 52

With oMail
    .to = "test@test.com"
    .Subject = subj
    .body = "See Attached"
    .attachments.Add newPath
    .display
End With

Workbooks.Open oPath
Workbooks(newName).Close SaveChanges:=False
Kill newName

Set oMail = Nothing
Set oApp = Nothing

Application.ScreenUpdating = False
End Sub

 

[lrobbo314]

Realizing now that the code never gets to the 'Kill' line.

The following code can be used to delete all Excel files from the C:\temp folder.

Dim path As String: path = "C:\temp\"
Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
Dim fil As Object, fol As Object
Dim ext As String


Set fol = FSO.getfolder(path)


For Each fil In fol.Files
    ext = Split(fil.Name, ".")(1)
    If ext Like "xl*" Then Kill fil.path
Next fil

 

[msharpes90]

That is really close to what I was thinking. Basically I just want the subject line of the email to be cell E3+D9 but also I would like the file name to be the same. I am using the same form multiple times but the file name and subject line will change roughly 20 times. Reason behind why wanting the subject line to be specific cells as I will be changing them. Hopefully this make sense. Thanks again for your help.