print sheet as copy of email to be sent

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
424
Office Version
  1. 365
Platform
  1. Windows
this is my code
how do i add that after email sent
i need to print the same data

Private Sub Worksheet_Change(ByVal Target As Range)
Macro1 Target 'event runs when cell in Column Pis added

End Sub


Private Sub Macro1(ByVal Target As Range)

If Target.Column = 16 And Target.Cells.Count = 1 Then

If Target.Value <> "" Then


result = MsgBox("pressing OK will send email to notify", vbOKCancel + vbExclamation, "Missing Approval")

If result = vbCancel Then SaveUI = True

If result = vbOK Then


Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)

On Error Resume Next

newmsg.Recipients.Add (Cells(Target.Row, "Z").Value) ' Add Recipients
newmsg.Recipients.Add (Cells(Target.Row, "AA").Value)
newmsg.Subject = Cells(Target.Row, "B").Value & " Reimbursement" ' Add Subject
newmsg.Body = "This is to inform you that payment has been processed " & _
"on behalf of " & Cells(Target.Row, "B").Value & "." & vbCrLf & "" & _
"Check # " & Cells(Target.Row, "P").Value & " was issued " & " for the amount of " & "$" & Cells(Target.Row, "Q").Value & ", " & _
"for services in the month of " & Cells(Target.Row, "C").Value & " for " & Cells(Target.Row, "F").Value & Cells(Target.Row, "G").Value & "." & vbCrLf & "" & _
"The check was mailed to " & Cells(Target.Row, "S").Value & "." & vbCrLf & "" & _
"(This check may contain multiple reimbursement requests and bills.)" ' Email Body
newmsg.Display 'Display Email
newmsg.Send 'Send Email
MsgBox "Outlook message sent", , "Outlook message sent" ' Confirm Sent Email


End If
End If
End If

End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
424
Office Version
  1. 365
Platform
  1. Windows
ok
i asked newmsg.printout but how do i assign a printer?

newmsg.Recipients.Add (Cells(Target.Row, "Z").Value) ' Add Recipients
newmsg.Recipients.Add (Cells(Target.Row, "AA").Value)
newmsg.Subject = Cells(Target.Row, "B").Value & " Reimbursement" ' Add Subject
newmsg.Body = "This is to inform you that payment has been processed " & _
"on behalf of " & Cells(Target.Row, "B").Value & "." & vbCrLf & "" & _
"Check # " & Cells(Target.Row, "P").Value & " was issued " & " for the amount of " & "$" & Cells(Target.Row, "Q").Value & " for services in the month of " & Cells(Target.Row, "C").Value & " for " & Cells(Target.Row, "F").Value & Cells(Target.Row, "G").Value & ", " & "(" & "billed amount " & "$" & Cells(Target.Row, "K").Value & ")." & vbCrLf & "" & _
"(If Check amount is greater than billed amount, this check contains multiple receipts and reimbursement requests.)"
newmsg.Send 'Send Email
newmsg.PrintOut
 

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,669
Members
418,147
Latest member
dorkas

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