attach current unsaved workbook to email using vba and cc

bazbuyer

New Member
Joined
Dec 20, 2017
Messages
23
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,863
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,368
Messages
5,601,220
Members
414,434
Latest member
Riyen

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
Top