attach current unsaved workbook to email using vba and cc

[bazbuyer]

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]

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]

Many thanks for the quick reply

 

[Logit]

You are welcome.

 

[richierich1985]

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]

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]

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

 

[Joe4]

You are welcome.
Glad we were able to help!