vba print to use company letterhead

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
hi
i have code to send email and then to print.
this is a bit wrong as the printout should not have the email sender as the letterhead
how do i tell the code to print the information on a letterhead, i have the letterhead saved as an excel spreadsheet called group 29 or on a word document.
the message gets sent as part of macro2
shall i create a macro3 or can it be part of the same macro just print differently?
do you need to see the entire code before replying?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
hi
here it is

Private Sub Worksheet_Change(ByVal Target As Range)
Macro1 Target 'event runs when cell in Column Q is added
Macro2 Target 'event runs when cell in Column P is added

End Sub


Private Sub Macro1(ByVal Target As Range)
If Target.Column = 17 And Target.Cells.Count = 1 Then

If Target.Value <> "" Then
Cells(Target.Row, "R").Value = Date

End If
End If
End Sub



Private Sub Macro2(ByVal Target As Range)

If Target.Column = 17 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 & ", " & "(" & "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


End If
End If
End If

End Sub
 

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
my code ends with
newmsg.Send 'Send Email
newmsg.PrintOut

can i designate the printout to be added to a letterhead that was imported into excel as cells C2:I6?
can i add the letterhead to the email as well?
 

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
can i save my letterhead to the spreadsheet in use as a merged unit a1:h4 and then refer to that as part of my print?
 

Watch MrExcel Video

Forum statistics

Threads
1,100,042
Messages
5,472,142
Members
406,806
Latest member
babarfirasat

This Week's Hot Topics

Top