Help with organizing code..

[conradcliff]

Hey guys, I just want to start off by saying thanks for all the invaluable help! I've come a long way and feel that I'm almost there now with most of the hard stuff out of the way..

At this point I think I have all the functionality that I need and now it's just a matter of getting it organized. I intend to have one button run all of my macros in sequence with "yes or no" pop-ups asking whether you want to perform each action.

I had it working at one point with blind luck but then tried to add something and organize it a bit and totally broke it.

The following is all the pertinent code and as is will work fine if you answer yes to every question..but that of course will not always be the case.

    Response = MsgBox("Would you like to send the invoice to " & Range("ThisInvoiceBillEmail") & "?", vbYesNo)
        If Response = vbYes Then
        
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
    Dim ToAddress As String
    Dim MessageSubject As String
    Dim MessageAttachments As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    
    With ThisWorkbook.Sheets("PrintableInvoice")
        strbody = "<B>Hello " & .Range("ThisInvoiceBillName") & "," & " attached you should find your Snapshoot Invoice.</B><br><br>" & _
                  "<B>Simply print, sign and mail back with a check for the deposit amount and we will set your date in our calendars.</B><br>" & _
                  "<B>If you have any questions or concerns, please don't hesitate to call or email us.</B><br><br>" & _
                  "<B>Once again we thank you for your business and are looking forward to your event!</B>"
                  
        SigString = "C:\Users\" & Environ("username") & _
     "\AppData\Roaming\Microsoft\Signatures\ExcelSig.htm"
        If Dir(SigString) <> "" Then
                Signature = GetBoiler(SigString)
          Else
             Signature = ""
         End If
          
        ToAddress = .Range("ThisInvoiceBillEmail")
        MessageSubject = "Snapshoot Invoice for " & .Range("ThisInvoiceBillName")
        MessageAttachments = .Range("ThisInvoiceFileName")
        
    End With
    
    On Error Resume Next
    With OutMail
        .To = ToAddress
        .CC = ""
        .BCC = ""
        .Subject = MessageSubject
        .HTMLBody = strbody & "<br>" & Signature
        'You can add a file like this
        .Attachments.Add MessageAttachments
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    
    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    
    
    If Range("ThisInvoiceAttendant1") = "" And Range("ThisInvoiceAttendant2") = "" And Range("ThisInvoiceAmountPaid") >= (Range("ThisInvoiceTotal") / 2) Then
    '(AND(Range("ThisInvoiceAttendant1") = "",Range("ThisInvoiceAttendat2") = "",Range("ThisInvoiceAmountPaid") >= (Range("ThisInvoiceTotal") / 2)) Then
    
    Response = MsgBox("There are currently no attendants assigned to this event. Would you like to send the invites now?", vbYesNo)
        If Response = vbYes Then
    
    'Dim OutApp As Object
    'Dim OutMail As Object
    'Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With ThisWorkbook.Sheets("PrintableInvoice")
        strbody = "Date: " & .Range("ThisInvoiceEventDate") & " - " & vbNewLine & _
                  "Setup: " & .Range("ThisInvoiceEventSetup") & " - " & vbNewLine & _
                  "Time: " & .Range("ThisInvoiceEventStartTime") & " to " & .Range("ThisInvoiceEventEndTime") & " - " & vbNewLine & _
                  "Hours: " & .Range("ThisInvoiceEventHours") & " - " & vbNewLine & _
                  "Idle Hours: " & .Range("ThisInvoiceIdleHours") & " - " & vbNewLine & _
                  "Scrapbooking? " & .Range("ThisInvoiceScrapbooking?") & " - " & vbNewLine & _
                  "Location: " & .Range("ThisInvoiceVenueName") & vbNewLine & .Range("ThisInvoiceVenueAddress") & " " & .Range("ThisInvoiceVenueAddress2") & " - " & vbNewLine & _
                  "Round Trip From Office: " & .Range("ThisInvoiceTripMileage") & " Miles" & " - " & vbNewLine & _
                  "Pay: " & .Range("ThisInvoiceEventPay")
    End With
    
    On Error Resume Next
    With OutMail
        .To = "Test Group"
        .CC = ""
        .BCC = ""
        .Subject = "New Snapshoot Job"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
'End Sub


'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


If Range("ThisInvoiceAmountPaid") >= (Range("ThisInvoiceTotal") / 2) Then
Response = MsgBox("Would you like to create a calendar event?", vbYesNo)
        If Response = vbYes Then
