lojanica
New Member
- Joined
- Feb 22, 2024
- Messages
- 34
- Office Version
- 365
- Platform
- Windows
I have two codes which can be combined in one with yes no function.
Code is runed to create a new email, where one code will request approval to create purchase order and other just request creating PO without approval.
Both codes copy contents from tabs to create email body and select list of senders
I am struggling to get it work even though it looked simple from initial look, can anyone help or suggest what is best way to combine this two codes
Code1 to request PO approval
Code 2 to request po creation without approval
Thank You
Code is runed to create a new email, where one code will request approval to create purchase order and other just request creating PO without approval.
Both codes copy contents from tabs to create email body and select list of senders
I am struggling to get it work even though it looked simple from initial look, can anyone help or suggest what is best way to combine this two codes
Code1 to request PO approval
VBA Code:
Sub RequestPO()
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim OutApp As Object
Dim OutMail As Object
Dim mainwb As Workbook
Dim Signature As Variant
Dim rowrange As Range
Dim lastRow As Long
Set mainwb = ActiveWorkbook
Set rng = Nothing
Set rng2 = Nothing
Set rng3 = Nothing
Set rowrange = Nothing
On Error Resume Next
Set rng = mainwb.Sheets("Request PO").Range("j7:p10") '.SpecialCells(xlCellTypeVisible)
Set rng2 = mainwb.Sheets("Request PO").Range("j27:p31") '.SpecialCells(xlCellTypeVisible)
'Set rowrange = mainWB.Sheets("1 Client Form").Range("c12:c25") '.SpecialCells(xlCellTypeVisible)
'lastrow = Last(1, rowrange)
'MsgBox lastrow
Set rng3 = mainwb.Sheets("Request PO").Range("I11:P13")
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Display
Signature = .htmlbody
.to = mainwb.Sheets("names").Range("z5").Value & "; " & mainwb.Sheets("Request PO").Range("D23").Value
ClientName = mainwb.Sheets("1 Client Form").Range("E5").Value
.CC = mainwb.Sheets("Request PO").Range("D26").Value & "; " & mainwb.Sheets("Request PO").Range("D25").Value
.BCC = ""
.Subject = mainwb.Sheets("Request PO").Range("I3").Value
.htmlbody = RangetoHTML(rng) & RangetoHTML(rng3) & RangetoHTML(rng2) & Signature
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Code 2 to request po creation without approval
VBA Code:
Sub RequestPO200()
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim OutApp As Object
Dim OutMail As Object
Dim mainwb As Workbook
Dim Signature As Variant
Dim rowrange As Range
Dim lastRow As Long
Set mainwb = ActiveWorkbook
Set rng = Nothing
Set rng2 = Nothing
Set rng3 = Nothing
Set rowrange = Nothing
On Error Resume Next
Set rng = mainwb.Sheets("Request PO <200").Range("j7:p10") '.SpecialCells(xlCellTypeVisible)
Set rng2 = mainwb.Sheets("Request PO <200").Range("j27:p31") '.SpecialCells(xlCellTypeVisible)
Set rng3 = mainwb.Sheets("Request PO <200").Range("I11:P13")
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Display
Signature = .htmlbody
.to = mainwb.Sheets("Request PO <200").Range("D23").Value
ClientName = mainwb.Sheets("1 Client Form").Range("E5").Value
.CC = mainwb.Sheets("names").Range("z5").Value & "; " & mainwb.Sheets("Request PO <200").Range("D26").Value & "; " & mainwb.Sheets("Request PO <200").Range("D25").Value
.BCC = ""
.Subject = mainwb.Sheets("Request PO <200").Range("I3").Value
.htmlbody = RangetoHTML(rng) & RangetoHTML(rng3) & RangetoHTML(rng2) & Signature
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Thank You