Rename Workbook before sending email with Macros based on Cell Values

[GamerNeelie]

I Have an email script that will send a email using a button

I need to be able to rename the file based on 3 Cell values before it sends the email

Sub Email()
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Range("X4").Value
        .CC = ""
        .BCC = ""
        .Subject = "Report - " & Range("X11").Value & " - " & Range("X9").Value & " - " & Range("E6").Value
        .Body = "Please find Attached our Report for " & Range("X11").Value & vbNewLine & vbNewLine & _
                "Company: " & Range("X9").Value & vbNewLine & _
                "ID: " & Range("E6").Value

        .Attachments.Add ActiveWorkbook.FullName
        '.Send
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    'MsgBox "Email Sent "
End Sub

I have it sending the current workbook and filename but there is 100 business that will be sending this email to a master address so I need to be able to rename the files based on 3 cell values
A1 = Company ID
A2 = Company Name
A3 = Other Text

Thanks.

 

[Logit]

Option Explicit

Sub Email()
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    
    ActiveWorkbook.SaveAs "C:\Users\jimga\Desktop\" & Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value & ".xlsm"
    
    NewWB = "C:\Users\jimga\Desktop\" & Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value & ".xlsm"
    
    With OutMail
        .To = Range("X4").Value
        .CC = ""
        .BCC = ""
        .Subject = "Report - " & Range("X11").Value & " - " & Range("X9").Value & " - " & Range("E6").Value
        .Body = "Please find Attached our Report for " & Range("X11").Value & vbNewLine & vbNewLine & _
                "Company: " & Range("X9").Value & vbNewLine & _
                "ID: " & Range("E6").Value

        .Attachments.Add ActiveWorkbook.FullName
        '.Send
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    'MsgBox "Email Sent "
End Sub

 

[GamerNeelie]

Option Explicit

Sub Email()
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
   
    ActiveWorkbook.SaveAs "C:\Users\jimga\Desktop\" & Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value & ".xlsm"
   
    NewWB = "C:\Users\jimga\Desktop\" & Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value & ".xlsm"
   
    With OutMail
        .To = Range("X4").Value
        .CC = ""
        .BCC = ""
        .Subject = "Report - " & Range("X11").Value & " - " & Range("X9").Value & " - " & Range("E6").Value
        .Body = "Please find Attached our Report for " & Range("X11").Value & vbNewLine & vbNewLine & _
                "Company: " & Range("X9").Value & vbNewLine & _
                "ID: " & Range("E6").Value

        .Attachments.Add ActiveWorkbook.FullName
        '.Send
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    'MsgBox "Email Sent "
End Sub

how can I delete the created workbook once it has sent? so its like a temporary file

also can I set the save path to the current directory as these business computers have restrictions on where there allowed to create and modify folders

 

[GamerNeelie]

So the outcome I am trying to achieve is

1. Click button to send mail
2. Rename the worksheet based on 3 cell values
3. Save as a temporary file in current workbook dir
4. Attach the temporary file
4. send email automatically (once I enable the .send and disable .display)
5. delete the temporary file

seen a few things once I googled it but struggling with the temporary file part

 

[Logit]

Option Explicit

Sub save_file()

Dim filename1 As String

    filename1 = Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value
    
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveCopyAs Application.ThisWorkbook.path & "\" & filename1 & ".xlsm"
    Application.DisplayAlerts = True

Email2

End Sub

Sub Email2()
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String
    Dim filename1 As String

    filename1 = Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'On Error Resume Next
    
    With OutMail
        .To = Range("X4").Value
        .CC = ""
        .BCC = ""
        .Subject = "Report - " & Range("X11").Value & " - " & Range("X9").Value & " - " & Range("E6").Value
        .Body = "Please find Attached our Report for " & Range("X11").Value & vbNewLine & vbNewLine & _
                "Company: " & Range("X9").Value & vbNewLine & _
                "ID: " & Range("E6").Value

        .Attachments.Add ThisWorkbook.path & Application.PathSeparator & filename1 & ".xlsm"
        
        '.Send
        .Display
        
    End With
    
  
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Kill ThisWorkbook.path & Application.PathSeparator & filename1 & ".xlsm"
    
    'MsgBox "Email Sent "
End Sub

 

[GamerNeelie]

I am getting a syntax error on the line (filename1 = Sheet1.Range("A1").Value & " " & Sheet1.Range("A2").Value & " " & Sheet1.Range("A3").Value)

 

[Logit]

What is the name of the sheet where the company information is stored in A1:A3 ?

 

[GamerNeelie]

What is the name of the sheet where the company information is stored in A1:A3 ?

its on "sheet 2" or Tab Name "Home Page"

 

[Logit]

Change Sheet1 to Sheet2 in every location you find it. Your 'Home Page" will need to be Sheet2 from now on or you'll need to edit the code as
the Sheet # changes.

 

[GamerNeelie]

Change Sheet1 to Sheet2 in every location you find it. Your 'Home Page" will need to be Sheet2 from now on or you'll need to edit the code as
the Sheet # changes.

yeah i just tried that as I noticed it was set to Sheet1 and Not Sheet2 but still getting the error on that line

I did change the cells that the company info was saved in to Y19 Y20 Y21