Run one macro straight after another

Sub SendEmail()
'///Set up the Excel variables.
    Const MsgSignature As String = "Supplier Relationship Team"
    Const sSubj As String = "Supplier Meeting Action Points"
    Dim rCl As Range, rRng As Range
    Dim OLApp As Object, olMailItm As Object
    Dim iCnt As Integer
    Dim sTo As String, sMsg As String

    '///Create the Outlook application and the empty email.
    Set OLApp = CreateObject("Outlook.Application")
    Set olMailItm = OLApp.CreateItem(0)

    sMsg = "Hi,<br><br>" & _
           "Please could you complete the action points found on the supplier meeting report (link below) <br><br>" & _
           "Once you have completed your action points please change the status from the drop down list found in column C/D and leave any relevant comments in the STAKEHOLDER COMMENTS column.<br><br>" & _
           "Once completed please click the 'close' button found under the SUPPLIER MEETINGS tab at the top of the page.:<br><br>" & _
           "If you have any questions regarding your action points please contact the appropriate category manager found in cell E6 of the sheet.:<br><br>" & _
           "Please open the meeting report using the link below and select the meeting report link called " & Range("B2") & " found in column E<br><br>" & _
           "Click on the link below to open the file (Click 'Ok' and 'Continue' to all prompts when opening the file) :<br><br> " & _
           "<A HREF=""file://" & ActiveWorkbook.FullName & _
           """>Link to the file</A>" & _
           "<br><br>Regards," & _
           "<br><br>" & MsgSignature

    '/// create list of recipients

    Set rRng = Range("B86:B93")

    With olMailItm

        For Each rCl In rRng.Cells
            If rCl.Value > 0 Then
                If sTo = "" Then
                    sTo = rCl.Value
                    sTo = sTo & ";" & rCl.Value
                End If
                iCnt = iCnt + 1
            End If
        Next rCl

        If iCnt < 2 Then
            MsgBox "You nave not entered any recipients.", vbCritical, MsgSignature
            Exit Sub
        End If

        .To = sTo
        .Subject = sSubj
        .HTMLBody = sMsg
    End With

    '///Clean up the Outlook application.
    Set olMailItm = Nothing
    Set OLApp = Nothing

    Exit Sub

    MsgBox Error(Err)
    Resume Next

End Sub

Open in new window

Option Explicit

Sub NewCode()
''/// the sheet name will need to be changed to actual name used in the final workbook

    If ActiveSheet.Name = "Stakeholder_Actionpoints" Then
        MsgBox "You have the rsults sheet active.", vbCritical, "Error"
        Exit Sub
    End If
    Dim ws As Worksheet
    Dim rToCopy As Range
    Dim lRw As Long
    Dim iX As Integer
    '1. Copy all values (including blank cell values) from the range A14:E18 and paste under the relevant column headings found in sheet Stakeholder_Actionpoints
    Set ws = ActiveSheet
    Set rToCopy = ws.Range(Cells(14, 1), Cells(18, 5))

    With Sheet55
        lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        Application.Goto .Cells(lRw, 2)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 1)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 5)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 3)
        ActiveSheet.Paste Link:=True
        Application.Goto .Cells(lRw, 4)
        ActiveSheet.Paste Link:=True

        For iX = 1 To rToCopy.Rows.Count

            .Hyperlinks.Add Anchor:=.Cells(lRw, 6), Address:="", SubAddress:= _
                            "'" & ws.Name & "'!A" & iX + 13, TextToDisplay:=ws.Name

            lRw = lRw + 1

        Next iX
    End With
End Sub

Open in new window

Can somebody edit the first code snippet above so that when this code is run it will run the second code straight after the processes from the first code are complete.

Please re-post the amended code so I can copy over the orginal code for the SendEmail macro

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We're not really here to do the work for you, but we're more than happy to help you learn how to do these things.

To run a Sub or Function, you simple "call" that Sub or Function when needed. If I wanted to run your sub named "NewCode", fo rexample, I'd simply type:


You would do this wherever it's needed - for example, if you need to call this after the code in your SendEmail sub has run, you'd put it at the end, just before the "Exit Sub" line. Or, you could modify the code that calls SendEmail to call the NewCode sub after:


This would first run SendEmail, then run NewCode.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
mikes6058Author Commented:
What if I want to run three subs after the first SendEmail sub?

I've just tried this...


Exit Sub

Only the SendEmail and NewCode subs ran but not the unfilter or filter subs?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's sort of hard to say. If you "call" those subs, they should run.

You can follow the code in a Debug session to see what's happening. To do that, set a Breakpoint on the line of code where you fire off the SendEmail sub, and then run whatever process is needed to trigger than code block. You'll be taken to that breakpoint, and you can use the Debug options to step through the code (the Debug menu is on the toolbar). When you step through, you can determine if your code is executing correctly, and in what order.

To set a Breakpoint, put your cursor on the line where you want the break to occur, and press the F9 key.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.