Email VBA Using Index and Match from User Form

A

Ajweis01

New Member
Joined
Jul 28, 2015
Messages
3
I am trying to work with a User Form to send an email to my volunteers if they are a "No Show" selected on the User Form I have the code posted for the entire User form below, but the problem lies at the end with the email function. I am trying to use an index and match function using the name that is inserted in the user form to reference a list in an excel sheet. I don't know if it is possible to do this, nor if I explained what I am trying to do well or not. I am planning to replicate each email code so it references each separate entity, but once I figure one out, I'll be able to do so.

Code: 
Private Sub Submit_Click()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow


If Me.TGN1.Value = 0 Then GoTo Done Else:


Set the_sheet = Sheets("Tour Guide Tracker")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add




  If Me.TGN1.Value = 0 Then GoTo Done Else:
  table_object_row.Range(1, 1).Value = Me.TGN1.Value
  table_object_row.Range(1, 2).Value = Date
  table_object_row.Range(1, 3).Value = Date
  table_object_row.Range(1, 4).Value = Me.TGA1.Value
  table_object_row.Range(1, 5).Value = "1"
  table_object_row.Range(1, 6).Value = Me.Timebx.Value
  


  If Me.TGN2.Value = 0 Then GoTo Done Else:
  table_object_row.Range(2, 1).Value = Me.TGN2.Value
  table_object_row.Range(2, 2).Value = Date
  table_object_row.Range(2, 3).Value = Date
  table_object_row.Range(2, 4).Value = Me.TGA2.Value
  table_object_row.Range(2, 5).Value = "1"
  table_object_row.Range(2, 6).Value = Me.Timebx.Value
  
  If Me.TGN3.Value = 0 Then GoTo Done Else:
  table_object_row.Range(3, 1).Value = Me.TGN3.Value
  table_object_row.Range(3, 2).Value = Date
  table_object_row.Range(3, 3).Value = Date
  table_object_row.Range(3, 4).Value = Me.TGA3.Value
  table_object_row.Range(3, 5).Value = "1"
  table_object_row.Range(3, 6).Value = Me.Timebx.Value
  
  If Me.TGN4.Value = 0 Then GoTo Done Else:
  table_object_row.Range(4, 1).Value = Me.TGN4.Value
  table_object_row.Range(4, 2).Value = Date
  table_object_row.Range(4, 3).Value = Date
  table_object_row.Range(4, 4).Value = Me.TGA4.Value
  table_object_row.Range(4, 5).Value = "1"
  table_object_row.Range(4, 6).Value = Me.Timebx.Value
  
  If Me.TGN5.Value = 0 Then GoTo Done Else:
  table_object_row.Range(5, 1).Value = Me.TGN5.Value
  table_object_row.Range(5, 2).Value = Date
  table_object_row.Range(5, 3).Value = Date
  table_object_row.Range(5, 4).Value = Me.TGA5.Value
  table_object_row.Range(5, 5).Value = "1"
  table_object_row.Range(5, 6).Value = Me.Timebx.Value
    
  If Me.TGN6.Value = 0 Then GoTo Done Else:
  table_object_row.Range(6, 1).Value = Me.TGN6.Value
  table_object_row.Range(6, 2).Value = Date
  table_object_row.Range(6, 3).Value = Date
  table_object_row.Range(6, 4).Value = Me.TGA6.Value
  table_object_row.Range(6, 5).Value = "1"
  table_object_row.Range(6, 6).Value = Me.Timebx.Value
  
  If Me.TGN7.Value = 0 Then GoTo Done Else:
  table_object_row.Range(7, 1).Value = Me.TGN7.Value
  table_object_row.Range(7, 2).Value = Date
  table_object_row.Range(7, 3).Value = Date
  table_object_row.Range(7, 4).Value = Me.TGA7.Value
  table_object_row.Range(7, 5).Value = "1"
  table_object_row.Range(7, 6).Value = Me.Timebx.Value
    
  If Me.TGN8.Value = 0 Then GoTo Done Else:
  table_object_row.Range(8, 1).Value = Me.TGN8.Value
  table_object_row.Range(8, 2).Value = Date
  table_object_row.Range(8, 3).Value = Date
  table_object_row.Range(8, 4).Value = Me.TGA8.Value
  table_object_row.Range(8, 5).Value = "1"
  table_object_row.Range(8, 6).Value = Me.Timebx.Value
    
    
  If Me.TGN9.Value = 0 Then GoTo Done Else:
  table_object_row.Range(9, 1).Value = Me.TGN9.Value
  table_object_row.Range(9, 2).Value = Date
  table_object_row.Range(9, 3).Value = Date
  table_object_row.Range(9, 4).Value = Me.TGA9.Value
  table_object_row.Range(9, 5).Value = "1"
  table_object_row.Range(9, 6).Value = Me.Timebx.Value
  
  If Me.TGN10.Value = 0 Then GoTo Done Else:
  table_object_row.Range(10, 1).Value = Me.TGN10.Value
  table_object_row.Range(10, 2).Value = Date
  table_object_row.Range(10, 3).Value = Date
  table_object_row.Range(10, 4).Value = Me.TGA10.Value
  table_object_row.Range(10, 5).Value = "1"
  table_object_row.Range(10, 6).Value = Me.Timebx.Value
  
  If Me.TGN11.Value = 0 Then GoTo Done Else:
  table_object_row.Range(11, 1).Value = Me.TGN11.Value
  table_object_row.Range(11, 2).Value = Date
  table_object_row.Range(11, 3).Value = Date
  table_object_row.Range(11, 4).Value = Me.TGA11.Value
  table_object_row.Range(11, 5).Value = "1"
  table_object_row.Range(11, 6).Value = Me.Timebx.Value
  
  If Me.TGN12.Value = 0 Then GoTo Done Else:
  table_object_row.Range(12, 1).Value = Me.TGN12.Value
  table_object_row.Range(12, 2).Value = Date
  table_object_row.Range(12, 3).Value = Date
  table_object_row.Range(12, 4).Value = Me.TGA12.Value
  table_object_row.Range(12, 5).Value = "1"
  table_object_row.Range(12, 6).Value = Me.Timebx.Value
  
  If Me.TGN13.Value = 0 Then GoTo Done Else:
  table_object_row.Range(13, 1).Value = Me.TGN13.Value
  table_object_row.Range(13, 2).Value = Date
  table_object_row.Range(13, 3).Value = Date
  table_object_row.Range(13, 4).Value = Me.TGA13.Value
  table_object_row.Range(13, 5).Value = "1"
  table_object_row.Range(13, 6).Value = Me.Timebx.Value
    
  If Me.TGN14.Value = 0 Then GoTo Done Else:
  table_object_row.Range(14, 1).Value = Me.TGN14.Value
  table_object_row.Range(14, 2).Value = Date
  table_object_row.Range(14, 3).Value = Date
  table_object_row.Range(14, 4).Value = Me.TGA14.Value
  table_object_row.Range(14, 5).Value = "1"
  table_object_row.Range(14, 6).Value = Me.Timebx.Value
  
  If Me.TGN15.Value = 0 Then GoTo Done Else:
  table_object_row.Range(15, 1).Value = Me.TGN15.Value
  table_object_row.Range(15, 2).Value = Date
  table_object_row.Range(15, 3).Value = Date
  table_object_row.Range(15, 4).Value = Me.TGA15.Value
  table_object_row.Range(15, 5).Value = "1"
  table_object_row.Range(15, 6).Value = Me.Timebx.Value
  
  If Me.TGN16.Value = 0 Then GoTo Done Else:
  table_object_row.Range(16, 1).Value = Me.TGN16.Value
  table_object_row.Range(16, 2).Value = Date
  table_object_row.Range(16, 3).Value = Date
  table_object_row.Range(16, 4).Value = Me.TGA16.Value
  table_object_row.Range(16, 5).Value = "1"
  table_object_row.Range(16, 6).Value = Me.Timebx.Value
  
  If Me.TGN17.Value = 0 Then GoTo Done Else:
  table_object_row.Range(17, 1).Value = Me.TGN17.Value
  table_object_row.Range(17, 2).Value = Date
  table_object_row.Range(17, 3).Value = Date
  table_object_row.Range(17, 4).Value = Me.TGA17.Value
  table_object_row.Range(17, 5).Value = "1"
  table_object_row.Range(17, 6).Value = Me.Timebx.Value
  
  If Me.TGN18.Value = 0 Then GoTo Done Else:
  table_object_row.Range(18, 1).Value = Me.TGN18.Value
  table_object_row.Range(18, 2).Value = Date
  table_object_row.Range(18, 3).Value = Date
  table_object_row.Range(18, 4).Value = Me.TGA18.Value
  table_object_row.Range(18, 5).Value = "1"
  table_object_row.Range(18, 6).Value = Me.Timebx.Value
  
  If Me.TGN19.Value = 0 Then GoTo Done Else:
  table_object_row.Range(19, 1).Value = Me.TGN2.Value
  table_object_row.Range(19, 2).Value = Date
  table_object_row.Range(19, 3).Value = Date
  table_object_row.Range(19, 4).Value = Me.TGA2.Value
  table_object_row.Range(19, 5).Value = "1"
  table_object_row.Range(19, 6).Value = Me.Timebx.Value
  
  If Me.TGN20.Value = 0 Then GoTo Done Else:
  table_object_row.Range(20, 1).Value = Me.TGN20.Value
  table_object_row.Range(20, 2).Value = Date
  table_object_row.Range(20, 3).Value = Date
  table_object_row.Range(20, 4).Value = Me.TGA20.Value
  table_object_row.Range(20, 5).Value = "1"
  table_object_row.Range(20, 6).Value = Me.Timebx.Value
  
