Creat Message Box To Show All Macro's Run 1 Workbook

[kraamerica]

I'm trying to create a message box that will populate when all macros have run (there are 4 modules in the same workbook. I currently have them running all macros as such:

Sub Run_All_Macros()

Call TZ_Overnight
Call TZ_Today
Call TZ_24_Hours
Call TZ_7_Days_Out

MsgBox "All PFRC's Have Been Sent" (this is not working)

End Sub

I found another older thread (here) that has them running in Snippets but I am for sure inexperienced in that process, however would it be easier to run all macros from the same module to run them and create the message box that they have run from there?

*I'm also trying to create a follow-up to show all individual records (by count) sent as it pertains (separated) by the amount of records sent by each macro in 1 email. I have this that I started when I only had 1 macro/module running:

Set outlookmailitem = Nothing
Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
'edress = "Name@Company.com"
subj = "NameTotal PFRC Sent" & " " & Date
With outlookmailitem
.to = "Name2@Company.com"
.cc = ""
.bcc = "Name3@company.com"
.Subject = subj
.body = "The total PFRC emails sent on " & " " & Date & " " & "was" & ":" & " " & count
End With
'outlookmailitem.display
outlookmailitem.send

I would like it to run the same as a total # in the subject line, but in that same email, break it out by macro/module:

Call TZ_Overnight - Count
Call TZ_Today - Count
Call TZ_24_Hours - Count
Call TZ_7_Days_Out - Count

**I can create a separate post for this last question if need be, but was trying not to clutter, so please advise and thank you!

 

[myall_blues]

When you say it doesn't work, what specifically does happen, if anything? Do you get any sort of error message?

For example, the following simple code works:

Sub Run_All_Macros()
    Call TZ_Overnight
    Call TZ_Today
    Call TZ_24_Hours
    Call TZ_7_Days_Out
    MsgBox "All PFRC's Have Been Sent" '(this is not working)
End Sub

Sub TZ_Overnight()
    Debug.Print "TZ_Overnight"
End Sub
Sub TZ_Today()
    Debug.Print "TZ_Today"
End Sub
Sub TZ_24_Hours()
    Debug.Print "TZ_24_Hours"
End Sub
Sub TZ_7_Days_Out()
    Debug.Print "TZ_7_Days_Out"
End Sub

By not getting the message box it implies that one of the earlier routines is possibly failing without you seeing it. Do you maybe have an On Error statement or an Exit Sub somewhere that is making it drop out?
If the results of one sub are not dependent on input from the previous I'd suggest commenting out any error checking and running each sub individually.

 

[kraamerica]

Yes of course sorry about that. I have the MsgBox in the last call (TZ_7_Days_Out) macro. I get the following error:

or:

I also tried to create just a msgbox module to call

Here is the full code (minus email body of text):

Sub Seven_Days_Out()

Dim edress As String
Dim edressbcc As String
Dim subj As String
Dim name As String
Dim message As String
Dim strStreet As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim fulladdress As String
Dim fulladdressnew As String
Dim filename, filename2 As String
Dim logo As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path, path2, path3 As String
Dim attachment As String
Dim lastrow As Integer
Dim x, count As Integer


 x = 2
Do While Sheets("Seven_Days_Out").Cells(x, 1) <> ""
  
    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
                Set myAttachments = outlookmailitem.attachments
                path = "C:\Users\tziska\OneDrive\PFRC Shortcut Files\"
    'path = "C:\Users\screa\OneDrive\reconstructionBH\Documents\contracts\tracy\april13\"
    'path2 = "C:\Users\tziska\Google Drive\PFRC Shortcut Files\"
    'path3 = "C:\Users\tziska\Google Drive\PFRC Shortcut Files\"
    Application.DisplayAlerts = False
    edress = Sheets("Seven_Days_Out").Cells(x, 9)
    edressbcc = Sheets("Seven_Days_Out").Cells(x, 42)

    subj = "Upcoming Budget Truck Rental Reservation" & " " & ref
    ref = Sheets("Seven_Days_Out").Cells(x, 4)
    dealer = Sheets("Seven_Days_Out").Cells(x, 16)
    strStreet = Sheets("Seven_Days_Out").Cells(x, 36)
    strCity = Sheets("Seven_Days_Out").Cells(x, 38)
    strState = Sheets("Seven_Days_Out").Cells(x, 39)
    strZip = Sheets("Seven_Days_Out").Cells(x, 40)
    fulladdress = strStreet & "," & strCity & "," & strState & " " & strZip
    phone = Sheets("Seven_Days_Out").Cells(x, 41)
    name = WorksheetFunction.Proper(Sheets("Seven_Days_Out").Cells(x, 7))
    filename = "Personal Financial Responsibility Certificate.pdf"
    filename2 = "Insurance Agent Toll Free Numbers.pdf"
    logo = "BTRlogo.png"
    
                attachment1 = path + filename
                attachment2 = path + filename2
    'attachment = path + logo

        outlookmailitem.to = edress
        outlookmailitem.cc = ""
        outlookmailitem.bcc = edressbcc
        outlookmailitem.Subject = subj & ref
        'outlookmailitem.Attachments.Add path & logo, 0
        strbody = "Dear" & " " & name & "," _
        
        subj = subj & " " & ref
With outlookmailitem
        .to = edress
        .cc = ""
        .bcc = edressbcc
        .HTMLBody = strbody & "<br>" & .HTMLBody
                    .attachments.Add path & logo, 0
        '.Attachments.Add (attachment)
        End With
        
                    myAttachments.Add (attachment1)
                    myAttachments.Add (attachment2)
        
        'myAttachments.Add path & logo, 0
            'outlookmailitem.display
        outlookmailitem.send
        'End With
    
                
        lastrow = lastrow + 1
        edress = ""
        Set myAttachments = Nothing
        ref = ""
    x = x + 1
    count = count + 1
Loop


MsgBox "All PFRC's Have Been Sent"

End Sub

 

[Joe4]

Do you have any Procedures, Functions, or Variables in your VBA code that use a name of "MsgBox"?

I would recommend doing a search in VBA, and searching your entire project for "MsgBox", and check all instances it finds.

 

[kraamerica]

The only search that returned MsgBox was in the VBA code & module I pasted above. I did a project search and for good measure did it module by module. I also deleted the module that just contained the MsgBox that I tried to run as a call.

 

[kraamerica]

The only search that returned MsgBox was in the VBA code & module I pasted above. I did a project search and for good measure did it module by module. I also deleted the module that just contained the MsgBox that I tried to run as a call.

PS. I am now able to get the msgbox if I run that module only, but cannot get for all 4. I also have no idea how it happened as it wouldn't work that way through 1/2 an hour ago.

 

[Joe4]

Which version of Excel are you working with?
You could try the more traditional format of MsgBox, which uses parentheses and multiple arguments, as shown here: MsgBox function (Visual Basic for Applications)

 

[kraamerica]

Which version of Excel are you working with?
You could try the more traditional format of MsgBox, which uses parentheses and multiple arguments, as shown here: MsgBox function (Visual Basic for Applications)

I actually was using the MsgBox Function from that same link. I have zero clue what I did, but it's all working now. I know for sure I did way too much screwing around to try and fix it, so I have no reference for the "next time" or to post here for the next guy.

Thank you Joe4 and myall_blues for responding! Really appreciate you guys.

*PS Using 365