VBA Add Worksheet to an email?

blimey88

New Member
Joined
Aug 20, 2019
Messages
38
Good afternoon all,

Is there an easyish way to add a worksheet to an email! This is the code I have already, i want to add a certain worksheet!

'it is set to .Display so the email can be checked before it is sent
Sub InspectionEmail(Names As String)

Dim OutApp As Object ' holder for outlook app
Dim OutMail As Object 'container for outlook mail

Set OutApp = CreateObject("Outlook.Application") ' create the mail object
Set OutMail = OutApp.CreateItem(0) ' create the mail item

With OutMail ' build the email from information in the cells on the worksheet


'Reminder Email
'create the email
.To = Names
.CC = "test@test.com"
'.BCC = MailDest ' this will be used if you are sending to multiply people and don't want them to see the other receipients (10 max)
.Subject = " Monthly Vehicle Inspection Reminder "
.Body = " Hi " & vbNewLine & vbNewLine & "Please be advised that the Monthly Vehicle Inspections are due no later than the last day of the Month @ 17:00. " & ExpDateMail & vbNewLine & vbNewLine & "Please contact either Ben Pollard or the T Brown Compliance Department if you have any queries."
.Display
'Send ' I have left this in case your want to action from display to send
Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub

End With

'clean up the mail object and item
Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You can't attach a worksheet, you can attach a file. You will have to create a new workbook, copy the worksheet to it, save the workbook, then attach it

VBA Code:
 .Attachments.Add "New Workbook.xlsx"
 
Upvote 0
The following is to send the sheet in the body of the mail, check if it is what you need.

VBA Code:
Sub Send_Range(Names As String)
  Dim ExpDateMail
  ActiveWorkbook.EnvelopeVisible = True
  With ActiveSheet.MailEnvelope
    .Introduction = " Hi " & vbNewLine & vbNewLine & _
      "Please be advised that the Monthly Vehicle Inspections " & _
      "are due no later than the last day of the Month @ 17:00. " & _
      ExpDateMail & vbNewLine & vbNewLine & _
      "Please contact either Ben Pollard or the T Brown Compliance Department if you have any queries."
    .Item.To = Names
    .Item.CC = "test@test.com"
    .Item.Subject = " Monthly Vehicle Inspection Reminder "
    .Item.send
  End With
  ActiveWorkbook.EnvelopeVisible = False
End Sub
 
Upvote 0
That is a much better solution if you want the content of the sheet to appear in the body of the email rather than as an attachment.
 
Upvote 0
The following is to send the sheet in the body of the mail, check if it is what you need.

VBA Code:
Sub Send_Range(Names As String)
  Dim ExpDateMail
  ActiveWorkbook.EnvelopeVisible = True
  With ActiveSheet.MailEnvelope
    .Introduction = " Hi " & vbNewLine & vbNewLine & _
      "Please be advised that the Monthly Vehicle Inspections " & _
      "are due no later than the last day of the Month @ 17:00. " & _
      ExpDateMail & vbNewLine & vbNewLine & _
      "Please contact either Ben Pollard or the T Brown Compliance Department if you have any queries."
    .Item.To = Names
    .Item.CC = "test@test.com"
    .Item.Subject = " Monthly Vehicle Inspection Reminder "
    .Item.send
  End With
  ActiveWorkbook.EnvelopeVisible = False
End Sub

Many thanks for this, I have gone with the attachment option and that is working great!
 
Upvote 0
You can't attach a worksheet, you can attach a file. You will have to create a new workbook, copy the worksheet to it, save the workbook, then attach it

VBA Code:
 .Attachments.Add "New Workbook.xlsx"


This has worked really well, I now have that code attaching a file. Many thanks
 
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