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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
602
DanteAmor
DanteAmor
youbitto
  • Solved
Index match VBA taking too long
2 3
Replies
27
Views
530
Alex Blakenburg
A
R
  • Solved
Control text formatting (e.g. font, color, etc.) when using Excel VBA to automate emails via Outlook
Replies
2
Views
261
Roland Hoelscher
R
S
  • Solved
date formatting within vba
Replies
3
Views
203
shina67
S
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
226
jsand
J

Forum statistics

Threads
1,223,101
Messages
6,170,116
Members
452,302
Latest member
TaMere

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