# attach current unsaved workbook to email using vba and cc



## bazbuyer (Jun 20, 2019)

Hi
I am using this code(below) to send a current unsaved workbook to a mail recipient when a button is clicked.  I want to be able to add a cc/bcc email address, how do I do it?  There are plenty of solution that send the saved form from a drive but I don't want users to be able to save the form.  I have thought of saving it, sending then clearing resetting the form and resaving but that is cumbersome plus it may be on a sharepoint where they cannot save in any event.  Also thought of saving as temporary file but sometimes getting a '400' error.  I'm guessing there is a simple line or 2 that I am missing from this code.  Many thanks

_Sub Email_Open_WB_as_Attachment()_



_Dim myFile As String_
_Dim myMsg As String_
_Dim myEmail As String    'MsgBox ActiveWorkbook.Name_
_myFile = ActiveWorkbook.Name_
_myMsg = "form"_
_myEmail = "email@address.com"_

_Application.Workbooks(myFile).SendMail myEmail, myMsg, False_
_End Sub_


----------



## Logit (Jun 20, 2019)

```
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.Name & " Email.xlsx"
   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  'Comment out this line and uncomment the next line when ready to auto-send email
      '.Send    'Uncomment this line and comment out the line above when ready to auto-send email
   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
```


----------



## bazbuyer (Jun 20, 2019)

Many thanks for the quick reply


----------



## Logit (Jun 20, 2019)

You are welcome.


----------



## richierich1985 (Jan 14, 2022)

I know this is an old thread, but I used the code above and it worked brilliantly- so thank you SO much! 

The only problem I am experiencing is when they click the button it presents an error message "The following features cannot be saved in macro-free workbooks: -VB project". 

Is there any way to ensure no error message appears... Thank you!


----------



## Joe4 (Jan 14, 2022)

richierich1985 said:


> I know this is an old thread, but I used the code above and it worked brilliantly- so thank you SO much!
> 
> The only problem I am experiencing is when they click the button it presents an error message "The following features cannot be saved in macro-free workbooks: -VB project".
> 
> Is there any way to ensure no error message appears... Thank you!


Welcome to the Board!

You can try disabling those warning messages with the line:

```
Application.DisplayAlerts = False
```

Just be sure to turn it back on at the end of the code, with a line like:

```
Application.DisplayAlerts = True
```
(similar to what is already being done for *Application.ScreenUpdating*)


----------



## richierich1985 (Jan 14, 2022)

Worked a treat! Thank you for your help - new to using VBA so this is quite a learning curve!


----------



## Joe4 (Jan 14, 2022)

You are welcome.
Glad we were able to help!


----------

