Excel VBA Notify/Email when specific data entered

[mike90]

Hi,

I have a worksheet on Excel recording complaints. Each complaint is assinged a severity level T1 to T7 (T1 being the most serious). The problem I have is that I need to create some coding so that when a T1 case is entered, an email notifies the required people.

Can anyone help?

 

[Trevor G]

Mike welcome to the board,

How is your VBA skills? Are you using Outlook as your email system? Where is the data that needs to be triggered when it meets T1?

Shown below is some code that will send emails, this can be adapted to meet different conditions like a select case or if statement.

If you need help understanding and adapting then just add to your thread.

Sub sendForApproval()
'*************************************************************
'The following code has been created to send an email request
'To gain access to the database, References must be set
'To use Outlook, Tools Menu and References
'VBA Code created by Trevor G August 2010
'**************************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "Email Address goes here"
.Subject = "Access Request"
.Body = "Please can you provide access to the Database system." & vbCr & vbCr & _
"The required details are as follows: " & vbCr & vbCr & _
"Full Name is: " & vbCr & _
"My User ID is: " & vbCr & _
"My required access level is (Please delete as applicable (leave the number and position) - 1 Administrator, 2 Updating Only, 1 Manager, 3 Other (Please state))" & vbCr & _
" "
.Display

End With
Set olMail = Nothing
Set olApp = Nothing
End Sub

 

[mike90]

Hi Trevor,

Thanks for your reply!

My VBA skills are very limited as I only started a couple of days ago but I seem to be catching on pretty quickly!

I am using Outlook and the data is in an Excel Spreadsheet linked with the UserForm. My coding for the input of data is as follows:

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a job number
If Trim(Me.txtJobNo.Value) = "" Then
Me.txtJobNo.SetFocus
MsgBox "Please enter a job number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
ws.Cells(iRow, 3).Value = Me.txtCusRef.Value
ws.Cells(iRow, 4).Value = Me.txtTOC.Value
ws.Cells(iRow, 5).Value = Me.txtTOB.Value
ws.Cells(iRow, 6).Value = Me.txtJourney.Value
ws.Cells(iRow, 7).Value = Me.txtIncident.Value
ws.Cells(iRow, 8).Value = Me.txtResolution.Value
ws.Cells(iRow, 9).Value = Me.cboType.Value
ws.Cells(iRow, 10).Value = Me.cboBy.Value
ws.Cells(iRow, 11).Value = Me.cboTo.Value
ws.Cells(iRow, 12).Value = Me.cboCus.Value
ws.Cells(iRow, 13).Value = Me.cboSup.Value
'clear the data
Me.txtDate.Value = ""
Me.txtJobNo.Value = ""
Me.txtCusRef.Value = ""
Me.txtTOC.Value = ""
Me.txtTOB.Value = ""
Me.txtJourney.Value = ""
Me.txtIncident.Value = ""
Me.txtResolution.Value = ""
Me.cboType.Value = ""
Me.cboBy.Value = ""
Me.cboTo.Value = ""
Me.cboCus.Value = ""
Me.cboSup.Value = ""
End Sub

This is basically copied from a web example that I have adjusted. Don't know whether that will help with understanding what i want to acheive.

So to sum up - When a T1 case is input onto my excel spreadsheet via UserForm to notify/email respective people as soon as it's entered.

Cheers

 

[Trevor G]

Mike,

So once the form has been filled based on the cboType option that then should trigger the email as you click the Command Button. So the code I sent can be used, all you need to do is adapt it and add to your code before you reset the values to be empty. So consider this. Adjust the red text to either become a default message/subject or to refer to the objects from the form.

Before the comment Clear data add this

If me.cboType="1" Then
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = me.cboTo.value
.Subject = "Access Request"
.Body = "Please can you provide access to the Database system." & vbCr & vbCr & _
"The required details are as follows: " & vbCr & vbCr & _
"Full Name is: " & vbCr & _
"My User ID is: " & vbCr & _
"My required access level is (Please delete as applicable (leave the number and position) - 1 Administrator, 2 Updating Only, 1 Manager, 3 Other (Please state))" & vbCr & _
" "
.Display 'You can change this to .Send

