attach current unsaved workbook to email using vba and cc

bazbuyer

New Member
Joined
Dec 20, 2017
Messages
27
Office Version
  1. 2016
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Code:
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
 
Upvote 0
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!
 
Upvote 0
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:
VBA Code:
Application.DisplayAlerts = False

Just be sure to turn it back on at the end of the code, with a line like:
VBA Code:
Application.DisplayAlerts = True
(similar to what is already being done for Application.ScreenUpdating)
 
Upvote 0
Worked a treat! Thank you for your help - new to using VBA so this is quite a learning curve!
 
Upvote 0
You are welcome.
Glad we were able to help!
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top