VBA Add Worksheet to an email?

blimey88

New Member
Joined
Aug 20, 2019
Messages
33
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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.
 

blimey88

New Member
Joined
Aug 20, 2019
Messages
33

ADVERTISEMENT

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!
 

blimey88

New Member
Joined
Aug 20, 2019
Messages
33
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Many thanks for this, I have gone with the attachment option and that is working great!
Well, there you have another option to send emails. Thanks for comment.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,597
Members
413,916
Latest member
Islandlady

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