'Dim olApp As Outlook.Application
    'Dim olApt As Outlook.AppointmentItem
    
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = New Outlook.Application
    End If
    On Error GoTo 0

    Set olApt = olApp.CreateItem(olAppointmentItem)
    
    With ThisWorkbook.Sheets("PrintableInvoice")
        strbody = "Invoice #: " & .Range("InvoiceNumberLookup") & vbNewLine & _
                  "Scheduling Info: " & vbNewLine & _
                  "Event Date: " & .Range("ThisInvoiceEventDate") & vbNewLine & _
                  "Event Setup: " & .Range("ThisInvoiceEventSetup") & vbNewLine & _
                  "Event Time: " & .Range("ThisInvoiceEventStartTime") & " to " & .Range("ThisInvoiceEventEndTime") & " " & "Total: " & .Range("ThisInvoiceEventHours") & " hours" & vbNewLine & _
                  "Idle Time: " & .Range("ThisInvoiceIdleStartTime") & " to " & .Range("ThisInvoiceIdleEndTime") & " " & "Total: " & .Range("ThisInvoiceIdleHours") & " hours" & vbNewLine & vbNewLine & _
                  "Event Info: " & vbNewLine & _
                  "Event Name: " & .Range("ThisInvoiceEventName") & vbNewLine & _
                  "Event Location: " & .Range("ThisInvoiceVenueName") & vbNewLine & .Range("ThisInvoiceVenueAddress") & .Range("ThisInvoiceVenueAddress2") & vbNewLine & _
                  "Event Contact: " & .Range("ThisInvoiceCoordinatorName") & vbNewLine & .Range("ThisInvoiceCoordinatorNumber") & vbNewLine & .Range("ThisInvoiceCoordinatorEmail") & vbNewLine & _
                  "Scrapbooking? " & .Range("ThisInvoiceScrapbooking?") & vbNewLine & _
                  "Round Trip From Office: " & .Range("ThisInvoiceTripMileage") & " Miles" & vbNewLine & vbNewLine & _
                  "Billing Info: " & vbNewLine & _
                  "Name: " & .Range("ThisInvoiceBillName") & vbNewLine & _
                  "Company Name: " & .Range("ThisInvoiceCompanyName") & vbNewLine & _
                  "Billing Address: " & vbNewLine & .Range("ThisInvoiceBillAdress") & vbNewLine & .Range("ThisInvoiceBillAddress2") & vbNewLine & .Range("ThisInvoiceBillNumber") & vbNewLine & .Range("ThisInvoiceBillEmail") & vbNewLine & vbNewLine & _
                  "Discount: $" & .Range("ThisInvoiceDiscount") & vbNewLine & _
                  "Total: $" & .Range("ThisInvoiceTotal") & vbNewLine & _
                  "Paid: $" & .Range("ThisInvoiceAmountPaid") & vbNewLine & _
                  "Due: $" & .Range("ThisInvoiceAmountDue")
    End With
    
    With olApt
        .Start = Range("ThisInvoiceEventDate").Value + Range("ThisInvoiceDecimalStart").Value
        .End = Range("ThisInvoiceEventDate").Value + Range("ThisInvoiceDecimalEnd").Value
        .Subject = Range("ThisInvoiceEventName").Value
        .Location = Range("ThisInvoiceVenueName").Value & " " & Range("ThisInvoiceVenueAddress").Value & " " & Range("ThisInvoiceVenueAddress2").Value
        .Body = strbody
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 1440
        .ReminderSet = True
        .Save
    End With



'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!



Response = MsgBox("Are you finished using the invoice?", vbYesNo)
        If Response = vbYes Then
            Application.Quit
        Else: Sheets("InvoiceMaster").Activate
            Range("B1").Select
            For B = 1 To 65536
        If ActiveCell.Value = Empty Then
            BCell = "B" & CStr(B - 1)
            NBCell = "B" & CStr(B - 2)
        Else
            Range("B" & CStr(B + 0)).Select
End If
    Next B
End If
End If
    
   
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Else:
Response = MsgBox("Are you finished using the invoice?", vbYesNo)
        If Response = vbYes Then
            Application.Quit
        Else: Sheets("InvoiceMaster").Activate
            Range("B1").Select
            For B = 1 To 65536
        If ActiveCell.Value = Empty Then
            BCell = "B" & CStr(B - 1)
            NBCell = "B" & CStr(B - 2)
        Else
            Range("B" & CStr(B + 0)).Select
End If
    Next B
End If
End If
End If
End If
End If

I've been pouring over this now for several hours and can just not figure it out..I feel pretty stupid and pretty tired and a little embarrassed...

If anyone can help me out or point me to a really good tutorial on nested If statements (better than the many I've looked at[though you don't know which ones I've seen]) I would be so very appreciative!

Thank you all so much!

 

[conradcliff]

Oh, and you may want to ignore that last else statement..it's just leftovers from my feeble attempts.

 

[conradcliff]

One last thing I forgot to mention, I'll be adding yet another chunk of code for sending an email out to another recipient as well...though I'm sure once I get help with these that I can figure out how to add on more later.

 

[conradcliff]

Ok, so after a lot of reading and frustration I was able to get it working..though it's not very pretty.

I came across the GoTo command and with several Else, GoTo, and properly placed End If's I was able to figure it out.

Thanks again for all the help you guys have given me with everything else, it has truly been invaluable.