Excel VBA Notify/Email when specific data entered

mike90

New Member
Joined
Jun 2, 2011
Messages
16
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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

New Member
Joined
Jun 2, 2011
Messages
16
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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

New Member
Joined
Jun 2, 2011
Messages
16
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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

New Member
Joined
Jun 2, 2011
Messages
16
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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

New Member
Joined
Jun 2, 2011
Messages
16
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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-com:eek:ffice:eek:ffice" /><o:p></o:p>

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:p></o:p>

End With
Set olMail = Nothing
Set olApp = Nothing
End IF<o:p></o:p>

<o:p> </o:p>
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:p></o:p>

End With
Set olMail = Nothing
Set olApp = Nothing
End IF<o:p></o:p>

<o:p> </o:p>
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:p></o:p>

End With
Set olMail = Nothing
Set olApp = Nothing
End IF<o:p></o:p>
 

Forum statistics

Threads
1,081,545
Messages
5,359,435
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top