VBA Email with Outlook question

[Chris Hoek]

Hello everyone. Long time reader, first time poster here.

I have read the forum rules and the guidelines, and I think I'm good to go but please let me know if I'm messing anything up here.

I'm using Excel 2013 and have some experience with VBA but I would still consider myself a newb.

I have a trouble ticket tracker spreadsheet that I've been working on (for my job) for some time now. All information for a trouble ticket is entered from left to right on a row so that all information pertinent to that trouble ticket is on the same row. When a trouble ticket is opened it automatically sends an email to the person that opened it, when a trouble ticket is closed it also automatically sends them an email to tell them it has been closed.

Some of these trouble tickets require a status update every 2 hours so I have the macro set up to do this. Here's my question. Currently my macro for this "Status Update" email is going to the email address in the active cell row. To: code looks like this:

strto = Sheets("Issue Tracker").Range("D" & ActiveCell.Row)

On all my previous macros in this spreadsheet the macro is initiated by entering something into a cell on the same row as the email address that the email needs to go to. Accordingly, the current code does what it is supposed to do.

The problem with this is that at any given time we are tracking multiple issues and the current To: code isn't optimal for this macro.

The macro for my "Status Update" email is designed such that anytime my elapsed time column says 2:00, 4:00, 6:00, 8:00 etc. it automatically triggers the email to go out. I need this email to go out to the email address in the same row as the column in which the elapsed time column says 2:00, 4:00, 6:00, 8:00 etc. regardless of where my active cell is.

I hope I have explained this clearly enough.

Can someone please help?


Chris

 

[AlphaFrog]

Can you show your "Status Update" macro code? Take note of my signature block below about the use of CODE tags.

I suspect that the row could be determined from that and passed to the macro that sets the strto variable.

 

[Chris Hoek]

Can you show your "Status Update" macro code? Take note of my signature block below about the use of CODE tags.

I suspect that the row could be determined from that and passed to the macro that sets the strto variable.

Thanks AlphaFrog. The thing is, it works fine as long as I keep a cell selected on the row of the issue requiring status updates. But if I have a cell selected on another row it will send the correct information (the details from the correct row) but to the wrong recipient (the recipient on the row that is currently active).

Anyway, here's the code: (I'm sure there is a better way of writing this code, but like I said, I'm new to this, and it works, so I'm happy with it just need to fix the recipient issue)

Public Sub StatusUpdate()
Dim strto As String, strbody As String
Dim objOutLook As Object, objMail As Object
    Set objOutLook = CreateObject("Outlook.Application")
    Set objMail = objOutLook.CreateItem(0)


 
            strto = Sheets("Issue Tracker").Range("D" & ActiveCell.Row)


            strbody = "This is an automatically generated email.  The purpose of this email is to update you on the current status of your Trouble Ticket."
            StrBody1 = "Trouble Ticket number: "
            StrBody2 = "Venue/Carrier: "
            StrBody3 = "Affected component: "
            StrBody4 = "Issue/System affected: "
            StrBody5 = "Current status: "
            StrBody6 = "Thank you for your patience while we work to resolve your issue.  "
            StrBody7 = "If you have any questions regarding this matter, you can reach the Acme Company at (Acme@company.com)."
                    
 
 
    With objMail
 
        .To = strto
        .Subject = "Trouble Ticket #" & Sheets("Issue Tracker").Range("B" & ActiveCell.Row) & " Status Update"
        .HTMLBody = strbody & "

" & _
                    StrBody1 & _
                    Sheets("Issue Tracker").Range("B" & ActiveCell.Row) & "

" & _
                    StrBody2 & _
                    Sheets("Issue Tracker").Range("F" & ActiveCell.Row) & "
" & _
                    StrBody3 & _
                    Sheets("Issue Tracker").Range("G" & ActiveCell.Row) & "
" & _
                    StrBody4 & _
                    Sheets("Issue Tracker").Range("H" & ActiveCell.Row) & "
" & _
                    StrBody5 & _
                    Sheets("Issue Tracker").Range("AC" & ActiveCell.Row) & "