Done:


'If Me.TGA1.Value = "No Show" Then


'Dim tg_name As String
'Dim email As Long
'tg_name = Me.TGN1.Value
'email = Application.WorksheetFunction.Index([TGEmail], Application.WorksheetFunction.Match(tg_name, [TG], 0))






'Dim olApp As Outlook.Application
'Set olApp = CreateObject("Outlook.Application")


    'Dim olMail As Outlook.MailItem
    'Set olMail = olApp.CreateItem(olMailItem)
    'olMail.To = email
    'olMail.Subject = "Missed Tour"
    'olMail.Body = "Hello You Missed Your Tour"
    'olMail.Display
   
'Else: Unload Me
'End If
    
    
With Done
Unload Me
End With
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
I think the problem lies in trying to reference named ranges in the VB. Can you reference the actual ranges like sheet2.range("B1:B50") both in the index and the match? There is a way to use named ranges in vba but it still requires defining the actual workbook, example:

Set rng = Workbooks("Book1.xls").Names("Account").RefersToRange
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Add new row and add data of one cell to a specific cell through Button
Replies
1
Views
607
DanteAmor
DanteAmor
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
139
6StringJazzer
6StringJazzer
D
  • Solved
Error 1004
2
Replies
18
Views
401
dmt32
D
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
97
Denny57
D
I
  • Solved
Edit working code to look for value after a date & before a date
Replies
6
Views
132
Alex Blakenburg
A

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top