Forum Discussion
mark ainscough
Jul 14, 2018Brass Contributor
Combine x2 Macros into one
I need to add a vbOKCancel pop up box to thie macro below if cell Q13 = "COST" as a warning to the user. If they click ok then proceed with macro below if cancel is clicked the macro exits and does nothing.
before I can do this I will also have to combine the 2 Macros below but am struggling to figure out how. my knowledge is limited! currently they are set to run together when the user clicks on a button.
Sub emailsavePDF()
Dim objOutlook As Object
Dim objMail As Object
Dim signature As String
Dim oWB As Workbook
Set oWB = ActiveWorkbook
' MsgBox "
'1: A customer must been selected in cell C4" & vbNewLine & "" & vbNewLine & "
'2: A trailer number must be entered in cell C5 and must not contain any symbols" & vbNewLine & "" & vbNewLine & "
'3: A breif repair description must be entered in Cell C9 and must not contain any symbols" & vbNewLine & "" & vbNewLine & "
'4: You are connected to the network", , "THE FOLLOWING STEPS MUST BE COMPLETED"
With Sheets("Estimate")
If .Range("C4") = Empty Then
MsgBox ("A customer must been selected in cell C4 and must not contain any symbols")
End
ElseIf .Range("C5") = Empty Then
MsgBox ("A trailer number must be entered in cell C5 and must not contain any symbols")
End
ElseIf .Range("C9") = Empty Then
MsgBox ("A breif repair description must be entered in Cell C9 and must not contain any symbols")
End
End If
End With
s = Range("O7").Value
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
s, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
PDF_File = Range("O7").Value & ".pdf"
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.display
End With
signature = objMail.HTMLbody
With objMail
.To = Sheets("Estimate").Range("O9")
.Cc = Sheets("Estimate").Range("O10")
.Subject = Range("O12").Value
.HTMLbody = "<BODY style=font-size:11pt;font-family:Calibri>Hi,<p>Please find attached estimate for trailer " & Range("O13") & "<p> Any questions please don't hesitate to ask." & "<br> <br>" & signature & "</font>"
.Attachments.Add PDF_File
.Save
.display
End With
Set objOutlook = Nothing
Set objMail = Nothing
End Sub
Sub emailsaveexcel()
Dim newWB As Variant
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
With wb1
.SaveCopyAs Sheets("Estimate").Range("O5").Text & ".xlsm"
End With
End Sub
Hope there help out there!
- Zack BarresseIron Contributor
I'm not sure what you mean about combining the two macros. I don't specifically see where they're called together with a button or other routine.
As for your message box, you can use something like this:
Dim Prompt As String
Prompt = "1: A customer must been selected in cell C4" & vbNewLine & "" & vbNewLine & _
"2: A trailer number must be entered in cell C5 and must not contain any symbols" & vbNewLine & "" & vbNewLine & _
"3: A breif repair description must be entered in Cell C9 and must not contain any symbols" & vbNewLine & "" & vbNewLine & _
"4: You are connected to the network"
If MsgBox(Prompt, vbOKCancel, "THE FOLLOWING STEPS MUST BE COMPLETED") <> vbOK Then Exit Sub