Microsoft Office Forums - View Single Post - [Solved] Attach .xlsm file to Outlook email from whatever folder it's been saved in

kiwimtnbkr · 04-01-2022, 07:48 PM

This may be a case that I can't see the forest for the trees and the answer is not as complex as I think it is.

The code below runs to provide the file name as part of the SaveAs routine. The .xltm is saved as an .xlsm file in various folders by the users, depending on what it is that they are requesting.

Code:

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
Dim xFileName As String
Dim U7part As String, U8part As String, K13part As String

Dim strMsg As String
    If (Range("U7") = Empty) Then
        MsgBox "'Request number' is mandatory.", vbCritical, "Title"
        Cancel = True
        Range("U7").Select
    Exit Sub
        Else
    If (Range("U8") = Empty) Then
        MsgBox "'Request date' is mandatory.", vbCritical, "Title"
        Cancel = True
        Range("U8").Select
    Exit Sub
        Else
    If (Range("L33") = "click here then select from drop down list") Then
        MsgBox "para. 3.b. 'Method of Delivery' is mandatory.", vbExclamation, "Title"
        Cancel = True
        Range("L33").Select
    Exit Sub
        Else
    If (Range("L37") = Empty) Then
        MsgBox "para. 3.d. 'Date required by' is missing.", vbExclamation, "Title"
        Cancel = True
        Range("L37").Select
    Exit Sub
        Else
Call RenameTemplateSheet
        U7part = ws.Range("U7").value
        U7part = Replace(Replace(U7part, " / ", "/"), "/", "_")
        U8part = ws.Range("U8").value
        U8part = Replace(Replace(U8part, " / ", "/"), "/", "_")
        K13part = ws.Range("K13").value
        K13part = Replace(K13part, "/", "_")
Range("U9").Select
If SaveAsUI <> False Then
    Cancel = True
        xFileName = Application.GetSaveAsFilename(K13part & " Text " & Range("A1").value & U7part & " dated " & U8part, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
            If xFileName <> "False" Then
            Application.EnableEvents = False
            ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            Application.EnableEvents = True
        End If
    End If
    End If
    End If
    End If
End If
End Sub

How do I get the saved .xlsm file, from whichever folder it has been saved into, to then become an attachment in an Outlook email?

The code will be attached to 'Save and Email to Department x' command buttons so that when the user clicks one of the three command buttons, the 'To address' will be pre-populated with the correct 'To address'.

I thought I was finally onto something with the code I found here but I can't make it work no matter what I try.

cheers
Mike