Attach an Excel 2010 workbook in an e-mail using a "Submit" button

[CarlosTJackal]

Hi,

I am very new to VBA Code and am struggling to achieve adding an Excel 2010 workbook (or even Active Worksheet) to an e-mail using VBA and wondered if anyone could help?

So I've created a form in Excel 2010 which I want colleagues to complete. When they've done that, I'd like them to be able to press a button in Excel which then creates an e-mail and attaches the Active Sheet (or the entire workbook).

I don't want the e-mail to be sent automatically, as I would like people to be able to amend the e-mail text themselves. At the moment, I've managed to have Excel create the mail (with some basic text in it) and prefill the e-mail address I want it to be sent to but I can't work out (or find online) how to get the worksheet (or workbook) attached.

My code for creating the e-mail (as above) is here:

Private Sub CommandButton21_Click()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi," & vbNewLine & vbNewLine & _
"Please process the attached Leaver." & vbNewLine & vbNewLine & _
"Thanks"
On Error Resume Next
With xOutMail
.To = "GILiveSystemSupport@lloydsbanking.com"
.CC = ""
.BCC = ""
.Subject = "LEAVER'S FORM - "
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Can anyone help with the last part as it's driving me nuts!!

Thanks in advance

 

[Logit]

.
To attach active workbook :

Option Explicit


Sub Mail_workbook_Outlook()


    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 = "me@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "Test"
        .Body = ""
        .Attachments.Add (Application.ActiveWorkbook.FullName)
        
        '.Send
        .display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

To attach active sheet :

Option Explicit


Sub Email_Sheet()


   Dim oApp As Object
   Dim oMail As Object
   Dim LWorkbook As Workbook
   Dim LFileName As String


   'Turn off screen updating
   Application.ScreenUpdating = False


   'Copy the active worksheet and save to a temporary workbook
   ActiveSheet.Copy
   Set LWorkbook = ActiveWorkbook


   'Create a temporary file in your current directory that uses the name
   ' of the sheet as the filename
   LFileName = LWorkbook.Worksheets(1).Name
   On Error Resume Next
   'Delete the file if it already exists
   Kill LFileName
   On Error GoTo 0
   'Save temporary file
   LWorkbook.SaveAs Filename:=LFileName


   'Create an Outlook object and new mail message
   Set oApp = CreateObject("Outlook.Application")
   Set oMail = oApp.CreateItem(0)


   'Set mail attributes (uncomment lines to enter attributes)
   ' In this example, only the attachment is being added to the mail message
   With oMail
      .To = "user@yahoo.com"
      .Subject = "Subject"
      .body = "This is the body of the message." & vbCrLf & vbCrLf & _
      "Attached is the file"
      .Attachments.Add LWorkbook.FullName
      .Display
   End With


   'Delete the temporary file and close temporary Workbook
   LWorkbook.ChangeFileAccess Mode:=xlReadOnly
   Kill LWorkbook.FullName
   LWorkbook.Close SaveChanges:=False


   'Turn back on screen updating
   Application.ScreenUpdating = True
   Set oMail = Nothing
   Set oApp = Nothing


End Sub