cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Combine x2 Macros into one

mark ainscough
Brass Contributor

‎Jul 14 2018 03:10 PM - last edited on ‎Jul 25 2018 10:08 AM by

‎Jul 14 2018 03:10 PM - last edited on ‎Jul 25 2018 10:08 AM by

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!

 

 

Labels:
956 Views
0 Likes
1 Reply
Reply
1 Reply
Zack Barresse

‎Jan 04 2019 03:33 AM

‎Jan 04 2019 03:33 AM

Re: Combine x2 Macros into one

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

 

0 Likes
Reply