VBA Add Worksheet to an email?

[blimey88]

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

 

[6StringJazzer]

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

 .Attachments.Add "New Workbook.xlsx"

 

[DanteAmor]

The following is to send the sheet in the body of the mail, check if it is what you need.

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]

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]

The following is to send the sheet in the body of the mail, check if it is what you need.

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]

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

 .Attachments.Add "New Workbook.xlsx"

This has worked really well, I now have that code attaching a file. Many thanks

 

[DanteAmor]

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.