" & _
                    StrBody6 & _
                    StrBody7 & "


"
        .send
     End With
 
 
End Sub
[Code]

 

[Chris Hoek]

Just realized, this is the Module code, do you need the Sheet1 code? I don't think that will help, but let me know. Also, after further examination I realized that basically all the data that the macro pulls for the email comes from the activecell row. That would be an issue that I previously said wasn't an issue.

Regardless, the solution should be the same (I hope).

 

[AlphaFrog]

Yes I do want to see the Sheet1 code. I want to see how you trigger the email.

Missed the backslash on the end code tag.

[FONT=Courier New]your VBA code here[/FONT][[COLOR=#ff0000]/[/COLOR]CODE][/SIZE]

 

[Chris Hoek]

Yes I do want to see the Sheet1 code. I want to see how you trigger the email.

Missed the backslash on the end code tag.

[FONT=Courier New]your VBA code here[/FONT][[COLOR=#ff0000]/[/COLOR]CODE][/SIZE][/QUOTE]

Thanks again for the tips.

[CODE]


Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim MyMsg As String
    Dim My2Min As Double
    Dim My2Max As Double
    Dim My4Min As Double
    Dim My4Max As Double
    Dim My6Min As Double
    Dim My6Max As Double
    Dim My8Min As Double
    Dim My8Max As Double
    Dim My10Min As Double
    Dim My10Max As Double
    Dim My12Min As Double
    Dim My12Max As Double
    Dim My14Min As Double
    Dim My14Max As Double
    Dim My16Min As Double
    Dim My16Max As Double
    Dim My18Min As Double
    Dim My18Max As Double
    Dim My20Min As Double
    Dim My20Max As Double
    Dim My22Min As Double
    Dim My22Max As Double


    My2Min = 24.0833275462963
    My2Max = 24.0833391203704
    My4Min = 24.1666608796296
    My4Max = 24.166672453704
    My6Min = 24.249994212963
    My6Max = 24.250005787037
    My8Min = 24.333327546296
    My8Max = 24.33333912037
    My10Min = 24.41666087963
    My10Max = 24.416672453704
    My12Min = 23.499994212963
    My12Max = 23.500005787037
    My14Min = 23.583327546296
    My14Max = 23.58333912037
    My16Min = 23.66666087963
    My16Max = 23.666672453704
    My18Min = 23.749994212963
    My18Max = 23.750005787037
    My20Min = 23.833327546296
    My20Max = 23.83333912037
    My22Min = 23.91666087963
    My22Max = 23.916672453704
    


    Set FormulaRange = Me.Range("S17:S1000")


    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > My2Min And .Value < My2Max Or .Value > My4Min And .Value < My4Max Or .Value > My6Min And .Value < My6Max Or .Value > My8Min And .Value < My8Max Or .Value > My10Min And .Value < My10Max Or .Value > My12Min And .Value < My12Max Or .Value > My14Min And .Value < My14Max Or .Value > My16Min And .Value < My16Max Or .Value > My18Min And .Value < My18Max Or .Value > My20Min And .Value < My20Max Or .Value > My22Min And .Value < My22Max Then
                        Call StatusUpdate
                End If
            End If
        End With
    Next FormulaCell
    
ExitMacro:
    Exit Sub


EndMacro:
    Application.EnableEvents = True


    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description


End Sub

 

[AlphaFrog]

Call StatusUpdate like this to pass the row number of the FormulaCell that triggered the event
Call StatusUpdate(.Row)

Add a row argument r to StatusUpdate to receive the row number like this...
Public Sub StatusUpdate(r As Long)

Use the r variable in the range references.
strto = Sheets("Issue Tracker").Range("D" & r)

 

[Chris Hoek]

Call StatusUpdate like this to pass the row number of the FormulaCell that triggered the event
Call StatusUpdate(.Row)

Add a row argument r to StatusUpdate to receive the row number like this...
Public Sub StatusUpdate(r As Long)

Use the r variable in the range references.
strto = Sheets("Issue Tracker").Range("D" & r)

AlphaFrog, your recommendation worked perfectly. Thank you so much for your assistance.