End With
Set olMail = Nothing
Set olApp = Nothing
End IF

 

[mike90]

Cheers Trevor,

I have input the code and adjusted to for my needs, however an error occurs when trying to run, stating "User defined error" referring to:

Private Sub CommandButton1_Click() and Dim olApp As Outlook.Application

This is what I have now:

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.txtJobNo.Value) = "" Then
Me.txtJobNo.SetFocus
MsgBox "Please enter a job number"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
ws.Cells(iRow, 3).Value = Me.txtCusRef.Value
ws.Cells(iRow, 4).Value = Me.txtTOC.Value
ws.Cells(iRow, 5).Value = Me.txtTOB.Value
ws.Cells(iRow, 6).Value = Me.txtJourney.Value
ws.Cells(iRow, 7).Value = Me.txtIncident.Value
ws.Cells(iRow, 8).Value = Me.txtResolution.Value
ws.Cells(iRow, 9).Value = Me.cboType.Value
ws.Cells(iRow, 10).Value = Me.cboBy.Value
ws.Cells(iRow, 11).Value = Me.cboTo.Value
ws.Cells(iRow, 12).Value = Me.cboCus.Value
ws.Cells(iRow, 13).Value = Me.cboSup.Value
If Me.cboType = "T1" Then
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "email@example.com"
.Subject = "T1 Case Raised"
.Body = txtJobNo.Value & vbCr & vbCr & _
.Display
'You can change this to .Send

'clear the data
Me.txtDate.Value = ""
Me.txtJobNo.Value = ""
Me.txtCusRef.Value = ""
Me.txtTOC.Value = ""
Me.txtTOB.Value = ""
Me.txtJourney.Value = ""
Me.txtIncident.Value = ""
Me.txtResolution.Value = ""
Me.cboType.Value = ""
Me.cboBy.Value = ""
Me.cboTo.Value = ""
Me.cboCus.Value = ""
Me.cboSup.Value = ""
Me.txtDate.SetFocus
End Sub

 

[Trevor G]

In my original code it says you have to set the reference to use Outlook. So in the VBA screen select the Tools Menu and then Reference and go down the list until you find Microsoft Outlook (XX) .Object Library, click the box and try the code.

 

[mike90]

Sorry Trevor, Another query!!!

I have now got another error message for the .Send informing of a "Compile error: Expected Function or variable"

Please advise?

 

[Trevor G]

Mike,

Below the .Display you seem to have taken out the End with and these lines. I suggest you place them back in.

End With
Set olMail = Nothing
Set olApp = Nothing
End IF

 

[mike90]

Thank you ever so much Trevor, I have just shown the end results to my superiors and they are very happy!

They have now asked if it is possible to have individual email notifications for each of the severity types. I have tried to replicate your coding further down with variations for who is to be emailed etc. However when I try to run the code it states"Duplication declaration in current scope" error and highlights the second olapp As Outlook.Application

Any chance you could help with this?

 

[Trevor G]

Try something like this.

Take the olApp lines out and place them above the IF statement so you only refer to them once then that should work

So part of the code would now look like this (you still have to adjust it to include the 7 options)

Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If Me.cboType = "T1" Then
With olMail
.To = "email@example.com"
.Subject = "T1 Case Raised"
.Body = txtJobNo.Value & vbCr & vbCr & _
.Display
'You can change this to .Send<o></o>
End With
Set olMail = Nothing
Set olApp = Nothing
End IF<o></o>
<o> </o>
If Me.cboType = "T2" Then
With olMail
.To = "email@example.com"
.Subject = "T2 Case Raised"
.Body = txtJobNo.Value & vbCr & vbCr & _
.Display
'You can change this to .Send<o></o>
End With
Set olMail = Nothing
Set olApp = Nothing
End IF<o></o>
<o> </o>
If Me.cboType = "T3" Then
With olMail
.To = "email@example.com"
.Subject = "T3 Case Raised"
.Body = txtJobNo.Value & vbCr & vbCr & _
.Display
'You can change this to .Send<o></o>
End With
Set olMail = Nothing
Set olApp = Nothing
End IF<o></o>